SQL - IF EXISTS UPDATE ELSE INSERT INTO

Question

What I'm trying to do is INSERT subscribers in my database, but IF EXISTS it should UPDATE the row, ELSE INSERT INTO a new row.

Ofcourse I connect to the database first and GET the $name, $email and $birthday from the url string.

$con=mysqli_connect("localhost","---","---","---"); // Check connection if (mysqli_connect_errno())   {   echo "Failed to connect to MySQL: " . mysqli_connect_error();   }  $name=$_GET['name'];  $email=$_GET['email']; $birthday=$_GET['birthday']; 

This works, but just adds the new row;

mysqli_query($con,"INSERT INTO subs (subs_name, subs_email, subs_birthday) VALUES ('$name', '$email', '$birthday')");  mysqli_close($con); 

Here's what I tried;

mysqli_query($con,"INSERT INTO subs (subs_name, subs_email, subs_birthday) VALUES '$name', '$email', '$birthday' ON DUPLICATE KEY UPDATE subs_name = VALUES($name), subs_birthday = VALUES($birthday)"); mysqli_close($con); 

and

mysqli_query($con,"IF EXISTS (SELECT * FROM subs WHERE subs_email='$email')     UPDATE subs SET subs_name='$name', subs_birthday='$birthday' WHERE subs_email='$email' ELSE     INSERT INTO subs (subs_name, subs_email, subs_birthday) VALUES ('$name', '$email', '$birthday')"); mysqli_close($con); 

and

mysqli_query($con,"IF NOT EXISTS(SELECT * FROM subs WHERE subs_email='$email') Begin INSERT INTO subs (subs_name, subs_email, subs_birthday) VALUES ('$name', '$email', '$birthday') End"); mysqli_close($con); 

But none of them work, what am I doing wrong?

Any help is greatly appreciated!

Answer 1

1. Create a UNIQUE constraint on your subs_email column, if one does not already exist:

   ALTER TABLE subs ADD UNIQUE (subs_email) 

2. Use INSERT ... ON DUPLICATE KEY UPDATE:

   INSERT INTO subs   (subs_name, subs_email, subs_birthday) VALUES   (?, ?, ?) ON DUPLICATE KEY UPDATE   subs_name     = VALUES(subs_name),   subs_birthday = VALUES(subs_birthday) 

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE - dev.mysql.com

3. Note that I have used parameter placeholders in the place of string literals, as one really should be using parameterised statements to defend against SQL injection attacks.