Breaking up a result set into batches

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
updev
Forum Newbie
Posts: 2
Joined: Fri Nov 14, 2008 9:03 am

Breaking up a result set into batches

Post 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
User avatar
chopsmith
Forum Commoner
Posts: 56
Joined: Thu Nov 13, 2008 10:40 am
Location: Red Bank, NJ, USA

Re: Breaking up a result set into batches

Post 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.
updev
Forum Newbie
Posts: 2
Joined: Fri Nov 14, 2008 9:03 am

Re: Breaking up a result set into batches

Post 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?
User avatar
chopsmith
Forum Commoner
Posts: 56
Joined: Thu Nov 13, 2008 10:40 am
Location: Red Bank, NJ, USA

Re: Breaking up a result set into batches

Post 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.
Post Reply