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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
