updating multiple columns in single MySQL table

[akshit]

hey guys...

i know this is a very common problem and has been posted earlier also... but nobody seems to have figured out a solution...

i am making a program using PHP/MySQL.

I need to update 3 columns in a table. when i use a single UPDATE-SET command(using commas/AND etc etc.... i've tried them all).. only the first field gets updated, the other 2 remaining as they are.

When i use three seperate UPDATE commands, one for each column, FUNNILY, the first and the third columns get updated, and for some GOD KNOWS WHAT reason, the second column just refuses to change...

the code is correct and is infact the same for all 3 statements (with the "field_name" beng different of course..)

please help as this is really bugging...

thx..

[nav33n]

Huh.. Can you post your code ? (because I have never heard of this problem before!)

[silviuks]

Hello,
the update command looks like this:
UPDATE tbl_name SET col1='value1', col2='value2', col3='value3'
WHERE some conditions comes here

[akshit]

hi silviuks

i tried the method u suggested, but with that, only the first of the 3 columns gets updated.

I tries using 3 diff commands, and this , somehow, causes col 1 and 3 to update, leaving col 2 unchanged.

THE CODE

mysql_query("UPDATE client SET dod = '$dod' where ID = '$id' ");

mysql_query("UPDATE client SET mod = '$mod' where ID = '$id' ");

mysql_query("UPDATE client SET yod = '$yod' where ID = '$id' ");

[pritaeas]

Are you sure all columns are of type string ? We could be of more help if you post the table structure.

Have you tried:

mysql_query(...) or die(mysql_error());

to see if there is an error ?

[Shanti C]

Hello..

Try this code to update three or more fields in database:

$qur= "update `users` set `username`='".$_POST['username']."', `password`='".$_POST['password']."', `name`='".$_POST['name']."', `email`='".$_POST['emailid']."', `phone`='".$_POST['phoneno']."', `country`='".$_POST['country']."', `county`='".$_POST['county']."', `city`='".$_POST['city']."', `pincode`='".$_POST['zip']."',  `shipping`='".$_POST['shipping']."',  `billing`='".$_POST['billing']."', `organisation`='".$_POST['org']."', `fax`='".$_POST['fax']."'  where id=".$_GET['id'];		
		mysql_query( $qur) ;

I think it will work properly after this...

ShantI

[akshit]

i have checked the table structure. All three columns i am trying to update are NOT STRINGS BUT INTEGERS (they are storing date, month and year respectively)...

The three columns are identical in every way.

When I issue the commands:

mysql_query("UPDATE client SET dod = '$dod' where ID = '$id' ");

mysql_query("UPDATE client SET mod = '$mod' where ID = '$id' ");

mysql_query("UPDATE client SET yod = '$yod' where ID = '$id' ");

the 1st and the 3rd column get updated, leaving the 2nd unchanged. I know this seems funny, but i'll be really grateful if somebody could point out the error..

thx... cheers...

[nav33n]

if somebody could point out the error..

mysql_query("update client set dod='$dod', mod='$mod',yod='$yod' where id='$id'");

seems perfectly fine. Your 3 queries are also fine. I don't see any error as there isn't any.

[akshit]

i tried using a common update command, but when i use it with mysql_error(), it reports the following error:

You have an error in your SQL syntax, check the manual that corresponds to your MySQL server version for the right syntax to use near 'mod = '77' ,yod='8899' where ID =56 at line 1.

Somebody pls help...

[nav33n]

Okay. Do this.

$query = "update client set dod='$dod', mod='$mod',yod='$yod' where id='$id'";
echo $query;
mysql_query($query);

Tell us what it prints.

[akshit]

it prints the query (since i have given: "echo $query").

but the update does not occur....

this is really confusing... wats the problem???

[nav33n]

What exactly does it print ?

[akshit]

i entered the date as 10/4/2008...

it shows:-

update client set dod='10', mod='4',yod='2008' where id='120'....

and then the table (with the 'date' and 'year' column updated, but the 'month' column as it is...)..

pls suggest...

thx...

[sayedjustetc]

I think the update commands with three column update should work fine. But you can check what are the values you are supplying, what are the table column data type, check are you sending null values or not, you may need to convert data to the right type.

Try to print the query with
echo $query_string_variable

Then execute the query in the database by itself [not from PHP [I mean in mysql console/GUI/Mysql Browser/PhpMyAdmin].
] and see if it runs or what kind of errors it provides

You can also try the other way....try to write a query by providing static values and run the query into the database. if the query runs take it and use variables to provide the values. See what it does. Also, print these query and execute in the backend database [I mean in mysql console/GUI/Mysql Browser/PhpMyAdmin].

[nav33n]

Strange! Now, execute this query in mysql console or phpmyadmin. If it still doesn't update, check the column datatype. :S I don't see any problem with the query.

[akshit]

I think the update commands with three column update should work fine. But you can check what are the values you are supplying, what are the table column data type, check are you sending null values or not, you may need to convert data to the right type.

Try to print the query with echo $query_string_variable

Then execute the query in the database by itself [not from PHP [I mean in mysql console/GUI/Mysql Browser/PhpMyAdmin]. ] and see if it runs or what kind of errors it provides

You can also try the other way....try to write a query by providing static values and run the query into the database. if the query runs take it and use variables to provide the values. See what it does. Also, print these query and execute in the backend database [I mean in mysql console/GUI/Mysql Browser/PhpMyAdmin].

Strange! Now, execute this query in mysql console or phpmyadmin. If it still doesn't update, check the column datatype. :S I don't see any problem with the query.

Well, i tried running the query using the MySQL GUI (NaviCAT) wioth static data. Again, the same problem arises. The query runs perfectly well for 'dod' and 'yod', but when i run it for 'mod', it shows the error,

You have an error in your SQL syntax, check the manuala that corresponds to your MySQL server version for the right syntax to use near 'mod = '12' where ID = '1" at line 1

PS... The query i used was :=- UPDATE client SET mod = '12' where ID = '1'

withexactly the shown usage of (' '), but the error shows a certain different type of usage.

PS2-- I have also tried using 'text' for the defintion of the 3 columns in the table, and hav even tried int(the 3 cols are basically for date, month and year)... but both produce the same results.

somebody pls suggest sumthing... this is getting really fustrating...

thx nav33 and sayedjustetc for your time n effort.

[badbart]

Why are you using single quotes around the variables in your queries? If the fields are integers, they don't require string indicators. I thought their presence would cause the query to fail by themselves (not in MySQL, I guess). Is the ID field also an integer?

Oh, and isn't "mod" a PHP or MySQL reserved word? That could very well be the other part of your problem.

Try renaming the mod field to modnum (or something similar) and executing:
"update client set dod=$dod, modnum=$mod, yod=$yod where id=$id";

[nav33n]

Oh, and isn't "mod" a PHP or MySQL reserved word? That could very well be the other part of your problem.

Ah! Exactly. You nailed it. This will do.

update client set dod='10', `mod`='4',yod='2008' where id='120'

Notice the extra` around mod. It will make a keyword to be used 'without any error'. But, its not a good thing to have keywords as columnnames.
Cheers,
Naveen

Edit: mod is a mysql reserve word.

[akshit]

Oh, and isn't "mod" a PHP or MySQL reserved word? That could very well be the other part of your problem.

Well badbart, as nav33n said, you hit the nail on the head. I just changed the name of the field and the system worked...thx a ton for the suggestion coz such things are exttreemely difficult to identify....

a big thx also to nav33n and to sayedjustetc for their time and effort.... thnk u guys... thx a lot...

[suredurga]

i dont understand u r problem but i know is

$_post['dd'] = $dd;
$_post['mm'] = $mm;
$_post['yy] = $yy;
$dob="$dd/$mm/$yy";
mysql_query("update set users where dateofbirth = '$dob' ");

or
update set users where username=$u,password=$p ... like that also