PHP MySQL Update query problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Mark82
Forum Newbie
Posts: 7
Joined: Mon Jun 11, 2007 8:41 am

PHP MySQL Update query problem

Post by Mark82 »

hi

I am working on a web page which logs incidents which may oocur at a school (such as accidents etc.) It is split over four pages, the first page's form's data is added to the table, and the user moves onto the second page.

At the second page, I wanted to be able to update the record that was previoulsy just been added. Only 1/4 of the record has data in it and the 2nd page should update the record with the additional data. Now the query I am running is:

QUERY: UPDATE incidents SET id='1',
first_name='Joe',
surname='Bloggs',
tutor='7ABC',
age='12',
sex='Male',
role='Student',
nature='broken bone',
body_part='right leg',
absence='Yes',
no_days='14',
condition='Remain at Hospital for over 24 hours',
WHERE id='1'

I am getitng a mySQL syntax error, but cant find it for the life of me! Is it becuase im not including all the fields in the database in the update, as I said this is only partial fields of the record.

Sorry for the long post. Any help is appreciated!

Thanks

Mark
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Get rid of the comma before the WHERE clause.
Mark82
Forum Newbie
Posts: 7
Joined: Mon Jun 11, 2007 8:41 am

Post by Mark82 »

I have taken the comma out, and still it gives me a syntax error message. This is now drivng me crazy, do I need to put all the other fields in the update query?

the ones in there currently are only 1/4 of the actual fields in the database.

this si sort of my last hope, Ive been staring at it for a day now.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

according to http://dev.mysql.com/doc/refman/5.1/en/ ... words.html condition is a reserved word.
The document also tells you what you have to do if you want to use a reserved word as field identifier.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Musta missed that part. Either rename your column or surround them all with `.

Code: Select all

UPDATE incidents SET id='1',
`first_name` = 'Joe',
`surname` = 'Bloggs', 
...
Last edited by superdezign on Tue Jun 19, 2007 1:15 pm, edited 1 time in total.
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post by bdlang »

condition is a MySQL reserved word. You cannot use this for a column name (or table / database name for that matter). Either rename the column (best practice) or surround the column with `backticks` (workaround).

You should refrain from surrounding INT type values like '1' with single quotes.

Also, are you sure you want to UPDATE id=1 WHERE id=1?? Does that make sense?

Mark82 wrote:I am getitng a mySQL syntax error
You've said this in two posts now but still haven't posted the error. This is important when you're asking us to help troubleshoot your syntax.

EDIT: volka beat me to it!!!
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

bdlang wrote:Also, are you sure you want to UPDATE id=1 WHERE id=1?? Does that make sense?
:lol:

It makes sense. It's just senseless. 8)
Mark82
Forum Newbie
Posts: 7
Joined: Mon Jun 11, 2007 8:41 am

Post by Mark82 »

wow thanks

condition didnt come up as a reserved word when I ran this in phpmyadmin, and usually if i selected a reserved word it does..

thanks very much guys, you mad emy night now! (how geeky does that sound)
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post by bdlang »

Mark82 wrote: condition didnt come up as a reserved word when I ran this in phpmyadmin, and usually if i selected a reserved word it does..
I would have bet you were using phpMyAdmin, this is probably the #1 reason people come to forums asking about query failures due to reserved words.
Mark82
Forum Newbie
Posts: 7
Joined: Mon Jun 11, 2007 8:41 am

Post by Mark82 »

I had already realised my mistake with the update id, I just didnt copy over the new query output, cant understand why I did that.

I've never had a problem with phpmyadmin before, I onyl ran the query in there to see if I could spot the error, the query is actually being run form a webpage.
Post Reply