MySQL Error #1064 on WHERE clause

Details

• Mac OS X Snow Leopard 10.6.7
• MAMP Version 1.7.2
• PHP Version 5.3.4
• MySQL 5.0.41
• Table Type: MyISAM
• Encoding: UTF-8 Unicode (utf8)
• Collation: utf8_unicode_ci

Basic MySQL "INSERT" query:

$startMonth = $date->getMonth();
$startDay = $date->getDay();
$startYear = $date->getYear();
$startTime = $date->getTime();

$query = sprintf("INSERT INTO todos 
                  VALUES (startMonth, startDay, startYear, startTime)
                  VALUES (%d, %d, %d, %d)
                   WHERE todo = '%s'",
                  mysql_real_escape_string($startMonth),
                  mysql_real_escape_string($startDay),
                  mysql_real_escape_string($startYear),
                  mysql_real_escape_string($startTime),
                  mysql_real_escape_string($todo));

$result = mysql_query($query) or die("A MySQL error has occurred.<br />Your Query: " . $q . "<br /> Error: (" . mysql_errno() . ") " . mysql_error());

Here is the mysql_error that prints out:

A MySQL error has occurred.

Your Query:

INSERT INTO todos SET startMonth = 6 AND startDay = 27 A开发者_开发技巧ND startYear = 2011 AND >startTime = 1309216538 WHERE todo = todo 2

Error: (1064) 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 'WHERE todo = todo 2' at line 1

I've tried it every possible way I can think of. From changing the query to "INSERT INTO todos SET startMonth..." etc.

I've encoded the $todo variable in every possible way imaginable (i.e. addslashes, magic_quotes_pgc (even though it's deprecated ...I was getting desperate), htmlentities, mysql_real_escape_string... everything I could find and/or think of).

As per MySQL's documentation, you can't use a WHERE clause in an INSERT statement. You also have 2 VALUES clauses. It looks like your first VALUES clause is defining the fields you're inserting into as opposed to being values.

If you're trying to INSERT, than you'll want to do this:

$query = sprintf("INSERT INTO todos (todo, startMonth, startDay, startYear, startTime)
                  VALUES ('%s', %d, %d, %d, %d)",
                  mysql_real_escape_string($todo)
                  mysql_real_escape_string($startMonth),
                  mysql_real_escape_string($startDay),
                  mysql_real_escape_string($startYear),
                  mysql_real_escape_string($startTime));

INSERT INTO todos SET startMonth = 6 AND startDay = 27 AND startYear = 2011 AND >startTime = 1309216538 WHERE todo = todo 2 

should be

UPDATE todos SET startMonth = 6, startDay = 27, startYear = 2011 WHERE startTime = 1309216538 AND todo = 2;

You're confused with with the proper syntax for an update statement. AND is a logical operator. You need to use commas when assigning a value through your update statement. INSERT != UPDATE.

That, and there is some other crazy stuff going on with your statement. Either way, the second statement should work as long as your naming/schema is correct

Besides the two errors in INSERT that Francois explained, you are running one ($query) and printing another ($q):

$result = mysql_query($query)                                  --- $query is not 
or die("A MySQL error has occurred.<br />Your Query: " . $q    --- same with $q
  . "<br /> Error: (" . mysql_errno() . ") " . mysql_error());