Insert last id from one table into another

Question

I have two tables, articles and logs. When new record is inserted into the articles table the ID of that article should be inserted into the logs table after that... How this can be done using mysql/php? Can anyone please provide an example of code?

Answer 1

After an insert query through PHP's mysql_query(), you can get the ID by calling the mysql_insert_id() function. Then you can run your log insert query.

This could also be accomplished automatically if you created an insert trigger on the articles table.

Answer 2

In MySQL you could use

SELECT @@IDENTITY AS ID;

to retrieve the last inserted row's id.

Answer 3

SELECT SCOPE_IDENTITY()

More information here

Answer 4

one very simple example

$query = "INSERT INTO article(article_title, article_body) VALUES('some title', 'some text')";
    mysql_query($query);

$query = "INSERT INTO logs(article_id, user_id) VALUES(" . mysql_insert_id() . ", 1)";
mysql_query($query);

Answer 5

You can write a database trigger for this. so You need not to care every time you insert in article table. trigger will automatically get fired.

CREATE TRIGGER insert_article AFTER INSERT ON articles
     BEGIN
         insert into logs( article_id ) values ( scope_identity());
     END;