Page 1 of 1

Breaking up a result set into batches

Posted: Fri Nov 14, 2008 9:15 am
by updev
Hi everyone,

I work with php and MySQL.
Im having a little challenge and need help.

After reading from the database, i want to display the results in an html table. so far, this is what ive done

Code: Select all

 
while($newArray = mysql_fetch_array($result3)){
    $datee = $newArray[DATE] == null ? "-" : $newArray[DATE];
    $pcfname = $newArray[PCFName] == null ? "-" : $newArray[PCFName];
    $cellname = $newArray[CellName] == null ? "-" : $newArray[CellName];
    $memberstrenght = $newArray[MemberStrenght] == null ? "-" : $newArray[MemberStrenght];
    $totalattendance = $newArray[TotalAttendance] == null ? "-" : $newArray[TotalAttendance];
    $nofirsttimers = $newArray[NoOfFirstTimers] == null ? "-" : $newArray[NoOfFirstTimers];
    $newconverts = $newArray[NoOfNewConverts] == null ? "-" : $newArray[NoOfNewConverts];
    $filled = $newArray[NoFilledWithHolySpirit] == null ? "-" : $newArray[NoFilledWithHolySpirit];
    $offering = $newArray[TotalCellOffering] == null ? "-" : $newArray[TotalCellOffering];
    $pioneers = $newArray[NoOfCellPioneers] == null ? "-" : $newArray[NoOfCellPioneers];
    print "<tr><td>$datee</td><td width='100'>$pcfname</td><td width='100'>$cellname</td><td>$memberstrenght</td><td>$totalattendance</td><td>$nofirsttimers</td><td>$newconverts</td><td>$filled</td><td>$offering</td><td>$pioneers</td></tr>";
}
 
problem with this is that it gets all the rows in the result set, but i want to be able to display a limited number of rows on a page and then include page numbers as clickable links. on clicking a link, i want to display another set of results and so on

Re: Breaking up a result set into batches

Posted: Fri Nov 14, 2008 9:51 am
by chopsmith
You have to put a LIMIT clause on the query.

For example, if you want the first 30 results of a query, do something like:

Code: Select all

 
SELECT * 
FROM table_name
ORDER BY id ASC
LIMIT 0,30
 
If you want the second 30, do:

Code: Select all

 
SELECT * 
FROM table_name
ORDER BY id ASC
LIMIT 30,30
 
The number before the comma is the offset, the number after the comma is the number of rows.

So, if you want the third group of 30, you'd do:

Code: Select all

 
SELECT * 
FROM table_name
ORDER BY id ASC
LIMIT 60,30
 
Obviously, you don't want to hard code the offsets, but rather use something like a page_number variable multiplied by the number of results per page to get the offset value for whatever page you're viewing.

Re: Breaking up a result set into batches

Posted: Fri Nov 14, 2008 10:00 am
by updev
thanks 4 d reply, but i know how to use LIMIT. the problem is how do i get to rerun the query with the new offset value when a link is clicked?

Re: Breaking up a result set into batches

Posted: Fri Nov 14, 2008 10:18 am
by chopsmith
First, calculate the offset and setup the query:

Code: Select all

 
if (isset($_GET['page_number']) {
  $page_number = $_GET['page_number'];
}
else {
  $page_number = 0;
}
$results_per_page = 30;
$offset = $page_number * $results_per_page;
 
$sql = "select * from table_name order by id asc limit {$offset}, {$results_per_page}";
 
Run that query to get the results you want viewed on the current page.

Then, generate the URLs for "next" and "previous" as needed. Something like the following for the "next" link:

Code: Select all

 
$next_page_number = $page_number + 1;
$url = 'index.php?page_number={$next_page_number}';
echo "<a href='{$url}'>Next Page</a>";
 
Hope this helps.