2

How can I use ON DUPLICATE KEY UPDATE in PDO

My problem is that I get (SQLSTATE[42000]: Syntax error or access violation?) when I try the following synthaxe, the insert function and update works fine, but when I use both with on duplicate, I get error :

    $query = "INSERT INTO my_table (name,tophtml,bothtml,lang,background_mode,redirhtml,bg_color)
              VALUES (:name, :top_html, :bottom_html, :hs_language, :bg_style, :redirect_to, :bg_color )
              ON DUPLICATE KEY UPDATE
              tophtml= :top_html, bothtml= :bottom_html, lang= :hs_language, bg_color= :bg_color, redirhtml= :redirect_to, background_mode= :bg_style WHERE name=:name
             ";
    $request = $databaseStatus->connection->prepare($query);
    $request->bindParam(":name", $name, PDO::PARAM_STR);
    $request->bindParam(":top_html", $top_html, PDO::PARAM_STR);
    $request->bindParam(":bottom_html", $bottom_html, PDO::PARAM_STR);
    $request->bindParam(":bg_color", $bg_color, PDO::PARAM_STR);
    $request->bindParam(":redirect_to", $redirect_to, PDO::PARAM_STR);
    $request->bindParam(":hs_language", $hs_language, PDO::PARAM_STR);
    $request->bindParam(":bg_style", $bg_style, PDO::PARAM_STR);
    $request->execute();
zanderwar
  • 3,440
  • 3
  • 28
  • 46

1 Answers1

0

I found the solution here. My problem was in the where clause, you don't need to put the table name or SET in the ON DUPLICATE KEY clause, and you don't need a WHERE clause (it always updates the record with the duplicate key).

Community
  • 1
  • 1