How can I SELECT rows from a temporary table using PDO or MySqli?

Question

The following query works in phpMyAdmin but NOT from the php script neither when I use mysql or pdo. Normal select queries do work from the same php script.

CREATE TEMPORARY TABLE tmptable1 (INDEX myindex (sid)) SELECT * FROM `table1` WHERE uid = 55 ORDER BY cc; 
SELECT * FROM tmptable1 GROUP BY sid;

I have PHP Version 5.5.0 and mysqlnd 5.0.11-dev on a LAMP server.

I read on stackoverflow that you can't use pdo for multiple queries. However in this question two methods were described which both didn't work for me.

I tried using this setting, but it doesn't work:

$db = new PDO("mysql:host=localhost;dbname=test", 'root', '');

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

$sql = "
DELETE FROM car; 
INSERT INTO car(name, type) VALUES ('car1', 'coupe'); 
INSERT INTO car(name, type) VALUES ('car2', 'coupe');
";

try {
    $db->exec($sql);
}
catch (PDOException $e)
{
    echo $e->getMessage();
    die();
}

I tried another approach suggested by another answer on stackoverflow, but it said that the function is unknown:

$db = new PDO("mysql:host=localhost;dbname=test", 'root', '');

// works not with the following set to 0. You can comment this line as 1 is default
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);

$sql = "
DELETE FROM car; 
INSERT INTO car(name, type) VALUES ('car1', 'coupe'); 
INSERT INTO car(name, type) VALUES ('car2', 'coupe');
";

try {
    $stmt = $db->prepare($sql);
    $stmt->execute();
}
catch (PDOException $e)
{
    echo $e->getMessage();
    die();
}

How can I get this query to fetch results from within my php script?

Hanky Panky · Accepted Answer · 2016-01-22T09:51:24.713

4

Execute both queries one by one.

$db->query($query1);  // Create temporary table
$db->query($query2);  // Fetch from it

There is no reason why that should not work.

edited Jan 22 '16 at 09:51

answered Jan 22 '16 at 09:37

Hanky Panky

46,730
8
72
95

You totally made my day, it works just fine, thanks!! – BastiaanWW Jan 22 '16 at 09:49

score 1 · Answer 2 · answered Jan 22 '16 at 09:37

Temporary tables are available only in the same connection. Once the connection is destroyed/closed, so are the temporary tables created during this connection.

In your script I don't see the creation of tmp table, does it means you create it in another script and therefore connection ?

And yes, I would recommend you to do three separate queries. Even if you find a way to do all three in one. It is very unlikely that this cause a performance issue, but you'll gain code clarity for sure.

The most upper query is the one I have the problem with, the the queries in the lower two snippets are just to try. — BastiaanWW, Jan 22 '16 at 09:49

How can I SELECT rows from a temporary table using PDO or MySqli?

2 Answers2