show results one by one

[manalnor]

Hello dear friends

 

Let say i've a huge database of 100,000 entries

my_table (id,name)

 

and i'd like to call all the entires to be shown by this code

 

$conn = mysql_connect('localhost','USER','PASS') or die(mysql_error());
mysql_select_db('my_table',$conn);

$sql = "SELECT * from my_table";
$result = mysql_query($sql,$conn);

while ($row = mysql_fetch_array($result)){

$name = $row['name'];
$id = $row['id'];

echo $name;
echo "<br>(done)<br>";

}

 

 

The problem

 

The problem that makes me almost gonna cry,it takes long time to load then it will showing names (remember my_table of 100,000 entries) all at once  :'( and sometimes it sudden shows huage amount once or even sometime it hang up.

 

My question

 

Is there any way that i can call it so that it showing me results one name by one name like streaming name1 (done),name2 (done),name3 (done),name4 (done),name5 (done),......etc not showing all at once

 

i'm afraid it may needs some ajax idea or somehow i really don't know but this is important to me so your help is very useful to me.

 

so anyone please helps me cause it would really save my hosting ram otherwise they would suspend me 

[litebearer]

consider using pagination -  example of a very nice class I use http://www.nstoia.com/sat/disp_pag/

[manalnor]

@litebearer

 

Thanks for the idea ,  but i don't want to use pagination  :-\

[PaulRyan]

I really have no idea why you would want to display 100,000 records at once.

 

Explain your reasoning behind this, and we may be able to find a solution for you.

[manalnor]

I really have no idea why you would want to display 100,000 records at once.

Explain your reasoning behind this, and we may be able to find a solution for you.

 

Okay,

 

What i've mention in my post is just an example for simplicity to know it as study case  wile the reason i don't want to use pagination and why i'm willing to show 10K results !

 

I've database with tons of quotes (ex: die well said by FFF)

and i want to lunch website where visitor will see an input form and put own name

then press submit

 

it should gives him all that tons of quotes after it operate name replace of all the quotes.

 

i want it appears (gives results) to the visitor one by one (row by row)  just like ajax steps effects baam ---- baam ---- baam ..etc 

 

 

[Psycho]

Well, pagination is really what you "should" be using. A person cannot realistically work with 100,000 records on a single page. They would have to scroll up and down to see/work with the records anyway. Pagination gives you (and the user) a lot more flexibility than showing the records on one page (sorting, filtering, etc.). For example, if the user wants to sort the records, doing that with pagination is simple and efficient. Trying to re-query all 100,000 records to resort them is a complete waste of resources.

 

But, since you asked the question, I will provide an answer. You can dynamically load records by using AJAX. But, you would NOT want to only load one record at a time. Running database queries is an expensive operation and running 100,000 queries will bring your server to a crawl. Instead you should target larger subsets of data that can be grabbed efficiently (I'd start with 100, maybe even 500). You can run tests to find the optimal count.

 

So, when the page loads you would have some JavaScript code that will make a call to a PHP script that will query 100 records, process it, and return the formatted data back to the JavaScript which will append the content into the display. Then the JavaScript will make the same call again to get the next 100 records. The process will continue until the PHP script returns to the JavaScript that there are no more records.

 

But, there are some problems with this approach. The one that comes to mind is that if records are added/removed during this process it could cause records to not get added to the display or be duplicated. For example, if you get the first 100 records, then someone adds a record that would have been in position 50, the next call to get records 101-200 will have the record that was at position 100 will now be at position 101 and would be duplicated.

[PaulRyan]

Now we know what you're wanting to do, it would seriously be better to use pagination for this.

 

It lightens the load on the server, just requesting say 50 results at a time, than loading all 100,000 at once.

 

You should think logically about this, instead of haphazardly diving into it and implementing the easiest solution (Showing all at once)

 

This is just my opinion, and I wouldn't feel right giving you a solution I feel is not right.

 

Regards, PaulRyan.

[Psycho]

it should gives him all that tons of quotes after it operate name replace of all the quotes.

 

If you are doing a process on 10K - 100K records you need to make sure these processes are extremely efficient. I really think you need to reassess what you are doing. Having all the records on one page is just plain dumb in my opinion. No one is going to read through 10,000 or 100,000 results. You are simply wasting resources by trying to show them all on one page. If you were to have multiple people trying to call that page simultaneously you are going to run into performance problems.

[manalnor]

@Psycho

 

Thanks for this explain and i'll try it since my db will be locked no more add or delete of the rows

 

@PaulRyan

 

Okay thanks for help

 

Now i will try 3 ways

 

1) Using mysql_unbuffered_query (it was said The PHP script can parse the results immediately, giving immediate feedback to users.)

 

2) Ajax call

 

3) Pagination

 

Hope one will works   ~Thanks all for help