Problem echoing huge query result

Pharismod

Hello,

I am having a problem putting the results of a large database query to the browser.

I query the database for the fields 'client_id' (primary key) and 'company_name' WHERE category='some_character'

Each record it the database has a 'category' letter assigned to them (a, b, c, whatever).

I echo them to the browser like so.

foreach ($list_array as $row)
{
$url = "modify_client.php?client_id=".($row["client_id"]);
$title = $row["company_name"];
echo "<tr><td class=\"lists\" width=\"50%\">";
do_html_url($url, $title);
echo "</td>";
echo "<td width=\"50%\">";
echo $row["city"];
echo "</td></tr>\n";
}

So as you can see... it prints out a giant list of everything in 2 columns straight down the screen. Which gets very long.

Some of those categories are fairly small. Category c has 4 records, a has 260 or so, but b has 694.

When I run my code with category=a or c it works fine, however when I use b it will crap out most of the time and say 'page can not be displayed'.

Is this a browser memory issue that I can't fix, or is it something else (Apache, PHP, MySQL)?

Thanks,

Pharismod

edwardsbc

Well I have some pages which return over 8000 rows in a table. I doubt it is Apache/PHP that is the problem, although it could be. The most likely culprit is your browser is running out of memory. Some things to check on the servers side:

1) Script time out - is this occuring before all of the output is processed (PHP.INI)

2) Script memory limit - is the script running out of memory (again, controlled in PHP.INI)

Pharismod

Hrmm... the browser running out of memory was my initial thought, but as you pointed out, pages should be able to print several thousand rows without the browser running out of memory...

Here are the entries from php.ini

max_execution_time = 30 ; Maximum execution time of each script, in seconds
memory_limit = 8M ; Maximum amount of memory a script may consume (8M😎

Seems more than adequate...

I also tested my problem on multiple PCs. One PC just says page is not displayed, OR it will show a portion of the listings but stop partially through (after 200-300 records out of 600).

The other PC usually shows me the results for about a 1/4 second before giving me the page could not be displayed error.

The PC mentioned in the second instance above is a 1.6GHz with 256MB DDR RAM. Seems more than adequate. Any other ideas?
It is a huge preference to display all of these records on one page instead of breaking it up over multiple pages...

Thanks,

Pharismod

edwardsbc

Well, I'm not a mySQL guru (I mostly use MS SQL7), but there are some mySQL server varialbles which might be limiting what is returned. However, the manual says these should generate an error message if the limits are hit.

So I would now begin to look at the actual data and make sure it is not inserting some bad HTML. Most browsers won't render a table until the final <\table> tag is encountered, so if something is going wrong dislpaying $row['city'] or one of the other variables it might crash the browser. You may want to try running the variables through htmlspecialchars() or htmlentities() and see what happens.

Pharismod

No that didn't work either.

I have done some more testing... taking the data out of tables and stuff. It seems to work about 75% of the time, but the other 25% it seems to get stuck in an infinite loop with the page refreshing itself until it crashes.

I just see the list/refresh/list/refresh a bunch of times until it dies. As I said it's not consistent. What the heck is going on...

Very strange.

Pharismod

edwardsbc

"Refreshing itself"??? How the heck is that happening? Your code doesn't have any header() calls does it? Meta refresh tags?

Another thought - is there a proxy between your server and browser?

Pharismod

No header() or meta stuff. I stripped out everything but the query and gave a straight printout of the results and while the problem is less frequent, it still happens.

The server is running on the same PC I code in, so no proxy. I've also tried connecting with other computers and I get similar problems. Though the other computers are usually slower and the page just partially loads or crashes, no refreshing...