Page 1 of 1

PHP MySQL Update query problem

Posted: Tue Jun 19, 2007 10:17 am
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

Posted: Tue Jun 19, 2007 11:03 am
by superdezign
Get rid of the comma before the WHERE clause.

Posted: Tue Jun 19, 2007 1:05 pm
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.

Posted: Tue Jun 19, 2007 1:12 pm
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.

Posted: Tue Jun 19, 2007 1:13 pm
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', 
...

Posted: Tue Jun 19, 2007 1:15 pm
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!!!

Posted: Tue Jun 19, 2007 1:17 pm
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)

Posted: Tue Jun 19, 2007 1:20 pm
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)

Posted: Tue Jun 19, 2007 1:38 pm
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.

Posted: Wed Jun 20, 2007 1:30 am
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.