[Resolved] pagination using returned records not total possible records?

[etoast]

i have a pagination issue. i used the code below which works fine except that it calculates the total number of records in tha database and then bases the page numbers on that. but my search may only bring back one result in which case i don't want all the page number links displaying.

every pagination code example seems to work like this but there must be a way of limiting the number of page links displayed based on the number of rows returned in the query. i just cannot figure out how. can anyone help please?

here is the code:

if(!isset($_GET['page'])){
      $page = 1;
  } else {
      $page = $_GET['page'];
  }

// Define the number of results per page
  $max_results = 10;

// Figure out the limit for the query based
// on the current page number.
  $from = (($page * $max_results) - $max_results);

  ....query here....

// Figure out the total number of results in DB:
  $total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM music"),0);

// Figure out the total number of pages.
  $total_pages = ceil($total_results / $max_results);

// show links to more pages.
for($i = 1; $i <= $total_pages; $i++){
  if(($page) == $i){
  echo "<strong><font color=#FF0000>$i </font></strong>";
  } else {
  echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
  }
  if(($i + 30) % 30 == 0)
  {
  echo '&nbsp;';
  }
  }

[bastien]

run two queries, one to count the total number of maches based on the where clause and the other to get the total data.

THen you can generate with limits

[etoast]

thanks but the query results are limited like this:

$result = mysql_query("SELECT * FROM table WHERE id='".$id."' AND location='".$location."' AND type='".$type."' LIMIT $from, $max_results") or die (mysql_error());

only i have 16 variations on the query (depending on what is picked in the search drop downs). i cannot figure out how to get the number that represents the total number of matches. because it is already limited to 10.

the bit that says for($i = 1; $i <= $total_pages; $i++){ is using $total_pages being calculated from $total_results which is the entire database, not the relevant matches in the database.

can anyone elaborate on how this can be changed please?
thanks.

[bastien]

No, the LIMIT is 10, you may have more matches...

Consider this code snippet from a site I wrote

...
// get the vehicle count and last updated time
$sql_match = "select count(1) as count from vehicle_inventory where record_deleted != 1";

// get the list of items in the inventory
$sql = "select a.*, b.thumb from vehicle_inventory a left outer join vehicle_imagelist b on
			 a.ccode = b.ccode where a.record_deleted != 1 ";

// from this page to search based on criteria
if (!empty($make)){
	$sql_where .= " and upper(make) like '$make%' ";
	$y++;
}
if (!empty($model)){
		$sql_where .= " and upper(model) like '$model%' ";
}
if (!empty($category)){
			$sql_where .= " and upper(category) like '$category%' ";
}
if (!empty($condition)){
		$sql_where .= " and upper(condition) like '$condition%' ";
}

$sql .= $sql_where . " order by added desc, make limit $NextBlock, $display";

// get the count and date
$sql_match .= $sql_where;
$matches		= connect($sql_match);

if (($matches)&&(mysql_num_rows($matches)>0)){
	$rs = mysql_fetch_array($matches);
	$count	 = $rs['count'];
}

// get the date
$sql_date = "select max(added) from vehicle_inventory";
$check_date = connect($sql_date);
$dates = mysql_fetch_array($check_date);
$my_date = $dates[0];

//echo $sql;

// get the regular listing results
$result = connect($sql);
....

You see how I separate out the where clause from the limit? Then I run the two queries. Matches produces the total number of matches and the main query produces the recordset with the limit.

hth

[etoast]

thanks for your help bastien. i had trouble applying your method to my existing code. i'm absolutely certain that if i started again i could make it work much better, however, i did manage a bit of lateral thinking and wrote something that worked - even though i admit is is a bad bad way to do it:

to get the total number of pages, rather than count every record in the database as all pagination scripts seem to do, i knew it would work if i ran the query without the limit. then counted the rows in the query result to get the total results...

$total_results = mysql_num_rows($result);
$total_pages = ceil($total_results / $max_results);

then run the query again and put the LIMIT back into the query. it means running the same query twice which isn't pretty. but it works so i'm happy.

[BarryJ]

I followed your example but am unable to display more than 1 page of results, out of anything between 4 and 19 pages depending on the circumstances of the request. Page one has the 10 results , but when you click on page 2 or more the page shows no results, althogu there are plenyt.

Here's the core code for the query:

			<?php

			  if(!isset($_GET['page'])){ 
				  $page = 1; 
			  } else { 
				  $page = $_GET['page']; 
			  } 

			// Define the number of results per page 
			  $max_results = 10; 

			// Figure out the limit for the query based 
			// on the current page number. 
			$from = (($page * $max_results) - $max_results); 


			$whereclause="(lp_dol BETWEEN '$priceLow' AND '$priceHigh' AND class='$class')";
			if ($bedrooms!="NoMin") $whereclause.=" AND (br='$bedrooms')";
			if ($bathrooms!="NoMin") $whereclause.=" AND (bath_tot='$bathrooms')";
			if ($County!="All") $whereclause.=" AND (county='$County')";
			if ($Town!="All") $whereclause.=" AND (town='$Town')";
			if ($waterfrontOrWaterview!=" ") $whereclause.=" AND (waterfront='$waterfrontOrWaterview')";

			$rst=Query("SELECT * FROM tBRSfeed WHERE $whereclause");

			$total_results = mysql_num_rows($rst); 

			// Figure out the total number of pages. 
			  $total_pages = ceil($total_results / $max_results); 

			// show links to more pages. 
			for($i = 1; $i <= $total_pages; $i++){ 
			  if(($page) == $i){ 
			  echo "<strong><font color=#FF0000>$i </font></strong>"; 
			  } else { 
			  echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> "; 
			  } 
			  if(($i + 30) % 30 == 0) 
			  { 
			  echo '&nbsp;'; 
			  } 
			  }

			$rst=Query("SELECT * FROM tBRSfeed WHERE $whereclause LIMIT $from, $max_results");
			?>

If I donot use pagination I get all the results on one page , obviously, any ideas?

[bastien]

you have somewhere i can see it in action? its very likely something small that can easily be corrected...

[BarryJ]

If you go to this url

http://www.homestarrg.com/buyers/mls_full_search_dev2.php

Choose a county and just a single town, as the multiple select doesn't work either, you should be OK.

[BarryJ]

I have removed the code while I try and find the answer. I tried other code and got the same results, an empty recordset on the second page. Running a debug script on the page showed that no values where being passed when moving on to the second or subsequest pages.

[BarryJ]

The problem it seems to me is that the results are generated by a sql statement dependent on the values from a form and when the next page option is chosen the values are gone, hence a blank page, ie no values for the 'where' clause in the sql statement.

If anybody has any ideas I'd appreciate them.

[Roger_Ramjet]

Ah, PAGINATION, that perenial problem.

Lots of ways to solve this, some simple, many as complicated as it gets.

If you are using sessions then you can store the required information to keep track of the pages in session vars.

If not then you have to store them in hidden fields on the html page.

Now you can work this in many ways:
1. store the whole query string and modify the LIMIT clause from page to page
2. store the values from the WHERE clause and the LIMIT clause to re-generate the query
3. calculate the Limit values for the next and last pages and embed them as values in your Next/Last buttons/links on the html page, along with the Where values in a hidden field
4. store the WHERE values and the page number and use a formula to calculate the Limit values.

My solution:

If you are displaying 20 results per page then query for 21: that way you know if there will be a next page: ie if you get 21 records back then there is at least 1 record to display on the next page. 😃

I store the Where values as session vars along with the page number and rows per page (rpp). I can then calculate the Limit values from the page number/rows per page thus:

LIMIT (rpp x (page - 1)), (rpp + 1)

This works cos the offset of the rows starts at 0, ie page 1 displays 0-19 so page 2 displays (20 x (2-1)) = 20-39 , page 3 displays (20 x (3-1)) = 40-69 etc

I store the rows per page as a var instead of hard-coding it so that it can be reset without changing my code.

Thus, my html only needs to indicate back or forwards. Back then p in my formula = current page - 1, forwards = current page + 1

Bear in mind that users will rarely page through more than 3 or 4 pages so working out that there are enough records for 50 pages is just a waste of processing time. If you want to look fancy then just query for 4 times rpp and give links for next 3 pages if they exist.

Hope you follow this and that it helps. 🆒

[BarryJ]

Thanks for this, very useful, I will try and get my rusty old brain around it all and hope it all works eventually.