INSERT on two or more tables

[SPrelewicz]

I am new to mySQL and SQL so please bear with me. I am creating a table of users and they are being assigned an AUTO INCREMENTED ID when inserted. I want to copy that ID to another table on the same insert. Is this possible? Is this bad DB design?

Let me explain what I am doing this for. We have some users who authenticate via IP and some with un/pw. I want a master table that creates both with columns that are shared by both, then two child tables (IP_usres, unpw_users) that share specific columns for each, like the username and password for those, and the IP for those who authenticate that way. So what I want to do is when creating a new account, put relevant info in the master users table with the primary key (The auto Inc one) then take that ID and put in in the child table with all that info.

What is the syntax, and does this sound like the correct approach.

As always, I hope this makes sense and thanks for your time in advance.

Scott

 

[sleipnir214]

That's not a bad design.

MySQL provides a builtin function last_insert_id() which returns the number generated by the last auto_increment insert. After you perform your insert, you can do a second query:

SELECT last_insert_id();


The number returned is maintained by the server on a per-connection basis. ______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!