Copying row data into another table

Databases
1

Hi.

I’ve got a query that inserts data into a table.

Query1


"INSERT INTO `dbPosts` (`post`, `username_id`, `private`) VALUES ('$post', '$user', '1')";

When data is inserted it automatically creates an ID (incremented value) in the table row

I’ve got another query in the same php document that inserts data into another table:

Query2


"INSERT INTO `dbPrivate` (`username_id`) VALUES ('$user')";

How do I copy the ID which was automatically inserted in query1 into query2.

2

You need the [fphp]mysql_insert_id[/fphp] function. It will tell you the ID of the row you just inserted.

So, insert the row in the first table, use mysql_insert_id to get that row’s ID, and then insert the data into the second table.

Edit>>
I’m assuming you’re using PHP for this ('cos of the $user variables in your SQL). If you’re not then the mysql_insert_id function in PHP won’t help much :wink:

3

Hi, would this still work if multiple people were using the query?

4

You’re already inserting the same id into both tables. Is there something missing from query 2?

5

No but the LAST_INSERT_ID function in MySQL might. See the PHP manual where it warns you of possible errors in the use of mysql_insert_id and switch to LAST_INSERT_ID directly in the mysql query.