2 different updates second needs output of first

[loki951510]

If i have both set like this they work

$sql = "UPDATE users SET pad_count = (pad_count + $add_pad_count) WHERE username = '$session->username'";
$sql = "UPDATE users SET cn1='blue' WHERE username = '$session->username'";

but on the second UPDATE i need cn1 to be like

 

cn$pad_count

 

so it will turn the next block blue in the list how can i do this as its starting to drive me made i have been trying for about a week now to get this sorted

 

thanks for the help in advance

[Psycho]

I'm not really following. First of all, there should be no reason to run two queries.

 

Are you wanting to set the field cn1 to the string value of "CN" concatenated with the new numerical value for pad_count? For example, if the exising pad_count value equals 10 and the $add_pad_count is 5: do you want to set pad_count to '15' and cn1 to 'cn15'?

[mac_gyver]

edit ^^^ i'm not trying to write same/similar posts as you, but when i'm logged in, i cannot see (if not logged in i can see) the info at the bottom of a thread that says who, if anyone, is already viewing it and would potentially write a reply.

 

you are apparently asking how to form a column name based on a numeric value, i.e. cn1, cn2, cn3, ... having a series of numbed columns is a bad database design, requiring extra php code and extra queries to just manage the data.

 

whatever this represents either needs to just store the current value (which is what the pad_count already is) or you need a separate table to hold the key/value pairs.

Edited November 3, 2015 by mac_gyver

[loki951510]

I'm not really following. First of all, there should be no reason to run two queries.

 

Are you wanting to set the field cn1 to the string value of "CN" concatenated with the new numerical value for pad_count? For example, if the exising pad_count value equals 10 and the $add_pad_count is 5: do you want to set pad_count to '15' and cn1 to 'cn15'?

yes thats how i need to do it if poss

i want to set pad_count to '15' and cn1 to 'cn15'

Edited November 3, 2015 by loki951510

[Psycho]

The fields are updated in order, so be sure to have the pad_count updated first in the query. Then the update to the cn1 field would use the 'current/new' value of pad_count

UPDATE users
SET
    pad_count = (pad_count + $add_pad_count),
    cn1 = CONCAT('cn', pad_count) -- will use the new pad_count value set on the line above
WHERE username = '$session->username'

EDIT: In retrospect, this is unnecessary to have pad_count and cn1 fields. You can always get the cn1 value from the pad_count. Either by dynamically creating the value in the select query or generating the value in the PHP code from the pad_count.

 

Dynamically creating cn value in SELECT query.

 

SELECT pad_count, CONCAT('cn', pad_count) as cn_value
FROM users

Edited November 3, 2015 by Psycho

[loki951510]

hi sorry that is almost right but i need

 

cn1='blue' then then on the next click it would then be cn2='blue'

 

ect

[mac_gyver]

^^^ that appears to be what i surmised. please reread my reply in this thread.

 

you may want to read my reply at the end of your last thread, where maintaining a count in a column can have race conditions that cause data to be lost, and the suggested methods to prevent this.

Edited November 4, 2015 by mac_gyver

[mac_gyver]

here's another problem with your code -

...WHERE username = '$session->username'";

a username can be anything that you allow when the user registered, so, something like D'Angelo is possible. this will break the sql syntax and cause a query error.

 

if someone is logged in, their username should only be used for display purposes. you should be using an integer user_id internally in your code. using an id will also make your queries faster, for a couple of reasons - the id column should be defined as an auto-increment column, this will/should automatically make it an index, and finding an integer value, either in the data or in the indexes, will be faster than finding a string, unless you use very short strings.