473,226 Members | 1,453 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

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 1917
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

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

Similar topics

0
by: Omar K | last post by:
Hi, I am quite new to frontpage and SQL but I have a Stock Control access database / frontpage to set up. My last problem deals with the automatic updating of the total quantity of stock with parts...
3
by: Nicolae Fieraru | last post by:
Hi All, I have a table, tblCustomers, with fields SalutationID and Firstname. I made a query, qrySelect = "Select FirstName from tblCustomers Where SalutationID = " If I run this query by...
2
by: Chris | last post by:
I have two forms. From form one I have a listbox that when double clicked I get the selected value (string) and store in a variable. I parse the string to get the first 12 characters and store it...
2
by: Bob Sanderson | last post by:
I have a search form from which I hope to be able to select a record by field JobNumber and display it with an output form titled test.php <html> <head> <title>Job Database Search</title>...
4
by: Ranginald | last post by:
Hi, I'm having trouble passing a parameter from my default.aspx page to my default2.aspx page. I have values from a query in a list box and the goal is to pass the "catID" from default.aspx...
0
by: kencana | last post by:
hi All, I got problem in passing data (more than one) from soap client to the soap server. but if i only passing one data at a time, it works successfully.. The following is the error message i...
1
by: rfr | last post by:
I have a need to use a single version of a Visitor Response Feedback Form on numerous HTML documents. Rather than have numerous versions of this, one on each HTML document, it makes more sense to...
5
by: RacerX2000 | last post by:
I have an MS access Database (2000) and Have created a form that sets a variable to a value I would like (Based on other selections in the form) to pass to my query criteria and I get the following...
11
by: Purdue02 | last post by:
I am trying to pass a global variable to criteria in a query using the below code, but the query is returning no results. I have the function ReturnStrCriteria() included in the query's criteria....
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.