Display mysql records from database 1 by 1 – PHP

  php

Q(Question):

In my PHP script, I have a basic mysql call :

$sql = "SELECT * FROM ...";
$req = mysql_query($sql) or die();
while($data0 = mysql_fetch_assoc($req)) {
//some update stuff here
echo "Important information about update...";
}

I have +7000 records in my database (and growing), so i’d like to show the update process result 1 by 1 and not showing them when script is finished.

Could you please tell me how to do this?

Thank you!

A(Answer):

Do you mean 1 record per page with a next record button/link? You’ll want to look into pagination.

A(Answer):

Thank you for your answer Rabbit!

I’m searching a way to fire my ECHO after each record, a live update. So i don’t want to have to wait the end of the script (can take several hours).

A(Answer):

You can’t do that with PHP alone. You will have to use AJAX and have it retrieve one record at a time. Most people just use pagination in this scenario.

A(Answer):

Pagination need a human action. With 7000 records and ~20 seconds per record, it would need so much time!

Do you have a link/code snippet to share for ajax use, please?

A(Answer):

Here is a link to an AJAX tutorial.

Pagination puts much less of a strain on the server by showing a subset of the records at a time. Whereas what you’re attempting to do will bombard the server with constant requests over many hours. Having just a few people on at once could bring the server to a crawl.

You said 7000 records? That should not take anywhere near a few hours to return data. That shouldn’t even take a few seconds to return. I suspect that your query is unoptimized and you would be better served by fixing your query instead of working around the inordinate amount of time it takes.

We have queries with millions of rows that return quicker than that.

A(Answer):

Looks to me you already have it streaming out. Here is another method, where you fetch all into an array and then stream out the array, if your computer has enough memory. Don’t know how much data you have in each record.


$cr = mysqli_fetch_all ( $result , $resulttype = MYSQLI_ASSOC );
$cCnt = $result->num_rows;
for ($n=0; $n < $cCnt; $n++)
{
$c = $cr[$n];
//do your processing and echoing here.
}

LEAVE A COMMENT