CODEWITHSUNDEEP
mysqlprepared-statement
Sam van beastlo
Sam van beastlo

Reputation: 849

SQL IF EXIST UPDATE ELSE INSERT prepared statement

I'm breaking my brains over this, i would realy appriciate help!

This is the code i have so far.. 

$conn = db_connect();                       
$sql = "INSERT INTO measurements
(`date`, `weight`, `waist`, `id`) VALUES (?,?,?,?)";
$stmt = $conn-> prepare($sql); 
$stmt ->bind_param("sddi", $date, $_POST['weight'], $_POST['waist'], $user_id);
$stmt->execute();
$stmt->close();
$conn->close();

Its a prepared statement for an sql insert. Now i want to change it to a IF EXIST THEN UPDATE ELSE insert the way i am doing right now. something like this but then with a prepared statement:

IF EXISTS 
(SELECT * FROM measurements WHERE user_id=’4’)
UPDATE measurements SET (`weight`=40, `waist`=45) WHERE user_id=’4’
ELSE
INSERT INTO measurements 
VALUES (`date`='week 1', `weight`= 40, `waist`=45, `id`=4)

I found some articles on stackoverflow about the if EXIST then update else insert but i did not find it with a prepared statement in it that worked for me.

Thanks a thousand!

UPDATE:

i've changed it to dublicate key style. 

$sql = "
INSERT INTO measurements (uniqueID, date, weight, waist) 
VALUES ('$uniqueID', '$date', '$weight',  '$waist') 
ON DUPLICATE KEY UPDATE weight= '$weight', waist= '$waist'";

$conn->query($sql);

Now the second part of the question, how do i make this a prepared statement?

Upvotes: 1

Views: 2341

Answers (2)

Darwin von Corax
Darwin von Corax

Reputation: 5246

To implement Mr. Jones' solution as a mysqli prepared statement, you would code it thus:

$sql = "INSERT INTO measurements
            (`uniqueID`, `date`, weight, waist) 
          VALUES
            (?, ?, ?, ?) 
          ON DUPLICATE KEY
            UPDATE weight = ?, waist = ?";
$stmt = $conn->prepare($sql); 
$stmt ->bind_param("isdddd", $user_id, $date, $_POST['weight'], $_POST['waist'], $_POST['weight'], $_POST['waist']);
$stmt->execute();

A slightly cleaner implementation would be to use PDO:

$sql = "INSERT INTO measurements
            (`uniqueID`, `date`, weight, waist) 
          VALUES
            (:uniqueId, :date, :weight, :waist) 
          ON DUPLICATE KEY
            UPDATE weight = :weight, waist = :waist";
/* $conn is a PDO object */
$stmt = $conn->prepare($sql);
$stmt->execute(array(':uniqueId' => $user_id, ':date' => $date, ':weight' => $_POST['weight'], ':waist' => $_POST['waist']));

Note that with named placeholders, you can use the same name in more than one place and only need to assign the value once.

Upvotes: 2

O. Jones
O. Jones

Reputation: 108651

MySQL's approach to this is INSERT ... ON DUPLICATE KEY UPDATE .... It works well; in particular it avoids race conditions if more than one database connection tries to hit the same row.

This requires the table that's the target of your UPSERT to have a meaningful unique index or primary key. It looks like your id is that key.

You can absolutely use parameter binding to present data to this.

You can read about it here. http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

Upvotes: 0

Related Questions