CODEWITHSUNDEEP
mysql
rlemon
rlemon

Reputation: 17666

MySql Insert to multiple tables based on the ID generated in the first table

I have three tables in my database. A users table, StoreA and StoreB

StoreA and StoreB both have a unique key which is the user ID value.

What I want is; When I create a user and insert them into the database, how can I Insert a row into the other two tables without too many additional queries.

I figure I can do this by inserting the user in one query, then in another return the newly created user ID, then in another, using said ID, create rows in StoreA and StoreB

Can I cut out the middle query?

Upvotes: 2

Views: 7191

Answers (5)

ahnbizcad
ahnbizcad

Reputation: 10797

I just wanted to share a php solution.

If you're using mysqli, first execute your insert query. Then do

$db_id = $this->db->insert_id;

Upvotes: 0

ajreal
ajreal

Reputation: 47321

YES

 Q1: insert into table1 values (...);
 Q2: insert into table2 values (last_insert_id(), ...);

last_insert_id is the default mysql build-in function

Most of the mysql libraries in various programming language did support return last insert id.
But You did not mention what sort of language you are using to connect to mysql.,
so cannot provide any example

Upvotes: 0

Johan
Johan

Reputation: 76537

Can I cut out the middle query?

YES

START TRANSACTION;
INSERT INTO user (id, name, other) 
          VALUES (null, 'John','rest of data');
INSERT INTO storeA (id, user_id, other) 
            VALUES (null, @user_id:= LAST_INSERT_ID(), 'rest of data');
INSERT INTO storeB (id, user_id, other) 
            VALUES (null, @user_id, 'rest of data');
COMMIT;

See: http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

It's a good idea to do this in a transaction, you you're not stuck with just a user with no other data if something goes wrong.
It's not a DB requirement though.

Upvotes: 7

Steve's a D
Steve's a D

Reputation: 3821

Why don't you use their username as the primary key instead of creating an arbitrary user_id field thats auto incremented? Their user names are unique, right?

Upvotes: -1

Sam Dufel
Sam Dufel

Reputation: 17598

Yes - there should be a function available to get the last inserted ID (assuming it's an autoincrement field) without another query. In PHP, it's mysql_insert_id(). Just call that after the first query.

Upvotes: 2

Related Questions