DaniWeb Forum Index > Web Development > PHP

updating multiple columns in single MySQL table

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jun 2008

Posts: 46

Reputation: akshit is an unknown quantity at this point

Solved Threads: 0

akshit

Offline

Light Poster

Re: updating multiple columns in single MySQL table

#11

Jul 4th, 2008

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

but the update does not occur....

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

•

Join Date: Nov 2007

Posts: 3,748

Reputation: nav33n is a jewel in the rough

Solved Threads: 331

nav33n

Offline

Senior Poster

Re: updating multiple columns in single MySQL table

#12

Jul 4th, 2008

What exactly does it print ?

Last edited by nav33n; Jul 4th, 2008 at 10:04 am.

Ignorance is definitely not bliss!

*PM asking for help will be ignored*

•

Join Date: Jun 2008

Posts: 46

Reputation: akshit is an unknown quantity at this point

Solved Threads: 0

akshit

Offline

Light Poster

Re: updating multiple columns in single MySQL table

#13

Jul 5th, 2008

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

•

Join Date: Jun 2008

Posts: 19

Reputation: sayedjustetc is an unknown quantity at this point

Solved Threads: 3

sayedjustetc sayedjustetc is offline

Offline

Newbie Poster

Re: updating multiple columns in single MySQL table

#14

Jul 5th, 2008

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

http://computer.justEtc.net - Free short notes, books, training videos on Business and Computer

•

Join Date: Nov 2007

Posts: 3,748

Reputation: nav33n is a jewel in the rough

Solved Threads: 331

nav33n

Offline

Senior Poster

Re: updating multiple columns in single MySQL table

#15

Jul 6th, 2008

Strange! Now, execute this query in mysql console or phpmyadmin. If it still doesn't update, check the column datatype.

I don't see any problem with the query.

Ignorance is definitely not bliss!

*PM asking for help will be ignored*

•

Join Date: Jun 2008

Posts: 46

Reputation: akshit is an unknown quantity at this point

Solved Threads: 0

akshit

Offline

Light Poster

Re: updating multiple columns in single MySQL table

#16

Jul 7th, 2008

•

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

•

Originally Posted by nav33n

Strange! Now, execute this query in mysql console or phpmyadmin. If it still doesn't update, check the column datatype. 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'

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

•

Join Date: Sep 2007

Posts: 12

Reputation: badbart is an unknown quantity at this point

Solved Threads: 1

badbart

Offline

Newbie Poster

Re: updating multiple columns in single MySQL table

#17

Jul 7th, 2008

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";

Last edited by badbart; Jul 7th, 2008 at 1:15 pm.

•

Join Date: Nov 2007

Posts: 3,748

Reputation: nav33n is a jewel in the rough

Solved Threads: 331

nav33n

Offline

Senior Poster

Re: updating multiple columns in single MySQL table

#18

Jul 7th, 2008

•

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.

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
UPDATE client SET dod='10', `MOD`='4',yod='2008' WHERE id='120'
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.

Last edited by nav33n; Jul 7th, 2008 at 1:41 pm.

Ignorance is definitely not bliss!

*PM asking for help will be ignored*

•

Join Date: Jun 2008

Posts: 46

Reputation: akshit is an unknown quantity at this point

Solved Threads: 0

akshit

Offline

Light Poster

Re: updating multiple columns in single MySQL table

#19

Jul 7th, 2008

•

Originally Posted by badbart

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

•

Join Date: Oct 2008

Posts: 3

Reputation: suredurga is an unknown quantity at this point

Solved Threads: 0

suredurga

Offline

Newbie Poster

Re: updating multiple columns in single MySQL table

#20

Oct 25th, 2008

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