MySql and insert data into link table how?
What is the trick in MySql (version 4) for writing from PHP thtree attachments and so that we can bind tables correctly.
[Table1]-[LinkTable]-[Table2]
In PHP code, I create an Insert that adds 1 row to table 1 and another Insert that adds another row to table 2. I get both PK (primary key) rows with two SELECTs that return me the last row of each table. So I get PK (autoincrement) from Table 1 and Table 2 and I insert it into the link table.
The problem is that it is not atomic and now that I get a lot of transactions, something is not being referenced properly.
How can I make a link between two tables in MySQL 4 that store data? I cannot use any prepared procedure.
source to share
Your problem is here:
SELECTs that return me the last row of each table.
if you did something like this: SELECT MAX (id) FROM table
you might get the wrong id for your transaction.
This is a common many-to-many relationship
as BaileyP says you should use mysql_insert_id () function.
at http://ar2.php.net/mysql_insert_id
you can read
Gets the identifier generated for the AUTO_INCREMENT column by a previous INSERT query.
so don't worry about another process inserting a row into the same table. you will always get the latest ID for your current connection.
source to share
Well, if you have the ability to use InnoDB tables (instead of MyISAM), you can use transactions. Here is some example 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, I suggest looking into the Unit Of Work pattern .
source to share