Upon form submit, if record exists update it, else insert

[kjetterman]

I 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 entered into the database every time.  Note:  The contact_id column is both primary key and unique in my database.  Here is my code:

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();
    }
	
	$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, first_name, last_name, email_address, phone_number, org, appquestion, banner, bulletin, giveaway, app, tshirt, promised_tee, print, party, org_notes)
      VALUES
      ('','$namefirst','$namelast','$emailaddy','$phonenum','$churchorg','$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?

 

Thank you for any help or guidance you can give me!  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.

[Ch0cu3r]

 

 

 The contact_id column is both primary key and unique in my database.

If it is also auto_increment then that will not work as the the new id value is generated when the record is inserted. 

 

You need to set other columns in your table to be unique too, such as the email address. When a column is set to unique mysql will check to make sure the data being entered for that column is unique. If it is not then it'll trigger the ON DUPLICATE KEY clause.

[kjetterman]

If it is also auto_increment then that will not work as the the new id value is generated when the record is inserted. 

 

You need to set other columns in your table to be unique too, such as the email address. When a column is set to unique mysql will check to make sure the data being entered for that column is unique. If it is not then it'll trigger the ON DUPLICATE KEY clause.

 

Thank you for your answer!  The problem is that some of the contacts in the database share the same email address because they come from the same organization.  Some of the first and last names are the same too...

[kjetterman]

Okay, so I took all of the records in the database and gave each of them a unique identifier.  My new "Unique" key is named u_id.  I modified my query a bit but the ON DUPLICATE KEY clause does not appear to be working still.  Any thoughts?

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();
    }
	
	$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
      ('', '','$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);
}

[Barand]

 

does not appear to be working still.

 

Means what?

 

Do you get an error message? If so, what is it?

 

What is the table structure (output from the query "SHOW CREATE TABLE database_name")?

[kjetterman]

Upon pressing the submit button for my form, I have two goals

 

1.  If it is a record that already exists in my database, then update said record. 

2.. If the record does not exist in the database, create it.

 

I added a column called u_id and assigned it as a unique key.  Or at least I thought I did.

 

After running the query you posted above,  I got the following results:

 

Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.

 

Here is my current table structure (if it helps):

+-------------+-------------+------+-----+-----------+-------------------+
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(  |   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     |
+-------------+-------------+------+-----+-----------+-------------------+

I do not get an error message.  Basically, anytime a current record is pulled up and change and then submitted via the form, it adds a new record instead of updating the record that the information was pulled from.  It would seem then that perhaps I don't understand the process of making a column Unique and how it translates to the two goals listed above.  What should I have done?

Edited October 5, 2014 by kjetterman

[Barand]

You have 2 unique keys in that table (contact_id and u_id). Your insert query provides no value for either of them therefore not duplicating a key value that already exists