Re: INSERT statement for JOINed tables

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 work: <? //$filename was passed from previous page, along with $users (<select name="users[]">) $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 ($maxid,$user_id)"; $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 manual: http://www.mysql.com/doc/L/O/LOCK_TABLES.html There may be better methods than this, but that's how I'd do it... HTH