Mystified by auto increment

[WDrago]

All,

I want to insert data into 6 tables. One of those tables has a primary key field that auto-increments (let's call it ID). The other tables use that ID number as a foriegn key.

My question is, how do I know what the newly generated value of ID is in order to use it in the other tables? Do I have to insert into the first table then read back the value of ID?

Thanks,
Bill

[paulkd]

Yes. If PHP....

http://php.net/manual/en/function.mysql-insert-id.php Although deprecated, has links to alternatives.

[deceptikon]

Database servers will provide a way to query the current ID. For example, in SQL Server you'd select one of the following:

select scope_identity();         -- Last identity generated explicitly in your scope
select ident_current('MyTable'); -- Last identity generated for the specified table

For MySql you might use something like this:

select last_insert_id();
select last_insert_id('MyTable');

Any programming API that supports database access should also provide a convenient (sometimes not so convenient) way of grabbing identities.

[Admin ESSL UK]

mysql_query("INSERT INTO whatevertable ('Bob', 'Smith')");
$id = mysql_insert_id();

Never use SELECT MAX(id) FROM whatevertable