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, Nov 02 '12 at 20:09

score 4 · Answer 1 · edited May 23 '17 at 12:06

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 · Answer 2 · 2012-11-02T20:13:41.260

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 02 '12 at 20:13

answered Nov 02 '12 at 20:05

Teena Thomas

5,139
1
13
17

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? – NaughtySquid Nov 02 '12 at 20:09
@Liam: Youre asking the worng question really... post your schema and what you want to accomplish. – prodigitalson Nov 02 '12 at 20:11
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. – NaughtySquid Nov 02 '12 at 20:12
@LiamDawe: you need to use `JOIN` for that, or combine the 2 queries using `IN`. – Teena Thomas Nov 02 '12 at 20:12
@Liam: no it is the wrong question because really there should be no need to do that. – prodigitalson Nov 02 '12 at 20:13
I have thought about some sort of join but i have no idea on the correct syntax to count totals from another table based on the main table. – NaughtySquid Nov 02 '12 at 20:14
I need to ask a new question for this i think then cheers guys. – NaughtySquid Nov 02 '12 at 20:15
@Liam: Thats why i said to post your schema and what you want to accomplish. Doing it the way youre attempting is most likely a bad implementation even if you could get it to work. – prodigitalson Nov 02 '12 at 20:15
What if the 2nd query, the one inside you want to do is an INSERT? Let's say you need to do multiple INSERTs, one for each iteration of the WHILE loop? That would preclude a JOIN. – TARKUS May 29 '13 at 17:00

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

2 Answers2

Linked