Jump to content

2 different updates second needs output of first


loki951510

Recommended Posts

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

Link to comment
Share on other sites

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'?

Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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 by loki951510
Link to comment
Share on other sites

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 by Psycho
  • Like 1
Link to comment
Share on other sites

^^^ 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 by mac_gyver
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.