INSERT statement for JOINed tables

Hi everyone, I have a database-related question that I was hoping some here could help me with. It sounds at first like it would be more appropriate for the MySQL mailing list, but actually it's a PHP problem. 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". files +-----------+--------------------+-----+----------------+

| Field     | Type               | Key | Extra          |

+-----------+--------------------+-----+----------------+ | file_id | mediumint unsigned | PRI | auto_increment |

| file_name | varchar(64)        |     |                |

+-----------+--------------------+-----+----------------+ users +-----------+--------------------+-----+----------------+

| Field     | Type               | Key | Extra          |

+-----------+--------------------+-----+----------------+ | user_id | mediumint unsigned | PRI | auto_increment |

| user_name | varchar(64)        |     |                |

+-----------+--------------------+-----+----------------+ files_users +-----------+--------------------+-----+----------------+

| Field     | Type               | Key | Extra          |

+-----------+--------------------+-----+----------------+

| file_id   | mediumint unsigned | PRI |                |
| user_id   | mediumint unsigned | PRI |                |

+-----------+--------------------+-----+----------------+ There are two UNIQUE indexes on "files_users": (file_id, user_id) and (user_id, file_id). This is because duplicate rows are unnecessary. /* I hope this makes sense so far. */ So, I would like to create a PHP page that has a form with a text input box where the user can type in a filename. The form also has a listbox (<select multiple="yes" size="5"> tag) that is dynamically populated with the names of each "user" in the "users" table (this is already done, and the value of each <option> tag is the user_id of that user). <select multiple="yes" size="10"> <option value="1">Prince Adam</option> <option value="2">Teela</option> <option value="3">Orko</option> <option value="4">Skeletor</option> </select> <!-- note that in the real code, there is a lot of PHP here that dynamically echoes the appropriate names and values, it's not static like this, but the result is the same --> Sounds easy, doesn't it? When the form is submitted, the text input sends the value of the filename, and the values of any users that were selected (several can be selected by holding down Cmd/Alt due to the "multiple" attribute of the <select> tag). 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. So if you were using this page, and you typed "greyskull.txt" into the text input box, and selected "Prince Adam" and "Skeletor" from the listbox, and hit "Submit"... 1) "greyskull.txt" would be INSERTed into "files.file_name" 2) the "files.file_id" is automatically entered into this record because it is an "auto_increment" column, let's say it's "57". 3) the "files.file_id" value ("57") is INSERTed into "files_users.file_id", and at the same time, the value of the selected users gets INSERTed ("Prince Adam" has a value of "1", and "Skeletor" has a value of "4") 4) so now the contents of the tables should look like this: SELECT * FROM files WHERE file_name LIKE 'greyskull.txt'; +---------+---------------+

| file_id | file_name     |

+---------+---------------+

|      57 | greyskull.txt |

+---------+---------------+ SELECT * FROM files_users; +---------+---------+ | file_id | user_id | +---------+---------+

|      57 |       1 |
|      57 |       4 |

+---------+---------+ SELECT * FROM users; +---------+---------------+

| user_id | user_name     |

+---------+---------------+

|      1  | Prince Adam   |
|      2  | Teela         |
|      3  | Orko          |
|      4  | Skeletor      |

+---------+---------------+ I can only assume that this is a common problem -- can anyone share with me some code that is the "standard" way to handle this? Thanks in advance for your help very much!! Erik