copying row data into another table

[Jaynesh]

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.

[Immerse]

You need the mysql_insert_id 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

[Jaynesh]

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

[SpacePhoenix]

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

[guelphdad]

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

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.