At 01:05 PM 1/18/2002 -0500, Erik Price wrote:
>I was hoping someone could share with me their code for situations like 
>this, since I'm pretty sure it's a common situation:  I need to insert a 
>record into a table "files", and simultaneously insert a record into a 
>table "files_users".  "files_users" is a foreign key table, which allows a 
>many-to-many relationship between "files" and "users".  There is no direct 
>relationship between "files" and "users".  The "files_users" table has two 
>columns, "files_id" and "users_id".
>What I need is for the filename to be INSERTed as a "file_name" record in 
>the "files" table, and simultaneously the "file_id" of that record (which 
>is automatically generated by MySQL due to the "auto_increment") needs to 
>be INSERTed as a "file_id" record into the "files_users" table, along with 
>the "user_id" of all of the users which were selected.

I'm far from an SQL expert, but I don't know of any way to *simultaneously* 
insert records into more than one table at once....I think you need to send 
them as separate insert statements.  Just insert your record into the files 
table, get the max id out (since it's an auto_increment and you won't know 
in advance what that number is) and then loop through the users, adding a 
record into the files_users table for each one selected.  The following is 
untested, and doesn't do any input validation or error checking, but should 

//$filename was passed from previous page, along with $users (<select 

$query = "insert into files (file_name) values ('".addslashes($filename)."')";
$result = mysql_query($query);

//Get the ID of the file record we just inserted
$query = "select max(file_id) as maxid from files";
$result = mysql_query($query);
$maxid = mysql_result($result,0,"maxid");

//Loop through each selected user and write a record into the files_users table
foreach($users as $user_id) {
   $query = "insert into files_users (file_id,user_id) values 
   $result = mysql_result($query);


The only problem with the above approach is the minuscule possibility that 
a record could be written to the files table in the split second between 
the first and the second query, and then your $maxid would be 
incorrect.  If this is a concern you can lock the tables at the top of your 
script and then unlock them at the bottom...that way other processes would 
have to wait for your script to finish before they could insert any rows 
and that would ensure the integrity of the process.  I personally have 
never done much with table locking in MySQL, but here's the entry in the 

There may be better methods than this, but that's how I'd do it...


PHP General Mailing List (
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to