Updating multiple columns in one table, it wont work...

[Hall of Famer]

Well I have a script file that was originally written like this:

 

 

$query = "UPDATE ".$prefix."users SET nickname='".$nickname."' WHERE username='".$loggedinname."'";
mysql_query($query);

$query = "UPDATE ".$prefix."users SET gender='".$gender."' WHERE username='".$loggedinname."'";
mysql_query($query);

$query = "UPDATE ".$prefix."users SET color='".$color."' WHERE username='".$loggedinname."'";
mysql_query($query);

$query = "UPDATE ".$prefix."users SET profile='".$profile."' WHERE username='".$loggedinname."'";
mysql_query($query);

$query = "UPDATE ".$prefix."users SET favpet='".$favpet."' WHERE username='".$loggedinname."'";
mysql_query($query);

$query = "UPDATE ".$prefix."users SET about='".$about."' WHERE username='".$loggedinname."'";
mysql_query($query);

 

 

I tried to simplify it by rewriting the following codes below, but unfortunately it did not work. It wouldnt give any errors, but the columns aint updated at all:

 

 

mysql_query("UPDATE ".$prefix."users SET nickname='".$nickname."' , 
                                      gender='".$gender."' , 
			      color='".$color."' , 
			      profile='".$profile."' , 
			      favpet='".$favpet."'  , 
			      about='".$about."' , WHERE username='".$loggedinname."'");

 

 

Did I make any mistake rewriting the codes? Or is it actually impossible to update six columns using only one mysql_query? Please help...

[Psycho]

Remove that last comma right before the WHERE clause. Also, I propose never writing the query directly within the mysql_query() function. Instead, create the query as a variable. then if there are any problems the first step in debugging is to echo the query to the page. Nine times out of ten you will find your error strait away. Lastly, giving some "structure" to the query with indenting makes it much more readable and less prone to those types of typos.

$query = "UPDATE {$prefix}users
          SET nickname = '{$nickname}', 
              gender   = '{$gender}', 
              color    = '{$color}', 
              profile  = '{$profile}', 
              favpet   = '{$favpet}' , 
              about    = '{$about}'
          WHERE username = '{$loggedinname}'";
mysql_query($query) or die("Query:<br />{$query}<br />Error:<br />".mysql_error());

[php-lover]

Hi there, you might need to debug your query string to make sure it's correct.

 

You can print out the query without call the mysql_query() function. This will let you examine the query.

 

Example:

 

echo "UPDATE ".$prefix."users SET nickname='".$nickname."' ,

                                      gender='".$gender."' ,

      color='".$color."' ,

      profile='".$profile."' ,

      favpet='".$favpet."'  ,

      about='".$about."' , WHERE username='".$loggedinname."';

 

I'm sure there's a problem in your query, and it might be a variable is not set.

 

hope it help.

[Hall of Famer]

Thank you so so much, thought I did it almost perfectly but this tiny little comma messed up my entire script... >_<

[btherl]

I do my queries like this:

$query = "UPDATE {$prefix}users
          SET nickname = '{$nickname}' 
             , gender   = '{$gender}' 
             , color    = '{$color}' 
             , profile  = '{$profile}' 
             , favpet   = '{$favpet}' 
             , about    = '{$about}'
          WHERE username = '{$loggedinname}'";
mysql_query($query) or die("Query:<br />{$query}<br />Error:<br />".mysql_error());

 

It may look a bit funny at first, but notice that when you add a new column you don't have to think about whether or not you need a comma at the end, because every line except the "SET" line has a comma at the start.

[Hall of Famer]

This looks good to me, would you mind explaining why the brackets {} are used in your code?  They dont seem to make a difference to me.

[btherl]

They don't make a difference here.  But I think it's easier to read {$var} instead of ".$var.".  So it's a matter of taste really, either way works.  {$var} looks nicer with the syntax highlighting I use in my php editing software (vim).

[Psycho]

I do my queries like this:

$query = "UPDATE {$prefix}users
          SET nickname = '{$nickname}' 
             , gender   = '{$gender}' 
             , color    = '{$color}' 
             , profile  = '{$profile}' 
             , favpet   = '{$favpet}' 
             , about    = '{$about}'
          WHERE username = '{$loggedinname}'";
mysql_query($query) or die("Query:<br />{$query}<br />Error:<br />".mysql_error());

 

It may look a bit funny at first, but notice that when you add a new column you don't have to think about whether or not you need a comma at the end, because every line except the "SET" line has a comma at the start.

 

Well, if you also insert a line break after the "SET" and the first value being set that format also has another benefit. You can comment out any line of the values being set and the query will still be valid. Very handy when debugging but, personally, I like the format that "reads" like a human would.

 

This looks good to me, would you mind explaining why the brackets {} are used in your code?  They dont seem to make a difference to me.

When defining a string using double quotes variables will be interpreted inside the string. And, contrary to what btherl stated they are required in this instance for the $prefix variable. If you didn't use them it would try and interpret the variable $prefixuser - which doesn't exist. I prefer to interpret the variables inside the string since exiting/entering the quotes as the original string did is difficult to read. But, the brackets solve some issues where a variable may not be interpreted correctly.

[Hall of Famer]

I see what you mean, thank you so much for telling me all these. I will most likely to adopt this approach and use brackets in future so that it not only solves variable interpretation issue, but also makes it easier for other coders to read.

[btherl]

It does make a difference if you are interpolating variables, yes, but the OP was concatenating them.  I was comparing his original approach to your approach, not comparing interpolation without brackets to interpolation with brackets.  Thanks for the clarification though, I can see how it might be misinterpreted.