php retrieving id of the last record inserted and insert it in another table

Question

Hi all,

I'm trying to retrieve the id of the last record inserted in the table 'authors' and insert the retrieved id in the 'articles' table; here's my code:

$title = mysql_real_escape_string($_POST[title]);
$body = mysql_real_escape_string($_POST[body]);
$category = mysql_real_escape_string($_POST[category]);

$insert_author="INSERT INTO authors (name) VALUES ('$title')";
$select_author= mysql_query("SELECT LAST_INSERT_ID()");

$authId = mysql_fetch_array($select_author);
$query="INSERT INTO articles (body, date, category, auth_id) VALUES ('$body', '$date_now', '$category', '$authId')";

this doesn't work...

Any idea please?

Thanks in Advance

Mauro

Answer 1

What exactly doesn't work. Do the $authId contains the expected value? Did you dump it to check?

Apart from that, you forgot the second mysql_query().

Answer 2

You can get the last insert id with mysql_insert_id()

$insert_author="INSERT INTO authors (name) VALUES ('$title')";
mysql_query($insert_author) or die(mysql_error());

$authId = mysql_insert_id();

$query="INSERT INTO articles (body, date, category, auth_id) VALUES
('$body', '$date_now', '$category', '$authId')";

Note that you were missing mysql_query function for insert query, i have added that also in code above.

Answer 3

Try mysql-insert-id instead.

Answer 4

As the name says, mysql_fetch_array($select_author) returns an array so you will need to get the specific value in that array to insert in the other table.