Updating multiple columns in single MySQL table

[hbk]

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..

[kidakaka]

Hi hbk,
Are you checking the code for exclusive table and field locks?
Do you have any other processes/triggers (mysql5 does have them) that update/revert the values back?
Are you using manual transaction processing, and committing the transaction half way through?

Would certainly help if you could post the code here or mail it to me - prasad dot ajinkya at gmail dot com.

[hbk]

here is the code---

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

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

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


the 1st and the 3rd query get executed, but no updation occurs thru the 2nd query.

I have even tried using a single query, but the same effect.

When i use mysql_error() with the second query, it reports the 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 = '10' ,yod='2008' where ID =56 at line 1.


Somebody please help...

thx a ton...

[mbz-bugsy]

Originally Posted by hbk

here is the code---

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

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

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


the 1st and the 3rd query get executed, but no updation occurs thru the 2nd query.

I have even tried using a single query, but the same effect.

When i use mysql_error() with the second query, it reports the 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 = '10' ,yod='2008' where ID =56 at line 1.


Somebody please help...

thx a ton...

Hi hbk,
I don’t know if it’s too late posting this, you might already have figured it out. When it comes to mysql, in your queries you must be careful when you want to update or insert data into columns with names that are usual parameters in mysql statements. For e.g ifyou want to update a column named update you must escape it with `, like UPDATE news set `update`=’value’. In this case the column mod, you must escape it with `. Like this

mysql_query("UPDATE table SET dod = '$dod', `mod`=’$mod’, yod =’$yod’ where ID = '$id' ");



Good luck

________________________
ILLUMINA
Web Dev Swe