How to use t1's ID with LAST_INSERT_ID() for an insert ID in t2 ??

[rogerzebra]

I'm back with new problems. (I wish I was in the other end

So anyway here it goes.
I'm trying to use the last_insert_id function in order for me to make a copy of the id in t1 and insert it in t2. By this point I'm not sure if my query is incorrect or if it is something else I should change to have this working. When I execute a submission it's register an auto_increment id as it should in t1. If I direct after that run a SELECT LAST_INSERT_ID()query in Mysql, the result gives me a 0. Does anyone know what's wrong here? I'm using InnoDB tables with MySQL version 4.1.7 and beneth is my query.

$query= mysql_query("INSERT INTO submissions (NameInsured,..)
VALUES ('$NameInsured',..., )")
or die (mysql_error());

$query= mysql_query("INSERT INTO test SET EvaluationID=SubmissionID WHERE SubmissionID=LAST_INSERT_ID()")

I'm greatful and open for any suggestions.
Thanks

 

[sleipnir214]

I recommend that you not try to fetch the insert it from within your query.

Instead, if your INSERT query generates an auto_increment ID, then use mysql_insert_id() to fetch the generated ID and use it in your next query.

Also, the WHERE clause should not appear in an INSERT query.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[rogerzebra]

sleipnir,
hi again and thanks for your fast respond. I was just thinking of the diffencies between the functions last_insert_id and mysql_insert_id.
My question is if its safe to use mysql_insert_id function when you have 20 people executing form submissions simultaniously. I'm abit confused what to use here, anyhow here is a quote I found from the manual.

The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions.

...thanks for pointing out the where clause. I guess I should use a value together with an insert, but what to use when you don't have a value and the value is an auto_increment integer? Maybe that is a stupid question but, I'm still learning and can't find any information in a post or books for that matter.

cheers
/rz

 

[rogerzebra]

sleipnir,
thanks for helping me out. I solved this using the last_insert_id it works fine with an auto increment.
The value to use is ofcourse a NULL value sorry for my confusion. To tired, anyhow mark this post as *SOLVED*
thanks/rz