TwoRE TwoRE - 7 months ago 18
SQL Question

PDO + MySQL: PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064

I know this is probably ends up being a typo somewhere but I've tried to debug my syntax for two days and I've searched numerous discussion boards and stack overflow questions to no avail.

When I try to execute the following

try {
$cnnxn = new PDO("mysql:host=$db_host;dbname=$db_name", $db_username, $db_password);
} catch (PDOException $e) {
die("ERROR: " . $e->getMessage());
}



$query1 = "INSERT INTO reply_statistics (reply_id, assignment_id, cat1, cat2, cat3, cat4, cat5, cat6, cat7, cat8) VALUES (:replyid, :assid, :cat1, :cat2, :cat3, :cat4, :cat5, :cat6, :cat7, :cat8)
ON DUPLICATE KEY
UPDATE reply_statistics SET `cat1`=:cat1, `cat2`=:cat2, `cat3`=:cat3, `cat4`=:cat4, `cat5`=:cat5, `cat6`=:cat6, `cat7`=:cat7, `cat8`=:cat8 WHERE `reply_id`=:replyid";

$query2 = "UPDATE replies SET status=:status, corrected_reply=:correply, score=:score WHERE id=:replyid";

$cnnxn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$cnnxn->exec("SET NAMES utf8");
$query = $cnnxn->prepare($query1);
$query->bindParam(':replyid', $replyid);
$query->bindParam(':assid', $assid);
$query->bindParam(':cat1', $cat1);
$query->bindParam(':cat2', $cat2);
$query->bindParam(':cat3', $cat3);
$query->bindParam(':cat4', $cat4);
$query->bindParam(':cat5', $cat5);
$query->bindParam(':cat6', $cat6);
$query->bindParam(':cat7', $cat7);
$query->bindParam(':cat8', $cat8);
$query->execute();

if ($query !== false)
{
$statusreport = "OK";
}

if($statusreport == "OK"){
$query = $cnnxn->prepare($query2);
$query->bindParam(':replyid', $replyid);
$query->bindParam(':correply', $correply);
$query->bindParam(':status', $status);
$query->bindParam(':score', $score);
$query->execute();

if ($query !== false)
{
echo "<div class=\"alert alert-success\">Tallennettu.</div>";
}

}


$cnnxn = null;


I get this

Fatal error: Uncaught exception 'PDOException' with message '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 'SET `cat1`='0', `cat2`='0', `cat3`='0', `cat4`='0', `cat5`='0', `cat6`='0', `cat' at line 3' in /[CENSORED]/do-review.php:56 Stack trace: #0 /[CENSORED]/do-review.php(56): PDOStatement->execute() #1 {main} thrown in /[CENSORED]/do-review.php on line 56


It connects fine and the variables set quite nicely, but it fails on the
INSERT -- ON DUPLICATE KEY UPDATE
clause.

About the table structure:
reply_id
is unique, but there is also a separate
id
column, which is a primary key with auto increment.

Answer

You have to treat the ON DUPLICATE set of parameters as totally seperately named parameters, even though you may use the same variables to bind data to them. And the query syntax was a little bit out of wack.

$query1 = "INSERT INTO reply_statistics 
               (reply_id, assignment_id, 
                cat1, cat2, cat3, cat4, 
                cat5, cat6, cat7, cat8) 
           VALUES (:replyid, :assid, 
                   :cat1, :cat2, :cat3, :cat4, 
                   :cat5, :cat6, :cat7, :cat8)
          ON DUPLICATE KEY UPDATE 
                  `cat1`=:cat1a, `cat2`=:cat2a, 
                  `cat3`=:cat3a, `cat4`=:cat4a, `cat5`=:cat5a, 
                  `cat6`=:cat6a, `cat7`=:cat7a, `cat8`=:cat8a ";


$query = $cnnxn->prepare($query1);

$query->bindParam(':replyid', $replyid);
$query->bindParam(':assid', $assid);
$query->bindParam(':cat1', $cat1);
$query->bindParam(':cat2', $cat2);
$query->bindParam(':cat3', $cat3);
$query->bindParam(':cat4', $cat4);
$query->bindParam(':cat5', $cat5);
$query->bindParam(':cat6', $cat6);
$query->bindParam(':cat7', $cat7);
$query->bindParam(':cat8', $cat8);
// parametes for the ON DUP set of params
$query->bindParam(':cat1a', $cat1);
$query->bindParam(':cat2a', $cat2);
$query->bindParam(':cat3a', $cat3);
$query->bindParam(':cat4a', $cat4);
$query->bindParam(':cat5a', $cat5);
$query->bindParam(':cat6a', $cat6);
$query->bindParam(':cat7a', $cat7);
$query->bindParam(':cat8a', $cat8);
Comments