Mysql while in mysql while - optimizing

Question

I have code:

$somevar=mysql_query("SELECT * FROM feed");
while ($fetched=mysql_fetch_array($somevar)) {

  $nextvar=mysql_query("SELECT FROM comments WHERE feed_id='".$fetched['id']."'");
  while ($anotherfetch=mysql_fetch_array($nextvar)) {
    echo $anotherfetch['comment'];
  }

  echo $fetched['person'].$fetched['text'];
}

Tables structures:

feed
id | person | text

comments
id | feed_id | person | comment

What's the best solution to optimize it and is it possible at all? I know it's a big mistake to make while in while but it looks like there is no other way..

Switching to PDO and prepared statements is a good start! (And no, nested loops are actually perfectly okay... as long as you don't only have one result, in which case you should definitely use a `JOIN` instead.) — Ry-, Nov 20 '12 at 19:36
Can't you use joins? Can you give us the actual queries so we can take a look? — PeteGO, Nov 20 '12 at 19:36
You have not told us anything about the table structures or what you are trying to query to where we could give you a reasonable answer. And as already pointed out - STOP using `mysql_*` functions, as they are being deprecated (note the BIG RED box in php.net documentation). — Mike Brant, Nov 20 '12 at 19:37
You could also use `WHERE something IN (SELECT ...)`, although it may be slow — lukedays, Nov 20 '12 at 19:41
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained and the [deprecation process](http://j.mp/Rj2iVR) has begun on it. See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — tereško, Nov 20 '12 at 19:46
Thanks for the information, somehow I didn't thought about JOIN in this query. And thanks for very helpful advice about PDO and mysqli I will use it for sure. — Mindaugas Jakubauskas, Nov 20 '12 at 19:52

score 0 · Accepted Answer · edited May 23 '17 at 11:48

A while within a while is not so much the issue as is nested queries. This is often an indication of an N + 1 problem.

While there are different solutions, my suggestion in your case would be to use a JOIN and stitch the data together as you need it.

Also, as noted in the comments, please don't use the mysql_* functions as they are in the deprecation process. Use MySQLi or PDO instead and be a better PHP Developer.

score 0 · Answer 2 · answered Nov 20 '12 at 19:45

This is in general a bad habit, to run N queries inside the loop, and the number of times the loop executes depends on the number of rows returned by the outer query. The risk is that the outer query returns 1000 rows, and then your code executes 1000 inner queries, which has a lot of overhead.

There's also the problem that MySQL doesn't allow a client to execute a new query while the outer query is still in progress. That is, the outer query still has results to return. The workaround, which is done transparently by the PHP mysql client, is that internally it has already fetched all the results from the outer query. The calls to mysql_fetch_array() are really just iterating over data it has fetched and is holding in memory.

You should learn how to use JOIN queries to avoid this antipattern.

score 0 · Answer 3 · answered Nov 20 '12 at 20:02

0

Sounds like you want something like this:

SELECT person, 
       comment 
  FROM feed f 
         INNER JOIN comments c 
         ON f.id = c.feed_id

answered Nov 20 '12 at 20:02

PeteGO

5,597
3
39
70

Mysql while in mysql while - optimizing

3 Answers3