how do I update a row if it exists, or create a new one if it doesnt exist in mysql

Question

orgI am trying to get this query correct. I want to insert a record into the database upon form submission but only if the record does not already exist. If the record exists, then I want it to be updated in the database.

What is happening: Upon form submit, a new record is inserted into the database every time. Even if it is a duplicate.

UPDATE: I added a column called "u_id" which holds unique information for each contact in the database. So, I made this my Unique Key column.

 if($_POST['submit']){
       $con=mysqli_connect("localhost","username","password","database_name");
       // Check connection
    if (mysqli_connect_errno())
    {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $contact = ($_POST['contact']);
    $u = ($_POST['uid']);
    $org = mysql_real_escape_string($_POST['organization']);
    $namefirst = mysql_real_escape_string($_POST['firstName']);
    $namelast = mysql_real_escape_string($_POST['lastName']);
    $emailaddy = mysql_real_escape_string($_POST['email']);
    $phonenum = mysql_real_escape_string($_POST['phone']);
    $appquestion = mysql_real_escape_string($_POST['appquestion']);
    $banner = mysql_real_escape_string($_POST['banner']);
    $bulletin = mysql_real_escape_string($_POST['bulletin']);
    $giveaway = mysql_real_escape_string($_POST['giveaway']);
    $app = mysql_real_escape_string($_POST['app']);
    $tshirt = mysql_real_escape_string($_POST['tshirt']);
    $tshirtp = mysql_real_escape_string($_POST['tshirtp']);
    $print = mysql_real_escape_string($_POST['print']);
    $party = mysql_real_escape_string($_POST['party']);
    $orgnotes = mysql_real_escape_string($_POST['notes']);


    $sql="INSERT INTO database_name (contact_id, u_id, first_name, last_name, email_address, phone_number, org, appquestion, banner, bulletin, giveaway, app, tshirt, promised_tee, print, party, org_notes)
      VALUES
          ('$contact', '$u', '$namefirst','$namelast','$emailaddy','$phonenum','$org','$appquestion','$banner','$bulletin','$giveaway','$app','$tshirt','$tshirtp','$print','$party','$orgnotes')   

      ON DUPLICATE KEY UPDATE first_name = '$namefirst', last_name = '$namelast', email_address = '$emailaddy', phone_number = '$phonenum', org = '$org', appquestion = '$appquestion', banner = '$banner', bulletin = '$bulletin', giveaway = '$giveaway', app = '$app', tshirt = '$tshirt', promised_tee = '$tshirtp', print = '$print', party = '$party', org_notes = '$orgnotes'" ;



    if (!mysqli_query($con,$sql))
    {
      die('Error: ' . mysqli_error($con));
    }
      echo "1 record added";



    mysqli_close($con);
    }

From everything I have read, I need to use ON DUPLICATE KEY UPDATE to replace the old information with new information in the database upon form submission. While the insert part of my code is working, the portion with ON DUPLICATE KEY UPDATE is not working.

Why might this portion of the code not be working? Is there a better way to insert else update the information?

I have also tried REPLACE INTO (instead of INSERT and ON DUPLICATE KEY UPDATE), it didn't work either. Here is my column structure in my MySQL database:

+-------------+-------------+------+-----+-----------+-------------------+
Field         |  Type       | Null | Key |  Default  |  Extra   
+-------------+-------------+------+-----+-----------+-------------------+
contact_id    | int(1)      |   NO | PRI |  NULL     |   auto_increment
u_id          | char(32)    |   NO | UNI |  NULL     |
title         | varchar(80) |   NO |     |  NULL     |
first_name    | varchar(100)|   NO |     |  NULL     |
last_name     | varchar(100)|   NO |     |  NULL     |
job_title     | varchar(255)|   NO |     |  NULL     |
address_1     | varchar(255)|   NO |     |  NULL     |
address_2     | varchar(255)|   NO |     |  NULL     |
org_city      | varchar(100)|   NO |     |  NULL     |
org_state     | varchar(100)|   NO |     |  NULL     |
zip_code      | varchar(8)  |   NO |     |  NULL     |
country       | varchar(100)|   NO |     |  NULL     |
phone_number  | varchar(15) |   NO |     |  NULL     |
email_address | varchar(100)|   NO |     |  NULL     |
org           | varchar(150)|   NO |     |  NULL     |
appquestion   | tinyint(1)  |   NO |     |  NULL     |
banner        | tinyint(1)  |   NO |     |  NULL     |
bulletin      | tinyint(1)  |   NO |     |  NULL     |
giveaway      | tinyint(1)  |   NO |     |  NULL     |
app           | tinyint(1)  |   NO |     |  NULL     |
tshirt        | tinyint(1)  |   NO |     |  NULL     |
promised_tee  | tinyint(1)  |   NO |     |  NULL     |
print         | tinyint(1)  |   NO |     |  NULL     |
party         | tinyint(1)  |   NO |     |  NULL     |
org_notes     | varchar(255)|   NO |     |  NULL     |
notes         | varchar(255)|   NO |     |  NULL     |
+-------------+-------------+------+-----+-----------+-------------------+

Thank you for any help or guidance you can give me! I am new to PHP and MySQL. I've been working on this concept for three days and have read a ton of information about it, but am still not able to get it to work.

how do I update a row if it exists, or create a new one if it doesnt exist in mysql

Answers (1)

Related Questions