How do I insert data into multiple tables in single query - Mysql

Question

I have 8 query's to insert into 8 tables. I have tried with this But no use

$mysql_db_hostname = "localhost";
$mysql_db_user = "root";
$mysql_db_password = "";
$mysql_db_database = "emp";

$con = mysqli_connect($mysql_db_hostname, $mysql_db_user, $mysql_db_password) or die("Could not connect database");
mysqli_select_db($con, $mysql_db_database) or die("<div class='loginmsg'>Could not select database</div>");

if(mysqli_multi_query($con,"INSERT INTO t_emp (`e_id`,``,``) VALUES ('','',''); INSERT INTO t_emp_add (`e_id`,``,``) VALUES ('','',''); INSERT INTO t_emp_att (`e_id`,``,``) VALUES ('','',''); INSERT INTO t_emp_dep (`e_id`,``,``) VALUES ('','','');.....(etc);"))
{
 echo "Inserted";
}
else{
 echo "Not Inserted";
 }

Is there any method to store into multiple tables..?

That isn't how multi query works. RTM http://php.net/manual/en/mysqli.multi-query.php or this http://www.w3schools.com/php/php_mysql_insert_multiple.asp — Funk Forty Niner, Nov 03 '15 at 13:35
you also have a syntax error here echo "Not Inserted" and you need to use proper error handling http://php.net/manual/en/mysqli.error.php — Funk Forty Niner, Nov 03 '15 at 13:36
you can use trigger https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html — Omesh, Nov 03 '15 at 13:40
My above comment about the syntax error should have been and/or contained http://php.net/manual/en/function.error-reporting.php — Funk Forty Niner, Nov 03 '15 at 14:11
Then you woudl be better advised to use a transaction rather than a multi_query, as any one of you 8 INSERTS might fail and you would not know which one had failed and your database would be in a mess — RiggsFolly, Nov 07 '15 at 14:36
Pity, I am not sure what mysqlind 5.0.10 means. Is that MySQL 5.0.10 To use transactions you have to be using MYSQL 5.6 or greater — RiggsFolly, Nov 07 '15 at 14:48
@RiggsFolly, `mysqlind 5.0.10` would mean the Native Driver seen thru calling `phpinfo();` in php — Drew, Nov 07 '15 at 15:27
@Drew Thanks I sort of knew that and was trying to get her to look for that actual MYSQL version number rather than the Driver version. — RiggsFolly, Nov 07 '15 at 16:00
It all in [the manual](http://php.net/manual/en/mysqli.begin-transaction.php) Start here — RiggsFolly, Nov 07 '15 at 16:04

score 2 · Answer 1 · edited Feb 15 '23 at 08:13

2

MySQL doesn't support multi-table insertion in a single INSERT statement.

The problem that you are trying to solve by using a "single query" is solved by using transactions:

mysqli_query($con,"BEGIN");
mysqli_query($con,"INSERT INTO tab1 (col1, col2) VALUES('1', '2')");
mysqli_query($con,"INSERT INTO tab2 (col1, col2,col3) VALUES(1,2,3)");
mysqli_query($con,"COMMIT");

edited Feb 15 '23 at 08:13

Your Common Sense

156,878
40
214
345

answered Nov 03 '15 at 13:35

Abhishek Sharma

6,689
1
14
20

I am new to php and mysql. can you please elaborate how can I use this code means where do I insert connection string and how to implement if condition for successful insertions and failures – angel Nov 03 '15 at 14:07
there is nothing to suggest that this is transactional, especially for an OP that says she is new to php and mysql. Is it a bad idea, no. – Drew Nov 07 '15 at 16:13

score 0 · Answer 2 · answered Nov 03 '15 at 13:37

0

No, MySQL hasn't expression for inserting by one query in multiple tables.

If you want, you can use MySQL stored procedures or functions and/or use transactions for that.

answered Nov 03 '15 at 13:37

Anton Ohorodnyk

891
5
20

@Machavity OP posted the same question now http://stackoverflow.com/q/33583542/ and it has been suggested she uses just that. That's a repost/duplicate as per how Stack sees it. – Funk Forty Niner Nov 07 '15 at 14:06
I see no problem with this answer – Drew Nov 07 '15 at 16:17

score 0 · Answer 3 · answered Nov 07 '15 at 14:16

0

As the question was closed in other posting, I'll put this here - to create a stored procedure:-

delimiter $$
CREATE PROCEDURE `spInsertTemp`( IN `p_emp_no` INT, IN `p_e_id` INT, IN `p_att_s_no` INT, IN `p_dep_s_no` INT )
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
begin
    insert into `t_emp` (`emp_s_no`,`e_id` ) values ( p_emp_no, p_e_id );
    insert into `t_emp_add` ( `e_id` ) values ( p_e_id );
    insert into `t_emp_att` (`att_s_no`,`e_id` ) values ( p_att_s_no, p_e_id );
    insert into `t_emp_dep` ( `dep_s_no`, `e_id`) values ( p_dep_s_no, p_e_id );
end $$;
delimiter ;

Run that in your gui, use like:-

$sql='call `spInsertTemp`(1,2,4,5);';

answered Nov 07 '15 at 14:16

Professor Abronsius

33,063
5
32
46

$sql='call `spInsertTemp`(1,2,4,5);'; what are these 1,2,3,4,5..? – angel Nov 07 '15 at 14:19
They my friend are example parameter values that represent the various parameters of the storedprocedure. The sp has 4 params, hence 4 values – Professor Abronsius Nov 07 '15 at 14:21
@RamRaider I think you're in for a long haul with this one. I'm not insulting this person by saying this, but it's obvious that she hasn't grasped the SQL concept. In her other question http://stackoverflow.com/q/33583542/, she mentions a form *"@RiggsFolly first time means when I submit the form at first time ( First record ) – angel"* and there is no code to support the question, nor does she check why it failed. `echo "Not Inserted";` does nothing to tell her the REAL reason as to why her query failed. I doubt her questions will ever be resolved. I sincerely wish her well. – Funk Forty Niner Nov 07 '15 at 14:26
@Fred-ii- You could well be right ~ I realise too late alas! Let me join you in wishing her well – Professor Abronsius Nov 07 '15 at 14:29
@RamRaider I know I'm right; I feel it in my bones ;-) But I'll upvote for your trouble and that is a good example of using procedures. *Cheers* – Funk Forty Niner Nov 07 '15 at 14:30

score 0 · Answer 4 · answered Nov 21 '15 at 16:08

0

You can use After Insert Trigger to insert record in multiple table. It will automatically fired when a insert statement is excuted within that database.

answered Nov 21 '15 at 16:08

Avinash Kumar

51
6

How do I insert data into multiple tables in single query - Mysql

4 Answers4