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.

Answer 1

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 !

Answer 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

Answer 3

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

1. Insert data into the wine table
2. Get the ID from the wine table
3. 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.