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:
This means you'll need to bind the parameter twice, with two different names, and consequently two differentYou 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.
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
How to Create a PHP Function That I Can Call Without Parentheses
What Is the Best Method for Getting a Database Connection/Object into a Function in PHP
Laravel Unexpected Redirects ( 302 )
Laravel - Validate File Size When PHP Max Upload Size Limit Is Exceeded
Callback Function Using Variables Calculated Outside of It
PHP Sort a Multidimensional Array by Number of Items
Posting to a Facebook Page as the Page (Not a Person)
Base64 Over Http Post Losing Data (Objective-C)
How to Get an Array of Data from $_Post
Expression Is Not Allowed as Field Default Value
PHP Local Server Invalid Request (Unexpected Eof)
How to Access Outer Local Variable in PHP
How to Convert Emoji from Unicode in PHP
Yii Framework: Using Data from Related Active Record Models for Searching
Adding an Additional Custom Field in Woocommerce Edit Account Page
What's the Difference Between Post and Raw Post in PHP at All