PDO Create Temp Table and Select

Question

I am trying to create a temporary table from the results of multiple tables that are alike (same table structure). After creating the temporary table and runing the subsequent queries, I would like to store the results from the temporary table in an array to be accessible later in the script/program. I have tried searching for an answer and can't seem to find one.

I have tried nextRowset() as well as separating the queries, but nothing seems to be working like I expect it to work.

Here is my code:

$pdo = new PDO("mysql:host=".$_SESSION['server'].";dbname=data".$_SESSION['sysident'],$user,$pass);
$stmt = $pdo->prepare("DROP TABLE IF EXISTS $tabletocreate;
                CREATE TEMPORARY TABLE $tabletocreate LIKE table1;
                INSERT INTO $tabletocreate (SELECT * FROM table1 WHERE (MISC LIKE '%:memno%' OR MEMNO = :memno)) UNION (SELECT * FROM table2 WHERE (MISC LIKE '%:memno%' OR MEMNO = :memno)) UNION (SELECT * FROM table3 WHERE (MISC  LIKE '%:memno%' OR MEMNO = :memno)) ORDER BY SLIPNO;
                SELECT * FROM $tabletocreate");
$stmt->bindParam(":memno",$_SESSION['memno']);
$stmt->execute();

$stmt->nextRowset();

$test = $stmt->fetchAll();
print_r($test);

I am unsure as to why the results are not being stored into the array. From what I can tell, everything seems right and no errors occur when the script is ran. I appreciate any help that anyone can offer.

UPDATE - I found out why the query wasn't working. I was using a "-" in the table name I was trying to create which isn't allowed.

score 4 · Answer 1 · answered Aug 29 '13 at 16:46

4

You cannot run multiple queries in a single ->query() call. This is a security measure in the underlying PHP mysql drivers, to prevent some form of SQL injection attacks. Doesn't matter which DB interface library you're using, because they all use the same underlying drivers. You'll have to run each seperate query separately:

->query("DROP TABLE IF EXISTS ...");
->query("CREATE TEMPORARY TABLE ...");
->query("INSERT INTO ...");
etc...

answered Aug 29 '13 at 16:46

Marc B

356,200
43
426
500

I updated the code to have separate queries but it still doesn't seem to want to push the results into the array after I try to select from the temporary table. Any ideas? – kccoers Aug 29 '13 at 16:55

score 1 · Accepted Answer · answered Aug 30 '13 at 14:34

1

I was trying to create a table name with a "-" in the table name. After removing this from the table name, all the queries executed successfully and my PHP code worked as intended.

answered Aug 30 '13 at 14:34

kccoers

129
2
11

PDO Create Temp Table and Select

2 Answers2