PDO "ON DUPLICATE KEY UPDATE" prepared statement

Question

I am attempting a prepared statement for the first time based on this thread. So far I am getting the error message SQLSTATE[HY000]: General error: 1 near "ON": syntax error Here is the query followed by the try block:

$query = "INSERT INTO results2015_2016 ('id','ata','atc','atcommon','atn','ats','atsog','hta','htc','htcommon','htn','hts','htsog','bs','bsc','canationalbroadcasts','gcl','gcl1','gs','r1','usnationalbroadcasts')
          VALUES (':id',':ata',':atc',':atcommon',':atn',':ats',':atsog',':hta',':htc',':htcommon',':htn',':hts',':htsog',':bs',':bsc',':canationalbroadcasts',':gcl',':gcl1',':gs',':r1',':usNationalBroadcasts')
          ON DUPLICATE KEY UPDATE id= ':id2'";
try {   
    $db = new PDO('db info');  
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);  
    $stmt = $db->prepare($query);
    $stmt->bindParam(':ata', $ata, PDO::PARAM_STR);
    $stmt->bindParam(':atc' , $atc , PDO::PARAM_STR);
    $stmt->bindParam(':atcommon', $atCommon , PDO::PARAM_STR);
    $stmt->bindParam(':atn', $atn , PDO::PARAM_STR);
    $stmt->bindParam(':ats', $ats , PDO::PARAM_INT);
    $stmt->bindParam(':atsog', $atsog , PDO::PARAM_INT);
    $stmt->bindParam(':hta', $hta , PDO::PARAM_STR);
    $stmt->bindParam(':htc', $htc , PDO::PARAM_STR);
    $stmt->bindParam(':htcommon', $htCommon , PDO::PARAM_STR);
    $stmt->bindParam(':htn', $htn , PDO::PARAM_STR);
    $stmt->bindParam(':hts', $hts , PDO::PARAM_INT);
    $stmt->bindParam(':htsog', $htsog , PDO::PARAM_INT);
    $stmt->bindParam(':bs', $bs , PDO::PARAM_STR);
    $stmt->bindParam(':bsc', $bsc , PDO::PARAM_STR);
    $stmt->bindParam(':canationalbroadcasts', $caNationalBroadcasts , PDO::PARAM_STR);
    $stmt->bindParam(':gcl', $glc , PDO::PARAM_BOOL);
    $stmt->bindParam(':gcl1', $glc1 , PDO::PARAM_BOOL);
    $stmt->bindParam(':gs', $gs , PDO::PARAM_INT);      
    $stmt->bindParam(':r1', $r1 , PDO::PARAM_BOOL);
    $stmt->bindParam(':usnationalbroadcasts', $usNationalBroadcasts , PDO::PARAM_STR);
    $stmt->bindParam(':id', $idGame , PDO::PARAM_INT);
    $stmt->bindParam(':id2', $idGame , PDO::PARAM_INT);
    $stmt->execute();
} catch (Exception $e) {  
        echo $e->getMessage();
        exit;
    }

I can't seem to find much on this error message and what it has to do with my situation. This code is within a loop parsing jsonp...I can post the entire code if needed.

What made you think that sqlite supports this syntax? – Your Common Sense Feb 13 '16 at 08:09 — Your Common Sense, Feb 13 '16 at 08:09
where does it mention sqlite? – Professor Abronsius Feb 13 '16 at 08:43 — Professor Abronsius, Feb 13 '16 at 08:43

score 1 · Answer 1 · answered Feb 13 '16 at 07:36

1

When using a prepared statement, you do not need to enclose your parameters inside any kind of quotes. That will be taken care of by the engine itself:

$query = "INSERT INTO x(a,b,c,d) VALUES(:a, :b, :c, :d)";

answered Feb 13 '16 at 07:36

hjpotter92

78,589
36
144
183

score -1 · Answer 2 · answered Feb 13 '16 at 08:06

As noted by @HJPotter92 - the placeholders in the sql should not be quoted and it should also be noted that field names should be enclosed with backticks rather than single quotes ( or nothing if the names are not considered reserved & don't contain spaces etc )

$query = "insert into results2015_2016 (
              `id`,`ata`,`atc`,`atcommon`,`atn`,`ats`,`atsog`,`hta`,
              `htc`,`htcommon`,`htn`,`hts`,`htsog`,`bs`,`bsc`,`canationalbroadcasts`,`gcl`,`gcl1`,
              `gs`,`r1`,`usnationalbroadcasts`
          ) values (
             :id,:ata,:atc,:atcommon,:atn,:ats,:atsog,:hta,:htc,:htcommon,:htn,:hts,
             :htsog,:bs,:bsc,:canationalbroadcasts,:gcl,:gcl1,:gs,:r1,:usnationalbroadcasts
          ) on duplicate key update id=:id2";

PDO "ON DUPLICATE KEY UPDATE" prepared statement

2 Answers2