loki951510 Posted November 3, 2015 Share Posted November 3, 2015 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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted November 3, 2015 Share Posted November 3, 2015 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'? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted November 3, 2015 Share Posted November 3, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
loki951510 Posted November 3, 2015 Author Share Posted November 3, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted November 3, 2015 Share Posted November 3, 2015 (edited) 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 1 Quote Link to comment Share on other sites More sharing options...
loki951510 Posted November 3, 2015 Author Share Posted November 3, 2015 hi sorry that is almost right but i need cn1='blue' then then on the next click it would then be cn2='blue' ect Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted November 4, 2015 Share Posted November 4, 2015 (edited) ^^^ 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 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted November 4, 2015 Share Posted November 4, 2015 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.