CODEWITHSUNDEEP
phpmysqlpdo
maxedison
maxedison

Reputation: 17573

What's wrong with my PDO/MySQL syntax?

I'm using the following code:

$query = $dbh->prepare("INSERT into crm_order_errors "
                      ."SET (order_id, number_of_attempts, last_attempt) "
                      ."VALUES (:order_id, 0, :last_attempt) "
                      ."ON DUPLICATE KEY UPDATE number_of_attempts = number_of_attempts + 1, last_attempt = :last_attempt"
         );

$query->execute(array(':order_id'=>$orderId, ':last_attempt'=>1332849904);

This produces the following error:

PHP Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 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 '(order_id, number_of_attempts, last_attempt) VALUES ('10297', 0, '' at line 1 in /_myClasses/MYSQL_Logger.php on line 57

It simply isn't clear to me where the error is. It appears that the value for :last_attempt is not getting found, since the warning puts its value at '': Values ('10297', 0, ''. Why is that happening, and is that the source of the problem?

Also, am I allowed to use the same placeholder twice in a prepared statement (in this case, :last_attempt).

Upvotes: 1

Views: 780

Answers (3)

user1269636
user1269636

Reputation:

You are missing a parenthesis:

$query->execute(array('order_id' => $orderId, 'last_attempt' => 1332849904));

Also remove SET from your INSERT INTO statement.

Upvotes: 0

jasonlfunk
jasonlfunk

Reputation: 5249

You don't need the SET in the SQL statement.

Upvotes: 0

Travesty3
Travesty3

Reputation: 14489

You are missing an end parenthesis on your execute function.

Should be:

$query->execute(array(':order_id'=>$orderId, ':last_attempt'=>1332849904));

And you need to remove the SET keyword. It's for UPDATE statements, not INSERT statements.

Upvotes: 4

Related Questions