469,580 Members | 1,889 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,580 developers. It's quick & easy.

passing a variable to paginated query results

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:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. //where $rory is the variable that is passed via the form with the GET action
  3. $page = $_GET['page']
  4. $rory=$_GET['rory'];
  5. mysql_connect("localhost","<user>","<password>") or die(mysql_error());
  6. mysql_select_db("<db>") or die(mysql_error());
  7. $limit                  = 10;       // Change to how many results you want per page               
  8.          $query_count        = "SELECT * FROM bootlist WHERE venue LIKE '%$rory%' || location LIKE '%$rory%' || cd1 LIKE '%$rory%' || cd2 LIKE '%$rory%' || date LIKE '%$rory%' ORDER BY date";     
  9.          $result_count   = mysql_query($query_count);  
  10.  
  11.          $totalrows          = mysql_num_rows($result_count); 
  12.          if(empty($page)){ 
  13.                  $page = 1; 
  14.          } 
  15.  
  16.          $limitvalue = $page * $limit - ($limit); 
  17. $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");
  18.  $result = mysql_query($query); 
  19.  $num=mysql_num_rows($result);
  20.  if(mysql_num_rows($result) == 0){ 
  21.                  echo("Nothing to Display!"); 
  22.          } 
  23.  
  24.  
  25.  
  26. while ($row = mysql_fetch_array ($result) ) {
  27. echo "Date: ".$row['date'];
  28. echo "<br>Venue: ".$row['venue'];
  29. echo "<br>Location: ".$row['location'];
  30. echo "<br>CD 1: " .$row['cd1'];
  31. echo "<br>CD 2: " .$row['cd2'];
  32. echo "<hr>"; 
  33. }
  34.  
  35.  if($page != 1){ 
  36.                  $pageprev = $page-1; 
  37.  
  38.                  echo("<a href=\"$PHP_SELF?page=$pageprev\">PREV ".$limit."</a>&nbsp;&nbsp;&nbsp;"); 
  39.          }else{ 
  40.                  echo("PREV&nbsp;" .$limit."&nbsp;&nbsp;&nbsp;"); 
  41.          } 
  42.          $numofpages = $totalrows / $limit; 
  43.  
  44.          for($i = 1; $i <= $numofpages; $i++){ 
  45.                  if($i == $page){ 
  46.                          echo($i."&nbsp;"); 
  47.                  }else{ 
  48.                          echo("<a href=\"$PHP_SELF?page=$i\">$i</a>&nbsp;"); 
  49.                  } 
  50.          } 
  51.  
  52.          if(($totalrows % $limit) != 0){ 
  53.                  if($i == $page){ 
  54.                          echo($i."&nbsp;"); 
  55.                  }else{ 
  56.                          echo("<a href=\"$PHP_SELF?page=$i\">$i</a>&nbsp;"); 
  57.                  } 
  58.          } 
  59.          if(($totalrows - ($limit * $page)) >=0){ 
  60.                  $pagenext = $page+1; 
  61.  
  62.                  echo("&nbsp;&nbsp;&nbsp;<a href=\"$PHP_SELF?page=$pagenext\">NEXT ".$limit."</a>"); 
  63.          }else{ 
  64.                  echo("&nbsp;&nbsp;&nbsp;NEXT&nbsp;" .$limit); 
  65.          } 
  66.  
  67.          mysql_free_result($result); 
  68.  
  69.  
  70. ?>
  71.  
Sep 2 '07 #1
2 1691
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.
~
Sep 2 '07 #2
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.
Expand|Select|Wrap|Line Numbers
  1.  if($page != 1){ 
  2.                  $pageprev = $page-1; 
  3.                   echo("<a href=\"$PHP_SELF?page=$pageprev&rory=".$_GET['rory']."\">PREV ".$limit."</a>&nbsp;&nbsp;&nbsp;"); 
  4.          }else{ 
  5.                  echo("PREV&nbsp;" .$limit."&nbsp;&nbsp;&nbsp;"); 
  6.          } 
  7.          $numofpages = $totalrows / $limit; 
  8.  
  9.          for($i = 1; $i <= $numofpages; $i++){ 
  10.                  if($i == $page){ 
  11.                          echo($i."&nbsp;"); 
  12.                  }else{ 
  13.                          echo("<a href=\"$PHP_SELF?page=$i&rory=".$_GET['rory']."\">$i</a>&nbsp;"); 
  14.                  } 
  15.          } 
  16.   if(($totalrows % $limit) != 0){ 
  17.                  if($i == $page){ 
  18.                          echo($i."&nbsp;"); 
  19.                  }else{ 
  20.                          echo("<a href=\"$PHP_SELF?page=$i&rory=".$_GET['rory']."\">$i</a>&nbsp;"); 
  21.                  } 
  22.          } 
  23.          if(($totalrows - ($limit * $page)) >=0){ 
  24.                  $pagenext = $page+1; 
  25.                     echo("&nbsp;&nbsp;&nbsp;<a href=\"$PHP_SELF?page=$pagenext&rory=".$_GET['rory']."\">NEXT ".$limit."</a>"); 
  26.          }else{ 
  27.                  echo("&nbsp;&nbsp;&nbsp;NEXT&nbsp;" .$limit);
  28.  
  29.  
  30.          } 
  31.  
Sep 3 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Nicolae Fieraru | last post: by
2 posts views Thread by Bob Sanderson | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.