Insert DATA into 2 tables using one Query using PHP and Mysql

Question

I have 2 tables that I am trying to insert data into using PHP

Table: WINE
-----------------------------------------------------------------
|  wine_id  |  wine_type  | country  |  indicator  |  color  |
-----------------------------------------------------------------

wine_id is auto incremented, then This is my other table

Table: stock
 --------------------------------------
 |  stock_id  |  wine_id  | quantity  |
 --------------------------------------

For the STOCK table I have stock ID as Auto incremented and wine_id is foreign-key so All i need to insert is quantity.

I have a syntax like this:

$sql = mysql_query("INSERT INTO TABLE1(field1, field2, field3) VALUES('value1',value2,value3) INSERT INTO STOCK(field) VALUES ('value1')");

If there is another way to do so please suggest and I would like some examples please.

You may also create a trigger on the first table that inserts a row to the second table, that would also work for bulk insert or multiple inserts at once for the first table: https://www.mysqltutorial.org/mysql-triggers/mysql-after-insert-trigger/ — endo64, Feb 01 '21 at 10:59

score 7 · Accepted Answer · answered Mar 13 '14 at 16:10

7

You need to separate your two INSERT statements with a semicolon.

This(mysql_*) extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. Switching to PreparedStatements is even more better to ward off SQL Injection attacks !

answered Mar 13 '14 at 16:10

Shankar Narayana Damodaran

68,075
43
96
126

1

That's what I was going to say – Eligos Mar 13 '14 at 16:10
i do not think that mysql_* support multiple query. http://stackoverflow.com/questions/11106719/php-multiple-mysql-commands-in-one-mysql-query-query – Awlad Liton Mar 13 '14 at 16:15
I can't change my code to mysql* as I have done plenty work into it what do you suggest me doing? @shankar Damodaran – user3311898 Mar 13 '14 at 16:24

score 2 · Answer 2 · answered Mar 13 '14 at 16:11

2

You can run multiple INSERTS once, however you cannot insert into two tables from one single INSERT Statement. Also make sure to get the last inserted ID with LAST_INSERT_ID() for the foreign key and use a semicolon ; between the two INSERTS

answered Mar 13 '14 at 16:11

Levi Kovacs

1,159
8
14

score 2 · Answer 3 · answered Mar 13 '14 at 16:28

2

From the looks of it, what you're trying to do is:

Insert data into the wine table
Get the ID from the wine table
Insert the ID into the stock table

mysql_query does not support multiple queries. So, my suggestion would be:

$result = mysql_query("INSERT INTO `wine` (`wine_type`, `country`, `indicator`, `colour`) VALUES ('Merlot', 'Australia', 'Dry', 'Red')");
$result = mysql_query("INSERT INTO `stock` (`wine_id`, `quantity`) VALUES ('".mysql_insert_id()."', '0');");

Modifying of course to take into account your own variables and value sanitation. As has been mentioned, the mysql_ functions are being deprecated, and you're best to move to a PDO structure for your database in the near future.

answered Mar 13 '14 at 16:28

Eligos

1,104
1
11
26

where would we get mysql_insert_id() – BlueLeaf Apr 07 '18 at 05:24
@BlueLeaf It's a function in PHP. However, as I commented three years ago, the mysql_ functions are deprecated, and you are best to look at PDO alternatives. – Eligos Apr 09 '18 at 09:10
how can we use bind_param in this case? – Azhar Uddin Sheikh Jun 27 '21 at 13:32

Awlad Liton · Answer 4 · 2014-03-13T16:17:11.557

1

you can do it by multiple statement details

like this:

$sql = "INSERT INTO TABLE1(field1, field2, field3) VALUES('value1',value2,value3); INSERT INTO STOCK(field) VALUES ('value1');";

multi_query($conn,$sql);

you need to use mysqli_* for this. I assume $conn is your mysqli connection

edited Mar 13 '14 at 16:17

answered Mar 13 '14 at 16:11

Awlad Liton

9,366
2
27
53

Wher did you get $conn from? @Awlad Liton – user3311898 Mar 13 '14 at 16:19
@user3311898, This answer won't work for you as you are using `mysql_*` functions. – Shankar Narayana Damodaran Mar 13 '14 at 16:20
@user3311898: you must have to use `mysqli_*` for this. see details in the given link and my updated answer – Awlad Liton Mar 13 '14 at 16:22

Insert DATA into 2 tables using one Query using PHP and Mysql

4 Answers4