I'd like to switch PDO INSERT and UPDATE prepared statements to INSERT and ON DUPLICATE KEY UPDATE since I think it'll be a lot more efficient than what I'm currently doing, but I'm having trouble figuring out the correct syntax to use with named placeholders and bindParam.
I found several similar question on SO, but I'm new to PDO and couldn't successfully adapt the code for my criteria. This is what I've tried, but it doesn't work (it doesn't insert or update):
try {
$stmt = $conn->prepare('INSERT INTO customer_info (user_id, fname, lname) VALUES(:user_id, :fname, :lname)'
'ON DUPLICATE KEY UPDATE customer_info SET fname= :fname,
lname= :lname
WHERE user_id = :user_id');
$stmt->bindParam(':user_id', $user_id);
$stmt->bindParam(':fname', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname', $_POST['lname'], PDO::PARAM_STR);
$stmt->execute();
}
This is a simplified version of my code (I have several queries, and each query has between 20 - 50 fields). I'm currently updating first and checking if the number of rows updated is greater than 0 and if not then running the Insert, and each of those queries has it's own set of bindParam statements.
In layman's terms, PDO prepared statements work like this: Prepare an SQL query with empty values as placeholders with either a question mark or a variable name with a colon preceding it for each value. Bind values or variables to the placeholders. Execute query simultaneously.
PDO—PHP Data Objects—are a database access layer providing a uniform method of access to multiple databases. It doesn't account for database-specific syntax, but can allow for the process of switching databases and platforms to be fairly painless, simply by switching the connection string in many instances.
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);
IMHO below is the right answer for anyone coming across this again.
Note: this statement assumes user_id is a KEY in the table.
The STATEMENT indeed was wrong, but the accepted answer was not completely correct.
If you're inserting and updating using the same values (and not updating with different values), this is the query pseudo-code corrected:
try {
//optional if your DB driver supports transactions
$conn->beginTransaction();
$stmt = $conn->prepare('INSERT INTO customer_info (user_id, fname, lname) ' .
'VALUES(:user_id, :fname, :lname)' .
'ON DUPLICATE KEY UPDATE fname=VALUES(fname), lname=VALUES(lname)');
$stmt->bindParam(':user_id', $user_id);
$stmt->bindParam(':fname', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname', $_POST['lname'], PDO::PARAM_STR);
$stmt->execute();
//again optional if on MyIASM or DB that doesn't support transactions
$conn->commit();
} catch (PDOException $e) {
//optional as above:
$conn->rollback();
//handle your exception here $e->getMessage() or something
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With