PDO: How to add a value to an existing value for multiple columns using UPDATE

Question

I have a table "api_keys" where i am storing some API Keys.

The api_keys Table has 5 columns: user, key, api1_limit, api2_limit, api3_limit.

I will be using the last 3 columns to store the sum of each API request. i want to do this because there is an API Limit, so i want to make sure to not pass the limit.

So the idea is to add values to "api limits" columns each time i use the API.

By the way, i am using PDO.

I tried to use the default UPDATE syntax to add to values to the existing values.

The issue is that the 'api1_limit' is the only column that get updated.

$sql_query = "UPDATE api_keys 
            SET api1_limit = api1_limit + :api1_limit_counter,  api2_limit =  api2_limit + :api2_limit_Counter, api3_limit =  api3_limit + :api3_limit_Counter WHERE key = :thekey";


$statement = $connection->prepare($sql_query);
$result = $statement->execute(
    array(
        ':api1_limit_counter'      =>  $api1_limit_counter,
        ':api2_limit_Counter'       =>   $api2_limit_Counter,
        ':api3_limit_Counter'       =>   $api3_limit_Counter,
        ':thekey'                  => $thekey

         )
);

I want to update values in all the 3 columns using 1 call.

@delboy1978uk unfortunately i can't as the api1_limit_counter variable contains the total of how many times i used the api. (i am using foreach to call the api multiple times). — Alae
– Alae, Commented Aug 8, 2019 at 13:08
I just found what was the issue, the "api limits" columns are set to NULL. you can not add to the value if the initial value is NULL, so i start setting the values to 0, now everything works fine. Thank you everybody! — Alae
– Alae, Commented Aug 8, 2019 at 13:35

Alae · Accepted Answer · 2019-08-08 13:37:54Z

1

I found what was the issue, the "api limits" columns are set to NULL. you can not add to the value if the initial value is NULL, so i start setting the values to 0 when i am adding credentials to the api_keys table, now everything works fine.

answered Aug 8, 2019 at 13:37

Alae

113 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PDO: How to add a value to an existing value for multiple columns using UPDATE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related