Looping through a large mysql result

karlovac

I have a fairly large database query, that returns 5438 results. I have a function in my code that returns everything from that query as an associative array:

    while ( $row = mysql_fetch_assoc($result) ) {
        $subscribers[] = $row;
    }

The problem is that the browser times out by the time it gets to number 5405. I've confirmed this by writing out to a file.

The browser then does a bunch of retries. This happens in both Firefox and IE. I can't seem to change the browser setting on how long to wait before a retry.

What I want to konw is if there's a faster way of doing the while loop that prevents it from timing out.

Anyone experienced this before?

Thanks in advance,

Antun

devinemke

is sounds like PHP and/or mySQL is timing out. this can be set in php.ini with the max_execution_time and mysql.connect_timeout directives. strange that it would timeout though, 5438 is not that many records, unless you have massive amounts of data in each row.

karlovac

Thanks for your reply.

It can't be PHP timeout, because we're only talking about a matter of a couple of seconds.

The mysql timeout is set to -1 (unlimited), and I've confirmed that the SQL query does complete.

A colleague suggested that perhaps there's some funny behavior in the browser, where if the HTTP socket connection happens, but no headers are received within a second or two, the browser triggers the retry (which restarts the whole loop all over).

-Antun

LordShryku

I'd say that's a very wild guess. Try it by sending headers to the browser before querying...

<html>
<head>
</head>
<body>
<?php
while ( $row = mysql_fetch_assoc($result) ) {
        $subscribers[] = $row;
}
......

karlovac

I tried calling flush() before the while loop, and echoing some text during it. I tried calling header() with some header before the loop too, but no headers are returned by the time the browser does the retry.

5,000 records is about the breaking point. I tried limiting the SQL query to a maximum of 5000 records, and it worked fine.

-Antun