splitting query results into multiple pages?

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
mikegotnaild
Forum Contributor
Posts: 173
Joined: Sat Feb 14, 2004 5:59 pm

splitting query results into multiple pages?

Post by mikegotnaild »

Hi. I have the vision in my head but i have a brain block when it comes to getting the proper syntax down. In this script i have the pagination down. But now when i echo the results... say for example i have this:

Code: Select all

$query = "select * from Acoustic order by band_name limit $offset, $limit";  
    $result = mysql_query($query);  

    // use $result here to output page content
	 $num=mysql_numrows($result);
	 $lo=0;
     while ($lo < $num) {
	 $bandname=mysql_result($result,$lo,"band_name");
          $description=mysql_result($result,$lo,"description");
	 
	 
	 echo "<a href="$urlToExtendedInformation" target="_blank"><b>$bandname</b></a><br><br><b>Description:</b> $description<br><br><hr>";
The echo displays the bandname and description that my users submit. These users also submit even more information though. Well i want to break up each row in my table 'Acoustic' into separate pages.

I need $urlToExtendedInformation to go to their own Band page with extended info. ex. file.php&band=$someVariable. Where $someVariable displays the page number for that band.

This is the extended info i want on the individual band pages.

Code: Select all

$query="SELECT * FROM Progressive";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

$i=0;
while ($i < $num) {

$bandname=mysql_result($result,$i,"band_name");
$description=mysql_result($result,$i,"description");
$history=mysql_result($result,$i,"history");
$influences=mysql_result($result,$i,"influences");
$genra=mysql_result($result,$i,"genra");
$email=mysql_result($result,$i,"email");
$website=mysql_result($result,$i,"website");
$mp3file=mysql_result($result,$i,"mp3file");
$imagefile=mysql_result($result,$i,"imagefile");

echo "<img align=right src="http://naild.com/localmm/upload/$imagefile"><br><b>$bandname</b><br><br><b>Description:</b> $description<br><br><b>History:</b> $history<br><br><b>Influences:</b> $influences<br><br><b>E-mail:</b> $email<br><br><b>Website:</b> <a href="$website">$website</a>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <b>Mp3: <a href="http://naild.com/localmm/upload/$mp3file">$mp3file</a><hr><br>";

++$i;
}
Anyone want to give me some kind of an idea of how to accomplish this? Heres my code i have so far with the pagination.

Code: Select all

<?php 

if (!eregi("modules.php", $PHP_SELF)) {

die ("You can't access this file directly...");
}
include("header.php");
$index = 1;//right sidebar or not 0=off 1=on
title("Acoustic");
OpenTable();
?>

<?php  
   class Pager  
   {  
       function getPagerData($numHits, $limit, $page)  
       {  
           $numHits  = (int) $numHits;  
           $limit    = max((int) $limit, 1);  
           $page     = (int) $page;  
           $numPages = ceil($numHits / $limit);  

           $page = max($page, 1);  
           $page = min($page, $numPages);  

           $offset = ($page - 1) * $limit;  

           $ret = new stdClass;  

           $ret->offset   = $offset;  
           $ret->limit    = $limit;  
           $ret->numPages = $numPages;  
           $ret->page     = $page;  

           return $ret;  
       }  
   }  
?>
<?php  
    // get the pager input values 
    $page = $_GET['page'];  
    $limit = 10;  
    $result = mysql_query("select count(*) from Acoustic");  
    $total = mysql_result($result, 0, 0);  

    // work out the pager values 
    $pager  = Pager::getPagerData($total, $limit, $page);  
    $offset = $pager->offset;  
    $limit  = $pager->limit;  
    $page   = $pager->page;  

    // use pager values to fetch data 
    $query = "select * from Acoustic order by band_name limit $offset, $limit";  
    $result = mysql_query($query);  

    // use $result here to output page content
	 $num=mysql_numrows($result);
	 $lo=0;
     while ($lo < $num) {
	 $bandname=mysql_result($result,$lo,"band_name");
     $description=mysql_result($result,$lo,"description");
	 
	 echo "<a href="$urlToExtendedInformation" ><b>$bandname</b></a><br><br><b>Description:</b> $description<br><br><hr>";
	 
	 ++$lo;
}
?>
<?

    // output paging system (could also do it before we output the page content) 
    if ($page == 1) // this is the first page - there is no previous page 
        echo "Previous";  
    else            // not the first page, link to the previous page 
        echo "<a href="http://naild.com/localmm/modules.php?name=Bands_Showcase&file=acoustic&page=" . ($page - 1) . "">Previous</a>";  

    for ($i = 1; $i <= $pager->numPages; $i++) {  
        echo " | ";  
        if ($i == $pager->page)  
            echo "Page $i ";  
        else  
            echo "<a href="http://naild.com/localmm/modules.php?name=Bands_Showcase&file=acoustic&page=$i">Page $i</a>";  
    }  

    if ($page == $pager->numPages) // this is the last page - there is no next page 
        echo "Next";  
    else            // not the last page, link to the next page 
        echo "<a href="http://naild.com/localmm/modules.php?name=Bands_Showcase&file=acoustic&page=" . ($page + 1) . "">| Next</a>";  
?>

<?
CloseTable();
include("footer.php"); 
?>
Last edited by mikegotnaild on Sat Feb 21, 2004 4:36 am, edited 1 time in total.
mikegotnaild
Forum Contributor
Posts: 173
Joined: Sat Feb 14, 2004 5:59 pm

Post by mikegotnaild »

bump
Post Reply