Q: MySQL + PHP , Update values in multiple rows triggered by a "uniquevalue"

Question

I have these rows of "api_credit" and "api_select" .

The Value of "api_credit" decrements by '1' when it is triggered by a PHP file with the value called "Selected" in "api_select". IMAGE is attached with my question to get a better idea of DATABASE.

Now , the problem is that when it decrements, it decrements all values in "api_credit" which are "Selected".If one column value decrements to '18' , all other columns value becomes '18' if they are having that "Selected" term.

What i want is all of the values of "api_credit" to be "Selected" and want Database to First decrement value in first column of "api_credit" , when it reaches zero , then it should move-on to the next column of "api_credit" and start decrementing its value and so on .

Can Anyone please please please give me an idea of a PHP code to trigger this database behaviour as i want.

Iam new to MySQL and PHP , This database is keeping me frustrated , So Please Please i request you to help me write code in PHP for this database.

Give me any simple or complicated solution for this , iam sure i'll keep up with you.

CODE :

$sql = "SELECT * FROM site_api WHERE api_select = 'Selected'";
    $querya = mysql_query($sql, $con);
    if(!$querya)
    {echo'no api selected'.mysql_error(); exit;}
    $row = mysql_fetch_object($querya);
    $api = $row->api_key;
    $key = $row->secret_key;
    include("network.php");
    if (preg_match("/OK/", $result))
{
echo "Data has Not been Sent";
}
else
{
echo "Data has Been Sent.";
}



    $cr = $row->api_credit;
    $aid = $row->api_id;
    $cr = $cr - 1;
    if($cr <= 0){

$sql="UPDATE site_api SET api_credit='$cr', api_select='Expired' WHERE api_select='Selected'";

Post your code, the minimum amount to reproduce the problem. — Jonathan, Apr 04 '17 at 04:40
I get it... I think... anyway, welcome to StackOverflow! This is a site to help you correct/find problems, not to do your homework (roughly speaking). Please post your code, whatever you think might potentially do what you want, and then we'll take a look at that to provide a fine answer! — Zeke, Apr 04 '17 at 04:41
Thanks @Augwa for your reply ... ok , iam gonna post that PHP code — Phil Coulson, Apr 04 '17 at 05:35
@Zeke , Thanks for reply ... Well , iTs not my homework , its an API i got from my friend , using it for few days (Exactly speaking) :) , i got frustrated by everytime manually changing its values. Anyway , Thanks again for your reply , iam gonna post the Code now — Phil Coulson, Apr 04 '17 at 05:40
Sorry @Zeke and for that inconvenience. I Posted That CODE now in my Question post , you can easily read it now. iS it better? or you need more code? — Phil Coulson, Apr 04 '17 at 06:02
Sorry @Augwa and for that inconvenience. I Posted That CODE now in my Question post , you can easily read it now. iS it better? or you need more code? — Phil Coulson, Apr 04 '17 at 06:02

score 0 · Accepted Answer · edited May 23 '17 at 12:17

First of all, mysql_* functions are deprecated. Use mysqli_* instead!

Second of all, your code is open to SQL injections, use parameterized statements instead! More info on that in here: How can I prevent SQL injection in PHP?.

Finally, and regarding to your issue, if I get it correctly all you want to do is to decrease the value of the first row which has an api_select value of Selected. There are many ways to do this, but I'll just find what I need using LIMIT 1 first and then use the api_id value of that result (if there is any) to query the UPDATE. Here's what I'd do (not using parameterized statements):

$sql = "SELECT * FROM site_api WHERE api_select = 'Selected' AND api_credit > 0 LIMIT 1";
// I'll get only 1 row or none, and also making sure the api_credit is greater than zero.
$querya = mysqli_query($con, $sql);
if(!$querya){
    echo 'No API selected. '.mysqli_error();
    exit;
} else if(mysqli_num_rows($querya) == 1){
    $row = mysqli_fetch_object($querya);
    $api = $row->api_key;
    $key = $row->secret_key;
    /*include("network.php");
    if(preg_match("/OK/", $result)){
        echo 'Data has not been sent';
    } else {
        echo 'Data has been sent.';
    }*/
    // I'm commenting that out because I don't know where $result is coming from, maybe the included file?
    $cr = $row->api_credit;
    $aid = $row->api_id;
    $sql = "UPDATE site_api SET api_credit = (api_credit - 1) WHERE api_id = '$aid'";
    // I'm just decreasing within the SQL, but you can still do this with $cr--; outside, it's ok.
    if(mysqli_query($sql)){
        echo 'Value has been decreased.';
    } else {
        echo 'Value has not changed.';
    }
} else {
    echo 'If you can read this, something\'s really wrong!';
}

You can tweak this so that whenever $cr is equal to 0 at UPDATE time it also changes api_select to Expired as you said in your question.

Zeke i will just say this " i Love you , i Love you , i Love you" , Thanks for being here with me. i did'nt test this code if this code works , but i got the idea pretty well. THe way you explained this code , just brilliant. :) You DOn't Know, how much you have helped me. May GOD Bless You , May GOD Bless YOu with a very happy life ahead , AMEN. — Phil Coulson, Apr 04 '17 at 19:06
Okay, I'm glad you found my answer helpful, that's all I'm here for. If this doesn't work for any reason or you have any further questions, you can always contact me, my info is in my profile. There's always room to improve, happy coding! — Zeke, Apr 04 '17 at 19:14
Zeke , i Tested it yesterday , i did'nt work. Guess , i have to send you the original file , you should take a look. So , i need your email or some other mean to send you that file. — Phil Coulson, Apr 09 '17 at 09:39
Ok, send an email to stackoverflow@snowlinks.net and I'll check it out. — Zeke, Apr 09 '17 at 15:36
Zeke , i Sent That File to Your Email , Please Check it out. — Phil Coulson, Apr 09 '17 at 18:43

Q: MySQL + PHP , Update values in multiple rows triggered by a "uniquevalue"

1 Answers1