Syntax error or access violation: 1064 - PDO refuses to execute multiple queries (including CREATE TEMPORARY TABLE)

Question

I'm trying to execute the following query using PDO in PHP. The query works fine and without any mentioned issues when applying it in PhpMyAdmin. Nevertheless, it shows the following error when executing it in PHP:

 Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TEMPORARY TABLE IF NOT EXISTS temp2 AS SELECT COUNT(*) AS total_visits, s' at line 1

The code is simple, I'm not sure if the table structure is required, but will provide it at the end of this question

    $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS SELECT COUNT(*) AS total_views, series_id FROM my_app_stats d1 WHERE 1 = 1 AND d1.stats_type = 0 AND d1.created_at_text = DATE_FORMAT(NOW(), '%Y-%m-%d') GROUP BY series_id; CREATE TEMPORARY TABLE IF NOT EXISTS temp2 AS SELECT COUNT(*) AS total_visits, series_id FROM my_app_stats d2 WHERE 1 = 1 AND d2.stats_type = 1 AND d2.created_at_text = DATE_FORMAT(NOW(), '%Y-%m-%d') GROUP BY series_id; ALTER TABLE `temp1` ADD INDEX `temp1_idx_id` (`series_id`); ALTER TABLE `temp2` ADD INDEX `temp2_idx_id` (`series_id`); SELECT database_series.id, (SELECT dusers.user_name FROM my_app_users dusers WHERE dusers.id = database_series.series_owner) user_name, database_series.series_title, temp1.total_views, temp2.total_visits FROM my_app_series database_series LEFT JOIN temp1 ON temp1.series_id = database_series.id LEFT JOIN temp2 ON temp2.series_id = database_series.id ORDER BY total_views DESC, total_visits DESC LIMIT 10";
    $query = $this->conn->prepare($sql);
    $query->execute();
    $data = $query->fetchAll(PDO::FETCH_ASSOC);
    $query->closeCursor();
    return $data;

my_app_users

id | user_name

my_app_stats

id | series_id | created_at (timestamp) | created_at_text (text, formatted as y-m-d)

my_app_series

id | series_owner

Unless you have `PDO::ATTR_EMULATE_PREPARES` set to 1 you can't prepare multiple statements at once. — Nick, May 08 '19 at 00:43
Using this line in the connection caused an error in other queries, which makes no sense? — Jaeger, May 08 '19 at 01:29
It makes a perfect sense. see https://phpdelusions.net/pdo#emulation — Your Common Sense, May 08 '19 at 08:29

Bill Karwin · Accepted Answer · 2019-05-08T01:36:13.867

You can't use prepare() with a series of multiple statements. MySQL doesn't support it.

This is almost a duplicate of PHP MySQLi multi_query prepared statement but that's for mysqli. It doesn't matter, it's a limitation of MySQL, not of either PHP interface.

There's no reason to prepare multiple SQL statements in one call anyway.

You should execute them one statement at a time.

Regarding your comment: Yes, they are all related, and you can rely on the temporary tables remaining available as long as you query them using the same connection you have in $this->conn. The temp tables won't go away unless you close the connection (or deliberately DROP the temp tables of course).

Example:

$sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS 
  SELECT COUNT(*) AS total_views, series_id FROM my_app_stats d1 
  WHERE 1 = 1 AND d1.stats_type = 0 
    AND d1.created_at_text = DATE_FORMAT(NOW(), '%Y-%m-%d') 
  GROUP BY series_id";
$this->conn->exec($sql);

$sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp2 AS 
  SELECT COUNT(*) AS total_visits, series_id FROM my_app_stats d2 
  WHERE 1 = 1 AND d2.stats_type = 1 
    AND d2.created_at_text = DATE_FORMAT(NOW(), '%Y-%m-%d') 
  GROUP BY series_id";
$this->conn->exec($sql);

$sql = "ALTER TABLE `temp1` ADD INDEX `temp1_idx_id` (`series_id`)";
$this->conn->exec($sql);

$sql = "ALTER TABLE `temp2` ADD INDEX `temp2_idx_id` (`series_id`)";
$this->conn->exec($sql);

$sql = "SELECT 
    database_series.id,
    (SELECT dusers.user_name FROM my_app_users dusers 
     WHERE dusers.id = database_series.series_owner) AS user_name, 
    database_series.series_title, 
    temp1.total_views, 
    temp2.total_visits 
  FROM my_app_series database_series 
  LEFT JOIN temp1 ON temp1.series_id = database_series.id 
  LEFT JOIN temp2 ON temp2.series_id = database_series.id 
  ORDER BY total_views DESC, total_visits DESC LIMIT 10";
$query = $this->conn->prepare($sql);
$query->execute();
$data = $query->fetchAll(PDO::FETCH_ASSOC);
return $data;

How would I be able to combine these statements in PDO? They're all related — Jaeger, May 08 '19 at 01:27

Syntax error or access violation: 1064 - PDO refuses to execute multiple queries (including CREATE TEMPORARY TABLE)

1 Answers1