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: mysqltutorial.org/mysql-triggers/mysql-after-insert-trigger — endo64
– endo64, Commented Feb 1, 2021 at 10:59

Shankar Narayana Damodaran · Accepted Answer · 2014-03-13 16:10:08Z

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, 2014 at 16:10

Shankar Narayana Damodaran

68.6k43 gold badges102 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Eligos Over a year ago

That's what I was going to say

Awlad Liton Over a year ago

i do not think that mysql_* support multiple query. stackoverflow.com/questions/11106719/…

user3311898 Over a year ago

I can't change my code to mysql* as I have done plenty work into it what do you suggest me doing? @shankar Damodaran

Levi Kovacs · Accepted Answer · 2014-03-13 16:11:49Z

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, 2014 at 16:11

Levi Kovacs

1,1698 silver badges14 bronze badges

Comments

Eligos · Accepted Answer · 2014-03-13 16:28:36Z

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, 2014 at 16:28

Eligos

1,1321 gold badge11 silver badges26 bronze badges

3 Comments

BlueLeaf Over a year ago

where would we get mysql_insert_id()

Eligos Over a year ago

@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.

Azhar Uddin Sheikh Over a year ago

how can we use bind_param in this case?

Awlad Liton · Accepted Answer · 2014-03-13 16:17:11Z

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, 2014 at 16:17

answered Mar 13, 2014 at 16:11

Awlad Liton

9,3513 gold badges30 silver badges53 bronze badges

3 Comments

user3311898 Over a year ago

Wher did you get $conn from? @Awlad Liton

Shankar Narayana Damodaran Over a year ago

@user3311898, This answer won't work for you as you are using mysql_* functions.

Awlad Liton Over a year ago

@user3311898: you must have to use mysqli_* for this. see details in the given link and my updated answer

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

Comments

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related