Pdo Prepared Statements for Insert and on Duplicate Key Update with Named Placeholders

PDO prepared statements for INSERT and ON DUPLICATE KEY UPDATE with named placeholders

Your ON DUPLICATE KEY syntax is not correct.

$stmt = $conn->prepare('INSERT INTO customer_info (user_id, fname, lname) VALUES(:user_id, :fname, :lname)
    ON DUPLICATE KEY UPDATE fname= :fname2, lname= :lname2');

$stmt->bindParam(':user_id', $user_id);  
$stmt->bindParam(':fname', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname', $_POST['lname'], PDO::PARAM_STR);      
$stmt->bindParam(':fname2', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname2', $_POST['lname'], PDO::PARAM_STR);      

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

See http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

You also had a PHP syntax error: you split the query up into two strings.

UPDATE:

To bind multiple parameters:

function bindMultiple($stmt, $params, &$variable, $type) {
  foreach ($params as $param) {
    $stmt->bindParam($param, $variable, $type);
  }
}

Then call it:

bindMultiple($stmt, array(':fname', ':fname2'), $_POST['fname'], PDO::PARAM_STR);

PDO Insert on Duplicate Key Update

What you have attempted to do is to dynamically build a SQL string that will become parameterized. The :paramname parameters are expected to be single values mapped to column values, where clause parameters, etc. Instead you have used $fields[] = sprintf("%s = :%s", $key, $key); to create a string of :paramname fields in order to plug into the query. This just won't work in a parameterized statement.

Rather than doing ON DUPLICATE KEY UPDATE :fieldlist, you should build the whole sql string before passing it into prepare().

Then rather than use the bindParam() method to bind each one individually, you can use an alternate syntax to execute() to pass in an array of expected parametric values. They need to be in the correct order, or have array keys the same names as the :param parameters in your SQL. See the docs for more info and examples.

$array_of_parameters = array();
foreach($faculty as $key=>$val){
    $array_of_parameters[$key] = $val);
}
$stmt->execute($array_of_parameters);

EDIT To properly use parameters in your UPDATE statement, do it like this:

// Create your $field_list before attempting to create the SQL statement
$field_list = join(',', $fields);

$update = 'UPDATE fhours SET '.$field_list. 'WHERE fname=:fname AND lname=:lname';
// Here, echo out $update to make sure it looks correct

// Then add the fname and lname parameters onto your array of params
$array_of_parameters[] = $_POST['fname'];
$array_of_parameters[] = $_POST['lname'];

// Now that your parameters array includes all the faculty in the correct order and the fname & lname,
// you can execute it.
$stmt->prepare($update);
$stmt->execute($array_of_parameters);

PDO prepared statements IN clause with named placeholders doesn't work as expected

This should work for you:

So as already said in the comments you need a placeholder for each value which you want to bind into the IN clause.

Here I create first the array $ids which only holds the plain ids, e.g.

[2, 3]

Then I also created the array $preparedIds which holds the placeholders as array, which you then later use in the prepared statement. This array looks something like this:

[":id2", ":id3"]

And I also create an array called $preparedValues which holds the $preparedIds as keys and $ids as values, which you then later can use for the execute() call. The array look something like this:

[":id2" => 2, ":id3" => 3]

After this you are good to go. In the prepared statement I just implode() the $preparedIds array, so that the SQL statement look something like this:

... IN(:id2,:id3) ...

And then you can simply execute() your query. There I just array_merge() your $preparedValues array with the other placeholders array.

<?php

    $ids = array_map(function($item){
        return $item->id;
    }, $entitlementsVOs);

    $preparedIds = array_map(function($v){
        return ":id$v";
    }, $ids);

    $preparedValues = array_combine($preparedIds, $ids);

    $timestart = (!empty($_GET['start']) ? $_GET['start'] : NULL );
    $timeend = (!empty($_GET['end']) ? $_GET['end'] : NULL );

    $statement = $this->connection->prepare("SELECT name AS title, timestart AS start, timestart + timeduration AS end FROM event WHERE courseid IN(" . implode(",", $preparedIds) . ") AND timestart >= :timestart AND timestart + timeduration <= :timeend");
    $statement->setFetchMode(\PDO::FETCH_CLASS, get_class(new EventVO()));

    if($statement->execute(array_merge($preparedValues, ["timestart" => $timestart, "timeend" => $timeend]))) {
        return $statement->fetchAll();
    } else {
        return null;
    }

?>

Also I think you want to put an if statement around your query, since your query will not run if the values of $timestart and $timeend are NULL.

How to run a PDO prepared statement (with named placeholders) in phpmyadmin?

For my setup this is what I ended up doing. Hopefully this solution will help someone else, regardless of my setup details...

1) I turned on query logging in my my.ini file (I'm using Windows / WAMP):

[mysqld]

log = "C:\wamp\logs\queries.log"

2) I needed to tell PDO to emulate prepared statements (I'm using Laravel 4.1):

// Before you run the query...
 $pdo = DB::connection()->getPdo();
 $pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES , true);    
// run query here...
// and then die, so it's the last query ran
die();

3) Keep the queries.log file open in notepad++ and just reload the file when prompted by notepad++. Copy & paste the last query into phpmyadmin. Now I can debug the query!

I'll probably later have to come up with a more automated approach, but this seems to work for now.

ON DUPLICATE KEY UPDATE not working in PDO

You have run into an unfortunate and misleading behavior of PDO's named parameters in a prepared statement. Despite assigning names, you cannot actually use a parameter more than once, as mentioned in the prepare() documentation:

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name twice in a prepared statement. You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

This means you'll need to bind the parameter twice, with two different names, and consequently two different bindValue() calls:

$stmt = $pdo->prepare("
  INSERT INTO b (id, website...) 
  VALUES (:id, :website_insert...)
  ON DUPLICATE KEY UPDATE  
    website=:website_update ...
");

// Later, bind for each
$job_B->bindValue(':id', ...);
// Same value twice...
$job_B->bindValue(':website_insert', $website, PDO::PARAM_STR);
$job_B->bindValue(':website_update', $website, PDO::PARAM_STR);

---

Related Topics