MySQL Syntax Error. stumped

[slapdashwebdesigner]

Here is my php code that builds the query.

<?php
$query = 'UPDATE websites SET designerid=' . $designer . ', clientid=' . $client . ', title=\'' . $title . '\', url=\'' . $_POST['url'] . '\', description=\'' . $desc . '\', designbrief=\'' . $_POST['designbrief'] . 
                '\', needs=\'' . $_POST['needs'] . '\', files=\'' . $_POST['files'] . '\', databases=\'' . $_POST['databases'] . '\', version=\'' . $_POST['version'] . '\', commissiondate=\'' . $commissioned . '\', deadline=\'' . $deadline .
                '\', status=\'' . $_POST['status'] . '\', finishdate=\'' . $finished . '\', price=\'' . $_POST['price'] .
                '\', previewurl=\'' . $_POST['previewurl'] . '\' WHERE id = ' . $_POST['id'];
?>

 

I then Get this error:

The mysql statement failed. 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 'databases='test', version='test', commissiondate='11/24/2009', deadline='11/24/2' at line 1

 

this is the full query that is submitted:

UPDATE websites SET designerid=1, clientid=1, title='Test', url='test', description='test', designbrief='test', needs='test', files='test', databases='test', version='test', commissiondate='11/24/2009', deadline='11/24/2009', status='test', finishdate='11/24/2009', price='6', previewurl='test' WHERE id = 11

 

Here is my database structure:

id  int(20)  No     

designerid int(20) No   

clientid int(20) No   

title varchar(40) No   

url varchar(240) No   

description blob No   

designbrief blob No   

needs blob No   

files blob No   

databases blob No   

version varchar(40) No   

commissiondate varchar(50) No   

deadline varchar(50) No   

status varchar(50) No   

finishdate varchar(50) No   

price int(50) No   

previewurl varchar(240) No

 

I have tried many things nothing has worked. I'm stumped 

[cags]

It's because you have used a reserved word for one of your column names (databases) I'd recommend renaming it if possible, if not you can simply put backticks around the column name in the query...

 

UPDATE websites SET designerid=1, clientid=1, title='Test' ... `databases`='test', version='test' ... previewurl='test' WHERE id = 11

[PFMaBiSmAd]

I also recommend using double-quotes to start and end your query string (so that you can put single-quotes into it without needing to escape them), then use the simplest (fewest changes) syntax within the query -

 

$query = "UPDATE websites SET designerid=$designer, clientid=$client, title='$title', url='{$_POST['url']}', description='$desc',
designbrief='{$_POST['designbrief']}', needs='{$_POST['needs']}', files='{$_POST['files']}', `databases`='{$_POST['databases']}',
version='{$_POST['version']}', commissiondate='$commissioned', deadline='$deadline', status='{$_POST['status']}',
finishdate='$finished', price='{$_POST['price']}', previewurl='{$_POST['previewurl']}' WHERE id = {$_POST['id']}";

 

You table definition also has a few problems -

 

1) The length of an INT cannot be more than 11, so INT(20) has no meaning.  An unsigned BIGINT can have a length of 20. Is that what your INT columns actually are?

 

2) Your deadline and finishdate columns appear to be for dates/time. Please use a DATE or DATETIME data type and put the correct formated values into them.

 

3) Your price column should be a DECIMAL data type of the correct length.

 

Finally, I hope you are using mysql_real_escape_string() on all string values and either validating or casting the numeric values being put into the query in order to prevent sql injection?

[slapdashwebdesigner]

thank you all, I am using mysql_real_escape_string, but its not really necessary because I will be the only one using it. but thanks, it is working now!

Solved!

[fenway]

I am using mysql_real_escape_string, but its not really necessary because I will be the only one using it. but thanks, it

Oh, it is VERY necessary -- don't kid yourself.