Running two SQL queries in PHP where the first statement creates a temporary table to be used by the second one

Question

I want to run this long SQL query in PHP.

CREATE TEMPORARY TABLE IF NOT EXISTS info AS (
  SELECT warehouse.merchandise_id, SUM(warehouse.prod_quantity) AS qty 
  FROM warehouse 
  WHERE warehouse.merchandise_id IN (SELECT merchandise.id FROM merchandise) 
  GROUP BY warehouse.merchandise_id
); 
SELECT LPAD(`id`,8,'0'), prod_title, prod_lcode, prod_price, qty 
FROM `merchandise` INNER JOIN info 
  ON merchandise.id = merchandise_count.merchandise_id;

Here's a quick explanation of what it does: First it creates a temporary table to store some selected data, then it uses the temporary table to INNER JOIN it with data in a permanent table.

I have already tried '$statement1;$statement2;' in PHP, but it gives syntax and access violation error but the given query works flawlessly in phpmyadmin.

I checked other similar posts like this and they suggest to use '$statement1;$statement2;' but it doesn't work for me. My server is running PHP 7. I'm using PHP PDO to connect to my database. Any help is appreciated.

Answer 1

I ran the following and it did work.

$stmt = $pdo->query("
CREATE TEMPORARY TABLE IF NOT EXISTS info AS (
  SELECT warehouse.merchandise_id, SUM(warehouse.prod_quantity) AS qty
  FROM warehouse
  WHERE warehouse.merchandise_id IN (SELECT merchandise.id FROM merchandise)
  GROUP BY warehouse.merchandise_id
);
SELECT LPAD(`id`,8,'0'), prod_title, prod_lcode, prod_price, qty
FROM `merchandise` INNER JOIN info
  ON merchandise.id = info.merchandise_id;
");

// skip to next rowset, because it's a fatal error to fetch from a statement that has no result
$stmt->nextRowset();

do {
    $rowset = $stmt->fetchAll(PDO::FETCH_NUM);
    if ($rowset) {
        print_r($rowset);
    }
} while ($stmt->nextRowset());

Notice I had to fix merchandise_count.merchandise_id to info.merchandise_id in your query, because you have no table reference to merchandise_count.

However, I would recommend you do not bother with multi-query. There's no benefit from concatenating multiple SQL statements in a single call. It's also not supported to use prepared statements when using multi-query, or to define stored routines like procedures, functions, or triggers.

Instead, execute the statements one at a time. Use exec() if the statement has no result set and doesn't need to be prepared statements.

$pdo->exec("
CREATE TEMPORARY TABLE IF NOT EXISTS info AS (
  SELECT warehouse.merchandise_id, SUM(warehouse.prod_quantity) AS qty
  FROM warehouse
  WHERE warehouse.merchandise_id IN (SELECT merchandise.id FROM merchandise)
  GROUP BY warehouse.merchandise_id
)");

$stmt = $pdo->query("
SELECT LPAD(`id`,8,'0'), prod_title, prod_lcode, prod_price, qty
FROM `merchandise` INNER JOIN info
  ON merchandise.id = info.merchandise_id
");

$rowset = $stmt->fetchAll(PDO::FETCH_NUM);
if ($rowset) {
  print_r($rowset);
}

As long as you use the same $pdo connection, you can reference temporary tables in subsequent queries.