Insert into two related tables

Question

tbl_account
username | password | status

tbl_data
username | name | address | phone

how can i insert data into both tables with php? I tried with query

INSERT INTO tbl_account(username, password, status)
OUTPUT INSERTED.'bobo', 'Bobo Ali','Markt St. 26', '0979877'
INTO tbl_data(username, name, address, phone)
VALUES  ('bobo','bobo123','manager');

but it doesn't work at all. The process is success but the table is remain empty.

Is writing 2 queries not an option? – Naruto Jan 16 '15 at 08:30 — Naruto, Jan 16 '15 at 08:30
yes it's insert data into both tables – Dewi Maida Jan 16 '15 at 09:04 — Dewi Maida, Jan 16 '15 at 09:04

dspacejs · Answer 1 · 2015-01-16T08:35:31.440

You will need seperate queries for inserting the same data into multiple tables.

If you are having trouble with that, the process is simple:

Use the query to insert the data into the first table
Free the last result using mysqli_free_result() (or mysql_free_result() if you are using the MySQL API.
Reuse the same query you used in step 1. to insert the data into the second table

Code:

<?php

INSERT INTO tbl_account(username, password, status)
VALUES ('bobo', 'Bobo Ali','Markt St. 26', '0979877');

// Free the result here

INSERT INTO tbl_data(username, name, address, phone)
VALUES  ('bobo','bobo123','manager');

// Free the result again, so you can use the query again if needed

?>

If it's important that there are booth or neither entries in database (it seems like it is), then you should also have consider to use transaction. — Ján Stibila, Jan 16 '15 at 08:37

score 1 · Answer 2 · edited May 23 '17 at 11:57

1

Use transactions. Alter your database like in the example and make userid primary key (auto increment)

tbl_account
userid | username | password | status

tbl_data
userid | username | name | address | phone

BEGIN;
INSERT INTO tbl_account(username, password, status)
  VALUES ('bobo','bobo123','manager');
INSERT INTO  tbl_data(username, name, address, phone)
  VALUES(LAST_INSERT_ID(),'bobo', 'Bobo Ali','Markt St. 26', '0979877');
COMMIT;

Check also this example if you want an example of php-MySQL(transaction) implementation. PHP + MySQL transactions examples

edited May 23 '17 at 11:57

Community

1
1

answered Jan 16 '15 at 08:44

i have 3 different tbl_data, so like tbl_dataManager, tbl_dataAdmin, and tbl_dataCust and 1 tbl_account, can i use 'userid'? – Dewi Maida Jan 16 '15 at 09:25
yes you can, with an if statement regarding user's account-type (e.g. manager->1, admin->99, cust->2), so if user is admin you insert to dataAdmin, if user is Customer you insert to dataCust, and so on... of course this means that there is a field in tbl_account regarding account type – Jan 16 '15 at 09:37

score 0 · Answer 3 · answered Jan 16 '15 at 08:32

0

you can only do with 2 seperate queries

INSERT INTO tbl_account(username, password, status)
 VALUES ('bobo', 'Bobo Ali','Markt St. 26', '0979877)'

Insert INTO tbl_data(username, name, address, phone)
VALUES  ('bobo','bobo123','manager');

answered Jan 16 '15 at 08:32

DevelopmentIsMyPassion

3,441
4
32
55

score 0 · Answer 4 · answered Jan 16 '15 at 09:22

Yes, you need to have two separate queries. But the answers are a little bit misleading because the table tbl_account has only three columns whereas the provided code inserts values for four columns.

So it should be:

INSERT INTO tbl_account(username, password, status)
VALUES  ('bobo','bobo123','manager');

INSERT INTO tbl_data(username, name, address, phone)
VALUES ('bobo', 'Bobo Ali','Markt St. 26', '0979877');

Or you can write a procedure which these two inserts does.

Insert into two related tables

4 Answers4