Pagination - search criterias

[klepec]

Can anyone suggest how to make pagination for search results?

 

The problem here is that WHERE (search criterias / input fields filled by user) resets every time you go to other page (page1, page2, page3).

 

Also, there is not always the same number of criterias because empty fields (fields ommited by user) are left out.

 

Thanks

[PFMaBiSmAd]

See items #2 and #3 in the following post - http://forums.phpfreaks.com/index.php?topic=363037.msg1717726#msg1717726

[PFMaBiSmAd]

Here's an example search/pagination script -

 

<?php

// settings used by this code -
$rows_per_page = 20; // how many rows to display per logical page
$pagination_name = 'pageno'; // the $_GET[xxxxx] index name to use for pagination
$pagination_range = 3; //  maximum number of pagination links to show either side of the currently selected page

// connect to your database server and select your database here...

// assuming this is being used for a search script, output a simple search form and produce a $where_clause to match the rows you are interested in
$search_form = "<form method='get' action=''>Search: <input type='text' name='search'><input type='submit'></form>";
echo $search_form;

// get and condition any search term
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$where_clause = '';
if($search != ''){
// form a simple LIKE '%serach term%' comparison
$where_clause = sprintf("WHERE your_column LIKE '%%%s%%'",mysql_real_escape_string($search));
}

// define the main and count queries
$main_query = "SELECT * FROM your_table $where_clause";
$count_query = "SELECT COUNT(*) FROM your_table $where_clause";

// find the total number of matching rows
$result = mysql_query($count_query) or die("Query failed: $count_query<br />Error: " . mysql_error());
list($total_rows) = mysql_fetch_row($result);

// calculate the total number of logical pages
$total_pages = ceil($total_rows/$rows_per_page);

// get and condition or set a default for the requested page
$requested_page = isset($_GET[$pagination_name]) ? intval($_GET[$pagination_name]) : 1;

// set max/min limits for the requested page. max first, then min so if the total is zero (no matching data), the requested page is 1
if($requested_page > $total_pages){
   $requested_page = $total_pages;
}
if($requested_page < 1){
   $requested_page = 1;
}

// calculate the starting row number for the requested logical page
$offset = ($requested_page - 1) * $rows_per_page;

// form the actual query to retrieve the matching data for the requested logical page
$query = "$main_query LIMIT $offset, $rows_per_page";

// query for the actual data
$result = mysql_query($query) or die("Query failed: $query<br />Error: " . mysql_error());

// get number of rows returned by the query for the logical page
$num_rows = mysql_num_rows($result);
if($num_rows == 0){
// query matched no rows
echo "There are no matching records to display on this page.";
} else {
echo "Your query matched $total_rows record" .($total_rows > 1 ? 's' : '').". ";
echo "Displaying records: ".($offset+1)." - " . ($offset+$num_rows) . ".<br />";
// loop over the matching rows and output the data the way you want on your page
while($row = mysql_fetch_assoc($result)){
	echo $row['your_column'] . '<br />';
}
}

// build pagination navigation links (if there's more than one page)
// this code uses http_build_query to build the query string on the end of the URL so that any existing get parameters, such as a search term, are not modified. This code only modifies the pagination get parameter and leaves all other get parameters as is.
$pagination_links = ''; // build pagination links in a string (output it later in your actual content on the page)
if($total_pages > 1){
// produce 'first' and 'prev' links
if($requested_page > 1){
	// 'first' page link
	$_GET[$pagination_name] = 1; // set/replace the pagination GET parameter (all other GET parameters unchanged)
	$pagination_links .= "<a href='?" . http_build_query($_GET, '', '&') . "'><<</a> ";
	// 'prev' page link
	$_GET[$pagination_name] = $requested_page - 1; // set/replace the pagination GET parameter (all other GET parameters unchanged)
	$pagination_links .= " <a href='?" . http_build_query($_GET, '', '&') . "'><</a> ";
} else {
	// text only place holders
	$pagination_links .= " << < ";
}

// loop to produce links for a range of pages around the currently selected page
for($x = $requested_page - $pagination_range; $x < $requested_page + $pagination_range + 1; $x++){
	// if between min and max page number
	if($x > 0 && $x <= $total_pages){
		// if currently requested page, output text only place holder
		if($x == $requested_page){
			$pagination_links .= " [<b>$x</b>] ";
		} else {
			// output page link
			$_GET[$pagination_name] = $x; // set/replace the pagination GET parameter (all other GET parameters unchanged)
			$pagination_links .= " <a href='?" . http_build_query($_GET, '', '&') . "'>$x</a> ";
		}
	}
}
                 
// produce 'next' and 'last' links
if($requested_page != $total_pages){
	// 'next' page link
	$_GET[$pagination_name] = $requested_page + 1; // set/replace the pagination GET parameter (all other GET parameters unchanged)
	$pagination_links .= " <a href='?" . http_build_query($_GET, '', '&') . "'>></a> ";
	// 'last' page link
	$_GET[$pagination_name] = $total_pages; // set/replace the pagination GET parameter (all other GET parameters unchanged)
	$pagination_links .= " <a href='?" . http_build_query($_GET, '', '&') . "'>>></a>";
} else {
	// text only place holders
	$pagination_links .= " > >>";
}
}

// output the pagination navigation links
echo $pagination_links; // echo the links wherever you want in the content on your page

 

 

 

[charles724]

This search criteria / pagination source code is a great example of how to do search and pagination. I've copied the code verbatim - with the exceptions of the database name and table name - but I have an issue that I can't seem to fix. When you first run the script, it falls through and displays all the records. Only when I actually fill in the search values does it operate correctly. I've tried to put conditions to bypass the erroneous listings when first called but am having no luck. Has anyone else had this issue with this script? If so, how did you fix it?

[PFMaBiSmAd]

If you don't want the query/pagination logic to run unless the search form has been submitted with a non-empty search term, just enclose all the relevant logic inside the test for the search term by moving the closing } down to the end of the logic -

if($search != ''){

// form a simple LIKE '%serach term%' comparison

$where_clause = sprintf("WHERE your_column LIKE '%%%s%%'",mysql_real_escape_string($search));

} <------ move this to after the end of the posted logic

[charles724]

Thank you! Works perfect.