CODEWITHSUNDEEP
phpmysql
user1034774
user1034774

Reputation:

MYSQL Showing results as it loads

I have a database with 12000 products. I have a query that would display around 3000 products on a page, and it takes a while to display. The data however gets displayed after the entire query or the loop finished. Is there anyway to display each result as they get pulled? The following is my code..

$result = mysql_query("SELECT title, model, description FROM products WHERE price > 500");
while($row = mysql_fetch_array($result)):
...
endwhile;

Any help is appreciated. Thanks

Upvotes: 0

Views: 94

Answers (3)

DonCallisto
DonCallisto

Reputation: 29912

It's called lazy loading from db.

Take a look a this article or to this tutorial

If I have to make a synthesis, basically you LIMIT your query to a certain quantity of record that have to be pulled out of db. Once some condition is reached (pagination change, scroll down, and so on) you fire an ajax call that will retrieve other records and so on.

Upvotes: 2

VolkerK
VolkerK

Reputation: 96159

The mysql extension has been deprecated, better switch to mysqli or pdo. But since it's the same issue regardless of the api/module let's stick with mysql_* for now....
When mysql_query(...) is called the function returns only after the complete result set has been transferred from the MySQL server into the php process' memory.
Use mysql_unbuffered_query(...) instead:

<?php
$result = mysql_unbuffered_query("SELECT title, model, description FROM products WHERE price > 500");
while($row = mysql_fetch_array($result)):
...
  // output
  // maybe flush() or ob_flush() here...
endwhile;

There difference is explained at

You also have to keep potential output buffers in mind: http://docs.php.net/flush and http://docs.php.net/ob_flush

And for the html client/browser you have to put the partial data into a structure that can be rendered before all of the data has been sent. So, e.g. an unfixed table is most likely suboptimal. see e.g. https://en.wikipedia.org/wiki/Incremental_rendering

Upvotes: 1

Wintermute
Wintermute

Reputation: 1531

There are many variables to achieve such an effect.

First of all, php settings should allow you to use output buffering (take a look at output_buffering parameter and, maybe, implicit_flush). After that you should probably use flush() and/or ob_flush()

Another thing you should consider, is that browsers themselves are not always allowing you to use output buffering, it is known "issue".

For query itself, you probably wanna try using mysql_unbuffered_query (or better MySQLi/PDO equivalent).

To sum it up, the best way to achieve such effect, in my opinion, is using ajax data polling.

Upvotes: 0

Related Questions