Erik,
You have to do two inserts. Have a look at
http://www.php.ca/manual/en/function.mysql-insert-id.php
Do your first insert
Call mysql-insert-id(), capturing its output.
Use the value returned in your second insert.
The returned value is unique to your connection; no need to lock the first
table or anything like that. If you look in this week's archives you will
find an extensive discussion on this topic. The beauty is that this
duplicates, in PHP, MySQL's last_insert_id() function.
Cheers - Miles
At 01:05 PM 1/18/2002 -0500, Erik Price wrote:
>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
>
>
>--
>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]
--
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]
