passing a variable to paginated query results

[milo1955]

I have a form that passes a single variable via the GET function to a mysql databse query with the results paginated. The first page of the results work fine, but the link to the second page yields the page 2 results of the entire table. I'm guessing that the variable hasn't passed to page2 query? Please excuse my ignorance. Here's my code:

<?php
//where $rory is the variable that is passed via the form with the GET action
$page = $_GET['page']
$rory=$_GET['rory'];
mysql_connect("localhost","<user>","<password>") or die(mysql_error());
mysql_select_db("<db>") or die(mysql_error());
$limit                  = 10;       // Change to how many results you want per page               
         $query_count        = "SELECT * FROM bootlist WHERE venue LIKE '%$rory%' || location LIKE '%$rory%' || cd1 LIKE '%$rory%' || cd2 LIKE '%$rory%' || date LIKE '%$rory%' ORDER BY date";     
         $result_count   = mysql_query($query_count);  
 
         $totalrows          = mysql_num_rows($result_count); 
         if(empty($page)){ 
                 $page = 1; 
         } 
 
         $limitvalue = $page * $limit - ($limit); 
$query = ("SELECT * FROM bootlist WHERE venue LIKE '%$rory%' || location LIKE '%$rory%' || cd1 LIKE '%$rory%' || cd2 LIKE '%$rory%' || date LIKE '%$rory%' ORDER BY date LIMIT $limitvalue, $limit");
 $result = mysql_query($query); 
 $num=mysql_num_rows($result);
 if(mysql_num_rows($result) == 0){ 
                 echo("Nothing to Display!"); 
         } 
 
 
 
while ($row = mysql_fetch_array ($result) ) {
echo "Date: ".$row['date'];
echo "<br>Venue: ".$row['venue'];
echo "<br>Location: ".$row['location'];
echo "<br>CD 1: " .$row['cd1'];
echo "<br>CD 2: " .$row['cd2'];
echo "<hr>"; 
}
 
 if($page != 1){ 
                 $pageprev = $page-1; 
 
                 echo("<a href=\"$PHP_SELF?page=$pageprev\">PREV ".$limit."</a>&nbsp;&nbsp;&nbsp;"); 
         }else{ 
                 echo("PREV&nbsp;" .$limit."&nbsp;&nbsp;&nbsp;"); 
         } 
         $numofpages = $totalrows / $limit; 
 
         for($i = 1; $i <= $numofpages; $i++){ 
                 if($i == $page){ 
                         echo($i."&nbsp;"); 
                 }else{ 
                         echo("<a href=\"$PHP_SELF?page=$i\">$i</a>&nbsp;"); 
                 } 
         } 
 
         if(($totalrows % $limit) != 0){ 
                 if($i == $page){ 
                         echo($i."&nbsp;"); 
                 }else{ 
                         echo("<a href=\"$PHP_SELF?page=$i\">$i</a>&nbsp;"); 
                 } 
         } 
         if(($totalrows - ($limit * $page)) >=0){ 
                 $pagenext = $page+1; 
 
                 echo("&nbsp;&nbsp;&nbsp;<a href=\"$PHP_SELF?page=$pagenext\">NEXT ".$limit."</a>"); 
         }else{ 
                 echo("&nbsp;&nbsp;&nbsp;NEXT&nbsp;" .$limit); 
         } 
 
         mysql_free_result($result); 
 
 
?>
 

[Weisbartb]

Hi, First you have an issue with your pagination method.
[php]
if($totalrows % $limit == 0){
$pages = $totalrows/$limit;
}else{
$pages = (int) ($totalrows-($totalrows % $limit))/$limit+1;
}
[/php]
Use that so you will properly paginate. When you use a for loop it deals with whole integers only. That will insure it gets an accurate page count.

The next issue is where you are creating your pagination bar. Not sure what exactly you are trying to do there.
[php]
if($pages == 0) $pages =1;
$bar = '';
for($i=1;$i<=$pages;$i++){
if($_GET['page'] == $i){
$bar .= '<b>'.($i).'</b>&nbsp;';
}else{
$bar .= '<a href="'.$_SERVER['PHP_SELF'].'?page='.$i.'">'.($i).'</a>&nbsp;';
}
}
[/php]
Above is an example of a way I generate pagination bars. Hope that is helpful.
~

[milo1955]

Thanks for another way to get the value whole. I used the integer "i" to get a whole value for my pages. As it turns out the problem with the query was in my not passing the variable from the form into the URL of my "previous" and "next" links along with the page variable, i.e.

 if($page != 1){ 
                 $pageprev = $page-1; 
                  echo("<a href=\"$PHP_SELF?page=$pageprev&rory=".$_GET['rory']."\">PREV ".$limit."</a>&nbsp;&nbsp;&nbsp;"); 
         }else{ 
                 echo("PREV&nbsp;" .$limit."&nbsp;&nbsp;&nbsp;"); 
         } 
         $numofpages = $totalrows / $limit; 
 
         for($i = 1; $i <= $numofpages; $i++){ 
                 if($i == $page){ 
                         echo($i."&nbsp;"); 
                 }else{ 
                         echo("<a href=\"$PHP_SELF?page=$i&rory=".$_GET['rory']."\">$i</a>&nbsp;"); 
                 } 
         } 
  if(($totalrows % $limit) != 0){ 
                 if($i == $page){ 
                         echo($i."&nbsp;"); 
                 }else{ 
                         echo("<a href=\"$PHP_SELF?page=$i&rory=".$_GET['rory']."\">$i</a>&nbsp;"); 
                 } 
         } 
         if(($totalrows - ($limit * $page)) >=0){ 
                 $pagenext = $page+1; 
                    echo("&nbsp;&nbsp;&nbsp;<a href=\"$PHP_SELF?page=$pagenext&rory=".$_GET['rory']."\">NEXT ".$limit."</a>"); 
         }else{ 
                 echo("&nbsp;&nbsp;&nbsp;NEXT&nbsp;" .$limit);
 
 
         }