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

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.

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
– hakre, Commented Aug 17, 2013 at 15:19

Your Common Sense · Accepted Answer · 2013-08-17 17:02:39Z

1

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%

answered Aug 17, 2013 at 17:02

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related