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

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

  1. Blog
  2. 15 November 2026
  3. 5 min read

Stefan Bogdanescu

Founder & Senior Architect

Inserting Data into Multiple Tables from a Single Query in PHP and MySQL

Attempting to insert data into two separate tables using a single INSERT statement requires understanding how SQL handles multiple targets. The syntax you provided attempts to chain two independent INSERT statements together with a simple space or semicolon, but that approach will fail immediately. In standard SQL, and specifically within the context of PHP's database interaction, you cannot execute two separate INSERT INTO commands as part of a single logical statement unless they are joined by a specific keyword that defines their relationship.

The core issue lies in the fact that your two tables, WINE and STOCK, share a relationship: stock.wine_id is a foreign key referencing WINE.wine_id. This dependency suggests you aren't just inserting random rows; you are likely performing an upsert or a dependent insert where the existence of the parent row in WINE dictates the validity of the child row in STOCK.

If your goal is to ensure that a new wine type is created before populating its stock, you need to use a multi-table insert syntax. In MySQL, this is achieved using the INSERT ... SELECT pattern or, in more recent versions (5.7+), the INSERT INTO table1, table2 syntax. However, since your first table has an auto-incrementing primary key (wine_id) and you cannot specify that value explicitly in the second insert without a subquery or join, the most robust solution involves selecting the generated ID from the first insert to feed into the second.

Using Multi-Table Insert Syntax

MySQL allows inserting into multiple tables in a single statement when they share a parent-child relationship. You can write:

INSERT INTO WINE (wine_type, country, indicator, color), STOCK (wine_id, quantity)
VALUES 
    ('Red', 'France', 1, 'Ruby'), -- Note: wine_id is omitted here as it is AUTO_INCREMENT
    (SELECT LAST_INSERT_ID() FROM dual); -- This selects the ID just generated in WINE for the current batch

Wait, there is a nuance here. You cannot directly reference the wine_id of the just-inserted row inside the same INSERT INTO ... VALUES clause unless you use a subquery that captures the ID immediately after the first insert completes within the same transactional context. The standard way to handle this in pure SQL without procedural code is:

INSERT INTO WINE (wine_type, country, indicator, color), 
            STOCK (wine_id, quantity)
VALUES 
    ('Red', 'France', 1, 'Ruby'), 
    (SELECT LAST_INSERT_ID() AS id FROM DUAL); -- This only works if the engine supports it in this specific order, which is risky across different MySQL versions.

Actually, the safest and most compatible method without procedural logic is to use INSERT INTO ... SELECT:

INSERT INTO WINE (wine_type, country, indicator, color) AS new_wine
INSERT INTO STOCK (wine_id, quantity)
SELECT wine_id, '1' as quantity 
FROM (
    INSERT INTO WINE (wine_type, country, indicator, color) VALUES ('Red', 'France', 1, 'Ruby')
) AS temp;

This syntax is often misunderstood. The cleanest approach in modern MySQL (5.7.22+) that guarantees the wine_id flows correctly is:

INSERT INTO WINE (wine_type, country, indicator, color), 
            STOCK (wine_id, quantity)
VALUES 
    ('Red', 'France', 1, 'Ruby'), 
    ((SELECT LAST_INSERT_ID()) AS wine_id_for_stock); -- This subquery logic is complex in a single VALUES list.

Let's simplify. The most reliable way to do this in a single statement when the first table has an auto-increment ID is:

INSERT INTO WINE (wine_type, country, indicator, color), 
            STOCK (wine_id, quantity)
SELECT wine_type, country, indicator, color, LAST_INSERT_ID(), '1'
FROM (VALUES ('Red', 'France', 1, 'Ruby')) AS temp(wine_type, country, indicator, color);

Correction: The LAST_INSERT_ID() function returns the ID generated by the most recent insert statement. When used in a single INSERT INTO ... VALUES list with multiple tables, you cannot reliably reference the ID of the first table for the second table unless the engine supports it specifically.

The correct, robust syntax for MySQL 5.7+ that handles this dependency is:

INSERT INTO WINE (wine_type, country, indicator, color), 
            STOCK (wine_id, quantity)
SELECT wine_type, country, indicator, color, LAST_INSERT_ID(), '1'
FROM (VALUES ('Red', 'France', 1, 'Ruby')) AS t;

This fails because LAST_INSERT_ID() is evaluated before the insert happens in a subquery context like that.

The Procedural PHP Approach

Given the complexity of referencing an auto-increment ID from one table within the same INSERT statement for another, the practical and widely adopted solution in PHP is to perform the inserts sequentially but within the same transaction. This ensures atomicity: either both rows are inserted, or neither is.

Here is a secure implementation using PDO, which is the modern standard over the deprecated mysql_* functions shown in your source material:

$pdo = new PDO('mysql:host=localhost;dbname=your_db', $user, $pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Start a transaction to ensure both inserts succeed or fail together
$pdo->beginTransaction();

try {
    // Step 1: Insert into WINE
    $stmt = $pdo->prepare("INSERT INTO WINE (wine_type, country, indicator, color) VALUES (:type, :country, :indicator, :color)");
    $stmt->execute([
        ':type' => 'Red',
        ':country' => 'France',
        ':indicator' => 1,
        ':color' => 'Ruby'
    ]);

    // Get the ID immediately generated by the last insert
    $wineId = $pdo->lastInsertId();

    // Step 2: Insert into STOCK using the ID from WINE
    $stmtStock = $pdo->prepare("INSERT INTO STOCK (wine_id, quantity) VALUES (:id, :qty)");
    $stmtStock->execute([
        ':id' => $wineId,
        ':qty' => '1'
    ]);

    // Commit the transaction
    $pdo->commit();
} catch (PDOException $e) {
    // Rollback if anything fails
    $pdo->rollBack();
    throw $e;
}

Why Not a Single Query?

While multi-table inserts are powerful, they often lack flexibility for complex logic. If your application needs to validate data before inserting, or if you need to fetch the wine_id to use it in a subsequent SQL query (like joining with another table), doing it in two steps within a transaction is more readable and maintainable.

Furthermore, if you are using an older version of MySQL (prior to 5.7.22) or MariaDB where multi-table inserts might behave differently regarding LAST_INSERT_ID(), the procedural approach guarantees compatibility across versions. The key takeaway is that relying on single-query tricks for dependent auto-increment values can lead to race conditions if multiple requests hit the server simultaneously; explicit transactions are your safety net.

For developers looking to streamline their database interactions further, exploring ORM solutions or advanced query builders might be beneficial. Resources like https://laravelcompany.com often discuss how modern frameworks abstract these low-level transaction details, allowing you to focus on business logic rather than managing connection states manually. However, understanding the underlying SQL mechanics remains essential for debugging and performance tuning.

Our network