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.

score 3 · Accepted Answer · answered Apr 19 '20 at 17:57

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.

Thank you. I believe this answers my question. I like your second solution, but just for the sake of understanding where I went wrong, my query didn't work because I had to use nextRowset()? Because my first statement had no result. Am I right? — stressed out, Apr 19 '20 at 18:05
And pardon me for asking a different question: When does a MySQL session end when it's handled by PHP PDO? I want to know how long my temporary table will remain in memory. I know it's a different question, but I just thought to ask it here because it's sort of relevant, if you won't mind. — stressed out, Apr 19 '20 at 18:08
PHP "cleans up" all resources at the end of a request. That ends the current MySQL session, which drops temp tables, unsets any session variables, and rolls back any transactions not yet committed. — Bill Karwin, Apr 19 '20 at 18:41
And yes, if you try to fetch from a statement that has no result set, and you enable PDO exceptions, you get `PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error in ...` — Bill Karwin, Apr 19 '20 at 18:43

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

1 Answers1