Inserting same data into 2 tables at once, but field 1 is auto-incremement

[sleepyw]

I have 2 tables with the same fields. Table 1 is the master list, table 2 is being used as an audit log.

 

Here's the problem: when I create a new record and insert it into table 1, there is a numeric field that is set to auto-increment and gets automatically populated in table 1, so it's a unique number that no one knows until the record is submitted into the db.

 

But since I have the PHP written to submit this data into 2 tables at the same time, that field in table 2 doesn't get updated.

 

How do I grab that auto-incremented field from table 1 and make sure that field gets copied into table 2?

[xylex]

mysql_insert_id()

 

But if table 2 is an audit of anything that happens in table 1, is there any reason you're not doing this with triggers?  Otherwise, you should really be doing this transactionally to deal with failures so you're not populating one table and not the other.

[sleepyw]

Setting it up with triggers is much more complicated (for me) and time consuming. This is the quick and dirty way and does exactly what I want (copies all info into a single row). Except for this one part when I insert a new item.

[BobcatM]

If you want to do it dirty like you say, after you insert the data into table one, run a query right after that and see what the last one was you inserted. Like this -

 

$sql = "SELECT num FROM table ORDER BY num desc limit 1";

$result = mysql_query($sql);

 

Then turn around and insert that back into to table 2.

[sleepyw]

That's what I'm trying to do....just couldn't figure out how to do it (still new to PHP). Thanks - I'll give it a try.

[BobcatM]

Np if you post some code I can try and help you.

[ohdang888]

theres no idea to write another query to get the value in the auto increment column of the last insert

 

you can get it like this:

 

$id = mysql_insert_id();

 

OR, this:

 

$res = mysql_query(" LAST_INSERT_ID() ")or die(mysql_error());

[sleepyw]

Bobcat's code didn't work. Seems like it would, but it returns a 0. I'll try ohdang's.

 

EDIT: That doesn't work either - getting an error on the LAST_INSERT line. My field is called ITEM_NUM, so I have LAST_INSERT_ITEM_NUM. Not sure if that's what's causing the problem.

[Ken2k7]

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

[BobcatM]

Post your code...

 

and what is your table name?

[sleepyw]

Regarding the LAST_INSERT_ID() - I'm doing this:

 

$itemnum = mysql_query("LAST_INSERT_ID() ")or die(mysql_error());

 

And then inserting the new data into table2 and calling for $itemnum. But I'm still getting an error on the LAST_INSERT code.

 

 

 

Bobcat:

 

I tried this:

 

$itemnum = "mysql_query(SELECT Item_Num FROM TABLE1 ORDER BY Item_Num DESC LIMIT 1)";

 

But it still returns a 0.

[sleepyw]

Got it. Here's what I had to do:

 

<?php 
$itemlast = mysql_query("SELECT Item_Num FROM TABLE1 ORDER BY Item_Num DESC LIMIT 1") or die(mysql_error());
while($item=mysql_fetch_array($itemlast))
{	
$itemnum=$item["Item_Num"];
}?>

 

Then I plugged in $itemnum where I wanted the number to appear. I used Bobcat's code and grabbed some code I used elsewhere on the site to make it work.

 

Thanks for the help everyone! You all gave me a good starting place to get this fixed.

[xylex]

You built in a race condition there.  If you have two inserts that happen at the same time, that $itemlast value is going to be the same higher value for both threads processing this statement, referencing whichever one MySQL processed first.

 

mysql_insert_id() in PHP or LAST_INSERT_ID() in MySQL both stay within the current connection handle, which is unique to each thread and won't change unless you tell it to while processing your script, so it eliminates this race condition.