MySQL Error #1064 on WHERE clause

Question

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

Overwriting existing values means you need to use an UPDATE statement, not INSERT... — OMG Ponies
– OMG Ponies, Commented Jun 28, 2011 at 0:20
Your error is displaying $q but your query is stored in $query — datasage
– datasage, Commented Jun 28, 2011 at 0:31
... AND >startTime is a syntax error, on top of everything else. — Marc B
– Marc B, Commented Jun 28, 2011 at 1:06

Francois Deschenes · Accepted Answer · 2011-06-28 00:18:34Z

3

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

answered Jun 28, 2011 at 0:18

Francois Deschenes

25.1k4 gold badges67 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Rose Over a year ago

What I wouldn't give to have a second pair of eyes around :). Thanks to all. You're fantastic! My first time on here... how do I mark this as "answered"?

Francois Deschenes Over a year ago

@tchnchn - 15 minutes after you posted the question, you'll be able to accept the answer by click on the green checkmark to the left of my answer. See How does accepting an answer work? for more information.

George Johnston · Accepted Answer · 2011-06-28 00:16:48Z

1

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

answered Jun 28, 2011 at 0:16

George Johnston

32.3k29 gold badges130 silver badges177 bronze badges

Comments

ypercubeᵀᴹ · Accepted Answer · 2011-06-28 00:34:11Z

1

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());

answered Jun 28, 2011 at 0:34

ypercubeᵀᴹ

116k19 gold badges181 silver badges247 bronze badges

Collectives™ on Stack Overflow

MySQL Error #1064 on WHERE clause

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related