1

I need to insert a new row in to a table, then grab the ID of that row and update another table. This is what I have:

$leadSQL="INSERT INTO $leadsTable (leadName, leadStatus, leadDescription, leadOpportunity, leadSource, leadSourceDescription, id, leadSince, contactID)
                     VALUES ('$_POST[leadName]', '$_POST[leadStatus]', '$_POST[leadDescription]', '$_POST[leadOpportunity]', '$_POST[leadSource]', '$_POST[leadSourceDescription]','$_POST[id]','$leadSince','$_POST[contactID]')";
$leadQuery = mysql_query($leadSQL);
$lastLeadID = mysql_insert_id();
$updateContactSQL = "UPDATE $contactsTable SET leadID = $lastLeadID WHERE contactID = $_POST[contactID]";
$updateContactQuery = mysql_query($updateContactSQL);

Everything works fine.. except that it inserts duplicate rows into the leads table. I have tried putting the update query into an if statement and it did the samething(this was just to try "something"). If I remove $lastID = mysql_insert_id(); it inserts just one row but obviously does not update the contacts table. So I am pretty sure it has to to with mysql_insert_id(). I need it to update the contacts table with the new id of the row inserted into the leads table. Any ideas would be greatly appreciated.

user1916290
  • 13
  • 2

1 Answers1

0

for all intents and purposes what you have should work.

you might try trimming the fat a little bit though. Since you don't need a resource for your query (it's an INSERT), you can get rid of that variable. And I'd just put the mysql_insert_id() in your update statement.

like this: 

$leadSQL="INSERT INTO $leadsTable (leadName, leadStatus, leadDescription, leadOpportunity, leadSource, leadSourceDescription, id, leadSince, contactID)
                     VALUES ('$_POST[leadName]', '$_POST[leadStatus]', '$_POST[leadDescription]', '$_POST[leadOpportunity]', '$_POST[leadSource]', '$_POST[leadSourceDescription]','$_POST[id]','$leadSince','$_POST[contactID]')";
mysql_query($leadSQL);
$updateContactSQL = "UPDATE $contactsTable SET leadID = '".mysql_insert_id()."' WHERE contactID = $_POST[contactID]";
mysql_query($updateContactSQL);
Christopher Johnson
  • 2,629
  • 7
  • 39
  • 70
  • Thanks for the advice, but as suspected this does not solve my issue. Any ideas why it would be inserting duplicate rows? – user1916290 Dec 20 '12 at 02:48
  • I have this: foreach($fields as $a => $b){ $body .= sprintf("%20s: %s\n",$b,$_REQUEST[$a]); } and it is only used to email the $_POST data. The query is outside that foreach. – user1916290 Dec 20 '12 at 03:05
  • I think I have narrowed it down. I have the following code outside the if statement that contains the insert and update queries: if (!mysql_query($leadSQL,$link)) { die('Error: ' . mysql_error()); When I remove it it now longer adds duplicate rows. } – user1916290 Dec 20 '12 at 03:18
  • ok that's your problem then. the !mysql_query($leadSQL) will execute the query function again which would insert another row. – Christopher Johnson Dec 20 '12 at 03:32
  • So do you suggest that I have nothing to check if the query failed? – user1916290 Dec 20 '12 at 03:37