Date: 07/18/05 (PHP Community)    Keywords: mysql, sql

is there a function that returns data from newly inserted rows in a mysql table? i have a table i'm inserting rows into, with an auto-incrementing id field. that id is used as a foreign id in another table which i need to insert rows into immediately after. a simple version might look like this:

\USERS\
id | name
1 | tom
2 | jen
3 | sam

\NOTES\
id | foregn_id | note
1 | 1 | note for tom
2 | 1 | note for tom
3 | 1 | note for tom
4 | 2 | note for jen
5 | 3 | note for sam

$sql = "INSERT INTO users (name) VALUES ('bob')";
mysql_query($sql);

so now in the users table we'll probably get

4 | bob

and immediately after in the script i'd need to do

$sql = "INSERT INTO notes (foreign_id,note) VALUES (4,'note for bob')";
mysql_query($sql);

what i'm doing now is constructing a query that finds the id for 'bob', then using it in the follow-up query. so, in total, we'd have

$sql = "INSERT INTO users (name) VALUES ('bob')";
mysql_query($sql);
$sql = SELECT * FROM users WHERE name = 'bob';
$id = mysql_result(mysql_query($sql),0);
$sql = "INSERT INTO notes (foreign_id,note) VALUES ($id,'note for bob')";
mysql_query($sql);

except my code is far messier because i'm dealing with more fields and more data. which brings us back to my original question! is there a cleaner way to get the id of the row i just inserted?

Source: http://www.livejournal.com/community/php/320911.html