MySql and inserting data in a link table, how to?

Question

What is the trick in MySql (Version 4) to write from PHP thtree Inserts and to be sure that we can link tables properly.

[Table1]-[LinkTable]-[Table2]

In the PHP code I create an Insert that add 1 row in table 1 and an other Insert that add an other row in table 2. I get both rows PK (primary key) with two SELECTs that return me the last row of each tables. This way I get the PK (auto-increment) from Table 1 and Table 2 and I insert it on the link table.

The problem is that is not atomic and now that I get a lot of transaction something it doesn't link properly.

How can I make a link between 2 tables in MySQL 4 that preserve data? I cannot use any stocked procedure.

Answer 1

Well, if you have the ability to use InnoDB tables (instead of MyISAM) then you can use transactions. Here's some rough code

<?php

//  Start a transaction
mysql_query( 'begin' );

//  Execute the queries
if ( mysql_query( "insert into table_one (col1, col2) values('hello','world')" ) )
{
    $pk1 = mysql_insert_id();
    if ( mysql_query( "insert into table_two (col1, col2) values('foo', 'bar')" ) )
    {
     $pk2 = mysql_insert_id();
     $success = mysql_query( "insert into link_table (fk1, fk2) values($pk1, $pk2)" );
    }
}

//  Complete the transaction
if ( $success )
{
    mysql_query( 'commit' );
} else {
    mysql_query( 'rollback' );
}

If you can't use InnoDB tables then I suggest looking into the Unit Of Work pattern.

Answer 2

your problem is here:

SELECTs that return me the last row of each tables.

if you did something like this: SELECT MAX(id) FROM table you can get a wrong id for your transaction.

This is a common many-to-many relationship

as BaileyP say you should use the function mysql_insert_id().

at http://ar2.php.net/mysql_insert_id

you can read

Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.

so dont care if other process insert a row in the same table. you always will get the last id for your current connection.