handling result rows from PDO multi query

Question

code derived from: PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)

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

$sql = "
DROP TABLE IF EXISTS car;
CREATE TEMPORARY TABLE car (name (varchar 300), type (varchar 300)); 
INSERT INTO car(name, type) VALUES ('car1', 'coupe'); 
INSERT INTO car(name, type) VALUES ('car2', 'coupe');
SELECT * FROM car;
";

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

for ($i = 0; $i < count($query_results); $i++) {
    echo $query_results[$i]['name']."</br>";
}
echo "resultamount: ".count($query_results);

the result amount for this query returns 0 (and the loop, of course, does not initiate). how do I fix this so that 2 rows are returned?

dupe of http://stackoverflow.com/questions/22726571/multiple-queries-results-pdo-mysql — Your Common Sense, Apr 03 '14 at 09:56
Why so much people are busying themselves with this useless and inconcenient feature? — Your Common Sense, Apr 03 '14 at 09:56
As you might have noticed, the link you provided as a duplicate links to a solution which I marked at the beginning of my question. The problem emerges not with carrying out the multiple queries but with handling their results in this specific case. Why is this an useless and inconvenient feature, though? — i_love_nachos, Apr 03 '14 at 10:13
1. Please reread the answer I linked to. 2. It is useless because one can always run these queries separately. — Your Common Sense, Apr 03 '14 at 10:20

score 2 · Accepted Answer · answered Apr 03 '14 at 10:37

2

Do not add complexity out of nowhere and you always will have convenient and predictable result

$db = new PDO("mysql:host=localhost;dbname=map1", 'root', 'root');
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$sql = "DROP TABLE IF EXISTS car;
CREATE TEMPORARY TABLE car (name (varchar 300), type (varchar 300)); 
INSERT INTO car(name, type) VALUES ('car1', 'coupe'); 
INSERT INTO car(name, type) VALUES ('car2', 'coupe');";
$db->exec($sql);

$sql = "SELECT * FROM car";
$stmt = $db->prepare($sql);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "resultamount: ".count($results);

answered Apr 03 '14 at 10:37

Your Common Sense

156,878
40
214
345

Thanks for the response. The execution of the second query generates an internal server error (but doesn't do so, if selecting from a non-temporary table). The auto-deletion of temp tables is why i thought i have to take the hard road in the first place... How do I overcome this issue? – i_love_nachos Apr 03 '14 at 15:18
this just can't be. No tables going to be deleted here. To overcome an issue one have to know what particular issue it is. You have to peek into server log for the error description. – Your Common Sense Apr 03 '14 at 15:44
PHP log: Base table not found. http://justpaste.it/eyp0 Seems to point out the table was deleted in between the 2 queries. Ideas? – i_love_nachos Apr 03 '14 at 16:38
@Minion it is only possible if you're closing connection between these queries. – Your Common Sense Apr 03 '14 at 16:48
argh. you just have table definition wrong. the table's just never created. Why should I test your SQL in my console? Why can't you do it your self? – Your Common Sense Apr 04 '14 at 06:24
ALSO, this explains why running many queries at once is inconvenient. Having them run separately, you'd had an error reported to you and eventually fixed long time ago – Your Common Sense Apr 04 '14 at 06:25
Oh geez. Everything works now. Thanks for the patience and thanks for the help! You have successfully shattered my illusions of knowing anything about working with SQL. – i_love_nachos Apr 04 '14 at 06:36

handling result rows from PDO multi query

1 Answers1