How to do a second PDO mysql query in a while loop from another query?

Question

So a snag I am hitting in using PDO MySQL is that if i run a query like so:

$db->pquery("SELECT `category_id`, `category_name` FROM `database_categorys` ORDER BY `category_name` ASC");
while ($category = $db->fetch())
{
}

Inside the while loop i cannot do another query or it will cancel out the previous query, is there a way to get around that?

This is my pquery btw:

// A plain query
public function pquery($sql)
{
    $this->STH = $this->database->prepare($sql);  

    $this->counter++;

    return $this->STH->execute();
}

And my fetch function:

public function fetch()
{
    $this->STH->setFetchMode(PDO::FETCH_ASSOC); 
    return $this->STH->fetch();
}

Usually if you find yourself trying to issue a query while looping over a result set from another query you should be using a JOIN instead. — prodigitalson
– prodigitalson, Commented Nov 2, 2012 at 20:09

Community · Accepted Answer · 2023-11-17 20:42:18Z

This isn't a PDO limitation, it's a limitation of the MySQL client library. MySQL supports only one query in progress at a time. You can't execute another query while the first query still has an open cursor (i.e. it still has results to return).

You have these options:

Use PDOStatement::fetchAll() and collect the whole result set of the outer query in a PHP array. This finishes the query result of the outer query. Then you can loop over the array and run an additional SQL query for each loop iteration.

But running a new query for every loop iteration of the outer result set is not efficient. It's a good way to kill the performance of your application.

Some people call this the N+1 Selects Problem because you run the first select, which returns N rows, and then you run N selects based on the results of the first select.

If you use MySQL, use PDO::MYSQL_ATTR_USE_BUFFERED_QUERY which basically does the same thing, downloads all the rows, saved in an array internally. Then subsequent calls to fetch() just iterate over the buffered results.

But this also involves the N+1 Selects antipattern.

It's better to write a single SQL query that gets you the values you want. Guessing from your comments, you want categories and the count of related rows from another table where category_id matches. Here's an example of such an SQL query:

  $db->pquery("SELECT c.`category_id`, c.`category_name`, COUNT(*) AS `count`
  FROM `database_categorys` AS c 
  LEFT OUTER JOIN `other_table` AS t ON t.category_id = c.category_id
  GROUP BY c.category_id
  ORDER BY c.`category_name` ASC");

Joins are a fundamental part of SQL. If you try to use SQL without learning to use joins, this is like using PHP without learning to use while loops.

Start here: A Visual Explanation of SQL Joins.

Teena Thomas · Accepted Answer · 2012-11-02 20:13:41Z

0

Try to do the 2nd query after the first query's while loop or fetching of records is complete.

Edit:

You need to use JOIN for that, or combine the 2 queries using IN. Here's the manual

edited Nov 2, 2012 at 20:13

answered Nov 2, 2012 at 20:05

Teena Thomas

5,2091 gold badge15 silver badges17 bronze badges

9 Comments

NaughtySquid Over a year ago

The point though is that i want to count items in a database where the "category_id" matches the current category_id in the while loop, so i need them together?

prodigitalson Over a year ago

@Liam: Youre asking the worng question really... post your schema and what you want to accomplish.

NaughtySquid Over a year ago

No i'm not asking the wrong question, my question is - is there a way to do another pdo mysql query inside a while loop already doing one...to achieve what i want.

Teena Thomas Over a year ago

@LiamDawe: you need to use JOIN for that, or combine the 2 queries using IN.

prodigitalson Over a year ago

@Liam: no it is the wrong question because really there should be no need to do that.

|

Collectives™ on Stack Overflow

How to do a second PDO mysql query in a while loop from another query?

2 Answers 2

Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related