I'm getting about 500 rows back from a DB that my PHP script connects to and executes a query on. For each row returned, I display it in table format. Script works fine in Firefox, and ultimately works in IE, but users get a 'Stop Running This Script' prompt 1 or 2 times (and telling IE to continue..) before everything displays properly.
To get around this, I need to send some data back to the browser -- and I think the best way to do that is processing the array in chunks/batches. PHP has an array_chunk function, but i'm having trouble figuring out how to use it here.
To get the DB rows, I use:
$result = mssql_query($query);
... then to display them in a table:
while($row = mssql_fetch_array($result))
{
[DISPLAY TABLE ROW IN TABLE]
}
What is the best way to use array_chunk to process my array, say in chunks of 50?
array_chunk is only going to help you after you've already gotten the entire array of information from the database. From your question, it sounds like the script is hanging on the actual query from the database as it's being set to an array and then you're wanting to process that entire array.
You could output each row of the table while still in the loop you're using to get each row of results from the database, instead of creating a master array first. However, your users might see the page load in chunks as your script chugs through the query.
Off the topic of your question now: If the page loading in chunks is less than ideal for you, you could look into asynchronously running the query in the background (using JavaScript) while showing some sort of "Loading" message until the script is done running. Then, output the results to the page.
Alternatively, if you have the option of not showing all 500+ results at once, you could use pagination and use the LIMIT and OFFSET SQL keywords to only grab portions of your overall query. Then, create links to next or previous pages of results which would change the OFFSET.
You're asking the wrong question, I think... array_chunk is not your best option here, since you need the full array to chunk it. Ergo: you' d already have looped through the entire resultset once.
Why not output directly within the while loop you've got there?
If you feel you must chunk the data, use an external counter. Increment within the loop, and check against that value using the MODULO operand.
$i = 0;
while($row = mssql_fetch_array($result))
{
if(++$i % 500 == 0) {
[DISPLAY TABLE ROW IN TABLE]
}
}
You're sending a bunch of data to the browser at once. To make this perform well, either:
Send less data. As other answers suggest, use SQL LIMIT to paginate the results, and output links to navigate back and forth through the pages (/data/page/1, /data/page/55, /data/page/last, etc.). This has a secondary effect: your PHP and SQL will execute more quickly as you're processing less data on the server per request.
Send the data in smaller blocks. While looping through the rows, call flush(); periodically (every $n rows) to give the browser time to display some results before it fetches more.
Pull the data incrementally. You could also fetch the results via AJAX in smaller bits (say 50 rows at a time). You can sit this on top of the pagination described above or similar.
Make the data smaller. Minimizing the size of the HTML you return to the browser can make a significant difference as well. You may be able to remove extra <div> tags, classes, IDs, or abbreviate the content itself (linking back to the full detail). You could also send the data in a briefer format (like JSON), and use JavaScript to expand it.