mysql_fetch_assoc goes into infinite loop

Question

I am trying to display posts in a specific condition with a while loop but it is giving me a 30 second timeout error. Here's my code:

while ($tagrow = mysql_fetch_assoc(mysql_query("SELECT * FROM posts WHERE tag='$usersearch' ORDER BY id DESC"))) {
         $stufftoecho .= $tagrow['text'];
        }

There is a timeout error on the line with the while loop, I guess it is an infinite loop but I don't know how to fix it.

NOTE: Please don't tell me I need to use mysqli or PDO, i will convert to one of them later.

You are running the query every loop... Why did you put `mysql_query` in there? — FirstOne, Mar 12 '16 at 16:49
`$result = mysql_query(YOUR_QUERY); while($tagrow = mysql_fetch_assoc($result)){...` — FirstOne, Mar 12 '16 at 16:51
@FirstOne oh, thank you! I wasn't aware of that. I think you should post this as an answer. — , Mar 12 '16 at 16:52
I know you said that you are going to convert to mysqli/PDO, but why the trouble? You should put effort in the correct way from the beginning. Anyway, please, take a look at [**How can I prevent SQL-injection in PHP?**](http://stackoverflow.com/q/60174/4577762) — FirstOne, Mar 12 '16 at 16:57
@FirstOne I have lots of php files and all of them have +200 lines of code written for mysql, not others. Now when I change one page to mysqli (or just write it in mysql from beginning) the others don't work. Because my database file is in mysql so all the other code **must** be in mysql — , Mar 12 '16 at 17:01

score 1 · Answer 1 · answered Mar 12 '16 at 17:01

1

Run mysql_query() once out of while loop.

$sql = "SELECT * FROM posts WHERE tag='$usersearch' ORDER BY id DESC";

$result = mysql_query($sql);


while ($row = mysql_fetch_assoc($result)) {
    echo $row['text'];   }

mysql_free_result($result);

answered Mar 12 '16 at 17:01

Nurul Akter Towhid

3,046
2
33
35

Tom · Answer 2 · 2016-03-12T18:14:42.440

0

It seems your running the query every time it goes through the loop. Try this instead,

$result = mysql_query("SELECT * FROM posts WHERE tag='" . $usersearch . "' ORDER BY id DESC"); //execute query

if ($result)
    {
    while ($tagrow = mysql_fetch_assoc($result))
        {
        $stufftoecho.= $tagrow['text'];
        }
    }
  else
    {
    echo mysql_error();
    }

edited Mar 12 '16 at 18:14

answered Mar 12 '16 at 16:49

Tom

1,223
1
16
27

score 0 · Answer 3 · answered Mar 12 '16 at 16:53

I'd suggest you to break it up into chunks.

$query = mysql_query("SELECT * FROM posts WHERE tag='".$usersearch."' ORDER BY id DESC") or die(mysql_error());

while ($tagrow = mysql_fetch_assoc($query)) {
     $stufftoecho .= $tagrow['text'];
}

Hope this helps.

Peace! xD

Matt Clark · Accepted Answer · 2016-03-12T22:09:05.940

This of course is going to be an infinite loop, provided at least one element matches the query; I suspect that in your infinite loop, you never progress past the first element returned by the query.

while ($tagrow = mysql_fetch_assoc(mysql_query("SELECT * FROM posts WHERE tag='$usersearch' ORDER BY id DESC"))) {

The function mysql_fetch_assoc is going to take in a result set of a query, and iterate through it, returning the next element in the series each time it is called.

The function mysql_query is going to take a query and return a result set;

They way you have your while loop structured, you will be executing the query, and passing the result set directly into the fetch_assoc, which will inturn assign the value of $tagrow to the first element; and then do it again, and again.

What we need to do instead, is execute the query, and then save the result set. This makes it so you only need to execute the query once, instead of on each iteration, and save the results in memory.

We can do this by simply breaking it into the following two lines, and using a variable as temporary storage of the query results.

$results = mysql_query( "SELECT * FROM posts WHERE tag='$usersearch' ORDER BY id DESC" );
while ( $tagrow = mysql_fetch_assoc( $results ) ) {

mysql_fetch_assoc goes into infinite loop

4 Answers4