Php mysql, pdo, and query: why don't multiple "queries" work (in this case)?

Question

Here is my sql query:

$sql = 'CREATE TEMPORARY TABLE tmp ' . 'SELECT * FROM '.$table.' ' . 'WHERE id=:id; ' . 'ALTER TABLE tmp drop ID; ' . 'INSERT INTO '.$table.' ' . 'SELECT 0,tmp.* FROM tmp; ' . 'SET @last=LAST_INSERT_ID(); ' . 'DROP TABLE tmp;' . 'SELECT @last; '; $stmt = $this->bd->execQuery($sql, array(':id'=>101)); echo "1 -> = "; var_export($stmt); echo "\n"; $stmt = $stmt->fetch(PDO::FETCH_OBJ); echo "2 -> = "; var_export($stmt); echo "\n"; 

Failure in itself: the request works (I checked).

 sql = 'CREATE TEMPORARY TABLE tmp SELECT * FROM categorie WHERE id=:id; ALTER TABLE tmp drop ID; INSERT INTO categorie SELECT 0,tmp.* FROM tmp; SET @last=LAST_INSERT_ID(); DROP TABLE tmp;SELECT @last; ' params = array ( ':id' => 101, ) 1 -> = PDOStatement::__set_state(array( 'queryString' => 'CREATE TEMPORARY TABLE tmp SELECT * FROM categorie WHERE id=:id; ALTER TABLE tmp drop ID; INSERT INTO categorie SELECT 0,tmp.* FROM tmp; SET @last=LAST_INSERT_ID(); DROP TABLE tmp;SELECT @last; ', )) 2 -> = false 

If I do this “manually” on the console line, it works too (sorry for the looong line of code):

 mysql> CREATE TEMPORARY TABLE tmp SELECT * FROM categorie WHERE id=101; ALTER TABLE tmp drop ID; INSERT INTO categorie SELECT 0,tmp.* FROM tmp; SET @last=LAST_INSERT_ID(); DROP TABLE tmp;SELECT @last; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.07 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +-------+ | @last | +-------+ | 141 | +-------+ 1 row in set (0.00 sec) mysql> 

This is where my code runs.

 public function execQuery($sql, $tab=array()) {  $stmt = self::$_pdo->prepare($sql);  if ($stmt===false) {    throw new Exception(      'Erreur prepare '.$sql.      ' = '.var_export(self::$_pdo->errorInfo(), true)    );  }  foreach ($tab as $key=>$valeur) {    $stmt->bindValue($key, $valeur);  }  if ($stmt->execute()===false) {    throw new Exception(      "Erreur execution de la requete :\n\"".$sql."\"\n".      "Paramètres de la requete :\n\"".var_export($tab, true)."\"\n".      "Details de l'erreur : \n".var_export(self::$_pdo->errorInfo(), true)    );  }  return $stmt; } 

How to do to get the last inserted value in one snapshot (= do what I did)?

Answer 1

As mentioned in my comment above, although it does not answer your question about how you issue multiple SQL commands in a single query from PHP, one way to solve this problem would be to put your SQL in a stored procedure using a prepared statement:

 DELIMITER ;; CREATE PROCEDURE copyRecord(TableName VARCHAR(20), id INT) BEGIN -- prevent SQL injection SET TableName = CONCAT('`', REPLACE(TableName, '`', '``'), '`'); SET @id = id; SET @sql = CONCAT(' CREATE TEMPORARY TABLE tmp SELECT * FROM ', TableName, ' WHERE id = ? '); PREPARE stmt FROM @sql; EXECUTE stmt USING @id; DEALLOCATE PREPARE stmt; ALTER TABLE tmp drop ID; SET @sql = CONCAT(' INSERT INTO ', TableName, ' SELECT 0,tmp.* FROM tmp '); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; DROP TABLE tmp; SET @sql = NULL; SET @id = NULL; SELECT LAST_INSERT_ID(); END;; DELIMITER ; 

From PHP, you just need to invoke the SQL command CALL copyRecord('categorie', 101) .