Updating multiple columns in single MySQL table

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

Replies

  • Prasad Ajinkya
    Prasad Ajinkya
    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
    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
    mbz-bugsy
    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

    ________________________
    #-Link-Snipped-#
    Web Dev Swe

You are reading an archived discussion.

Related Posts

My final year project topic is: CHARPY TOUGHNESS AND MICROSTRUCTURAL CHARACTERISTICS OF MILD STEEL WELDED JOINTS QUENCHED IN MINERAL OILS.I NEED HELP IN EXECUTING IT.
hi... I am developing an application using PHP/MySQL. here, i have one page(say P1), which contains a form(just one drop down menu, say D1, with only 2 options , say...
hey guys. this may be very trivial, but i cant figure out a way... i want to retrieve the data entered by the user through a drop-down-menu using POST method....
hi frnds. this is something which may seem foolish to ask, but i have been beating my head against the wall at the frustration of not being able to get...
Hello thar! My name is Tyler, and I'm a 15 year old female who plans on majoring in electrical engineering at Louisiana Tech. I joined this forum to gain information...