运行插入查询抓取ID并使用Id更新另一个表

Question

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.

Answer 1

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);