Most efficient way to insert two rows that depend on each other.

Question

I have a site that is essentially a collection of links. A mysql database which stores users, links and votes. My links table has two foreign keys, user_id and vote_id. When a link is inserted into the database it will start with one vote so that means I need to insert a row into the votes table but they essentially depend on one another to exist. I need the ID of the links for the foreign key of the votes table and vice versa. My current "plan" is to insert a links row with a vote_id of 0, select the same row to get it's ID and then insert a votes row using the links row id as it's foreign key, select it's ID and update my original links row. This seems really inefficient but I need to make sure users votes are recorded for time keeping and eliminating duplicate votes. Am I going about this the wrong way?

Answer 1

In PHP, you can use mysql_insert_id:

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());

You could use this to get the row ID of the newly inserted link.

Assuming one vote can only belong to one link, the links table should not have a vote_id column. So you shouldn't need the identity of the newly inserted vote.