PDO : sending multiple statements at once without using exec()

Question

My select statement looks somewhat like this :

CREATE TEMPORARY TABLE t1 AS(
    SELECT id, COUNT(*) AS count 
    FROM some_other_table 
    GROUP BY id);

ALTER TABLE t1 ADD UNIQUE INDEX (id);
SELECT * FROM t2 INNER JOIN t1 ON t1.id = t2.id

I'm using the following PHP code :

$pdo->query($sql)->fetchAll();

But I get an error since PDO does not allow multiple statements' execution in one query.

From what I've read so far, I should use exec(). But then exec() does not return results for select statement. I do not need the parametrization for this specific query so any unsafe method will work too, since the query itself is perfectly safe from any outside alteration.

Right now what I'm doing is executing the sql code as 3 different statements. but I believe that's slower than executing it in one go and would like to find a better method to do this.

see: http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd — MiDo, Jun 24 '13 at 12:50
You first of all might learn about the concept of a transactions and if your database server offers it. Also stop guessing around if that is slow or not. As long as it's fast enough for you, don't care because there is no issue. — hakre, Aug 17 '13 at 15:19

score 1 · Answer 1 · answered Aug 17 '13 at 17:02

Like many other inexperienced developers, you are looking wrong way trying to optimize your code.

While it doesn't really matter how many calls did you to database, the very idea of creating temporary table IS what makes your code inefficient. And you have to get rid of this really slow part instead of trying to optimize it for 0.00001%

PDO : sending multiple statements at once without using exec()

1 Answers1