many-to-many relationship INSERT question

Question

i am trying to create a many to many relationship between 2 tables. ive got 3 tables for that. it follows TOXY model.

table a: a.id (primary key) table ab: ab.a_id (foreign key) ab.b_id (foreign key) table b: b.id (primary key)

how should i insert the data so it will be all linked up?

like this? "INSERT INTO a ('name') VALUES ('my name')";

then like this? "INSERT INTO b ('name') VALUES ('my name')";

but then i have to have the a.id and b.id to put it in table ab. how should i retrieve them?

i know i could do a SELECT a.id FROM a WHERE name = 'my name'. but isnt there an easier way for this that automatically returns an id when u INSERT the row?

Answer 1

You can obtain the value stored into the auto_increment primary key column by calling the mysql_insert_id() function:

SELECT LAST_INSERT_ID()

Answer 2

I'm not sure what the TOXY model is, but you would use a simple join:

SELECT * FROM a INNER JOIN ab on a.id=ab.a_id WHERE ab.b_id = {b.id};

and for your instance, {b.id} would be last_insert_id();

Answer 3

All you need to do is store those IDs in variables to use in a query to insert into the ab table. LAST_INSERT_ID() returns the ID of the inserted rows. So, in PHP for instance:

// Run command to insert into A, then:
$a = mysql_query('SELECT LAST_INSERT_ID();');

// Run command to insert into B, then:
$b = mysql_query('SELECT LAST_INSERT_ID();');

// Then $a and $b hold the IDs that you can use to insert into AB.
mysql_query("INSERT INTO ab (a_id, b_id) VALUES ($a, $b);");