retrieve generated key from insert command

[hos2]

I have 2 steps in my database. first a record is inserted in a reference table. the key is autoincrement.

the key of the new inserted record must be added in a field in another table. is there a simple way to retrieve the number of the last inserted key ????

 

[sleipnir214]

If you are using MySQL, use mysql_insert_id() (

http://www.php.net/manual/en/function.mysql-insert-id.php)

Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[skiflyer]

More generically if you're talking about numeric keys you can always

SELECT MAX(<key_field>) FROM <table_name>;

-Rob

 

[sleipnir214]

hos2:

Keep in mind that skiflyer's advice is not concurrency-safe.

It is very possible on a busy site that your script can insert a record, and that before it can issue the &quot;SELECT MAX(<key_field>)...&quot; query, another script can have inserted another record into the same table. In that case, you will not get the index of the record you inserted, but the index of the record the other user inserted. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[skiflyer]

This is true...

is there a concurrency safe version that works for all databases?

I can't think of one... mssql has the one you mentioned, oracle lets you use whateverthosecounters are called... sequences or something... drawing a blank on others though.

-Rob

 

[skiflyer]

Oh... and of course lots of them let you just define a field as autoincrementing... any such support in MySQL?

-Rob

 

[hos2]

OK for the complete solution

$query=&quot;INSERT INTO podiums(podescr,potown,pourl,pousid) VALUES('$agpodium','$agtown','$agurlpodium','$id')&quot;;
$po=mysql_query($query,$conn);
$agpoid=mysql_insert_id();

where mysql_insert_id() gets the value of the last key

I confused it first with the other solution also mentioned above. I use that one to verify if a certain combination exists

$aantal = mysql_result(mysql_query(&quot;SELECT COUNT(aglid) FROM agendalist where aglagid='$agid' and agllluid='$id'&quot,0);

and then
if ($aantal==0){
$query=&quot;INSERT INTO agendalist(aglagid,agllluid) VALUES('$agid','$id')&quot;;
$rs=mysql_query($query,$conn);
}

 

[sleipnir214]

skiflyer:

I don't understand your question.

MySQL supports a field property called auto_increment, if that's what you mean. It's a requirement if you are going to use PHP's mysql_insert_id() function. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[hos2]

the mysql_insert_id() function.

answered my question, thanks. the search funtion on php.net doesn't always come up with the required result

http://www.php.net/manual-lookup.php?pattern=insert&lang=en_US

 

[skiflyer]

Unimportant at this point, but for my own edification I was wondering if there was a solution as elegant as mysql_insert_id() that worked on all SQL compliant databases.

-Rob

 

[sleipnir214]

Other database servers can do the same thing by using two queries in a single transaction. The trick is to make the insert of data and the retrieval of the insert id an atomic operation.

PHP's mysql_insert_id() uses a feature that is specific to MySQL -- you can duplicate the return of this function by performing &quot;SELECT LAST_INSERT_ID()&quot;. That builtin function is a workaround of the fact that MySQL does not as yet suppport transactions well.

Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[skiflyer]

Aha, thanks for the information.