Paginating criteria-based search results

[josephbupe]

Hi,

 

This will be my first time to use a criteria-based search with pagination togather.

 

I want to paginat my search query results and be able to go to next and previous pages of my search results based on the search criteria. For now the pagination returns all records from the table. I have tried to include one search term in the link for NEXT and PREVIOUS buttons but still when I click the button I next page load all the records from the MySQL table. The search term (e.g. gallery.php?cmaterial=$cmaterial) appears in the URL only when I click NEXT and not on the current page. Even then the search results do not match the search term.

 

Here is my code:

<?php

$s=$_GET['s'];

// rows to return
$limit=12;

// check for a search parameter
//if (!isset($var))
//  {
//  echo "<p>We dont seem to have a search parameter!</p>";
//  exit;
//  }

$ctitle = mysql_real_escape_string($_POST['ctitle']);
$csubject = mysql_real_escape_string($_POST['csubject']);
$creference = mysql_real_escape_string($_POST['creference']);
$cat_id = ($_POST['cat_id']);
$cmaterial = mysql_real_escape_string($_POST['cmaterial']);
$ctechnic = mysql_real_escape_string($_POST['ctechnic']);
$cartist = mysql_real_escape_string($_POST['cartist']);
$csource = mysql_real_escape_string($_POST['csource']);
$stolen = mysql_real_escape_string($_POST['stolen']);

$sql = "SELECT * FROM collections WHERE c_id>0 AND `ctitle` LIKE '%".$ctitle."%' AND `csubject` LIKE '%".$csubject."%' AND `creference` LIKE '%".$creference."%' AND `cat_id` LIKE '%".$cat_id."%' AND `cmaterial` LIKE '%".$cmaterial."%' AND `ctechnic` LIKE '%".$ctechnic."%' AND `cartist` LIKE '%".$cartist."%' AND `csource` LIKE '%".$csource."%'  ORDER BY c_id ASC";

$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);

 $numresults=mysql_query($sql);
 $numrows=mysql_num_rows($numresults);

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
$sql .= " limit $s,$limit";
  $sql_result = mysql_query($sql) or die("Couldn't execute query");

$count = 1 + $s ;

while ($row = mysql_fetch_assoc($sql_result)) {
    $c_id=$row['c_id'];
?>

..............................................HTML RESULT TABLE ...................................................

<?php
$currPage = (($s/$limit) + 1);

//break before paging
  echo "<br />";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print " <a href=\"$PHP_SELF?s=$prevs&cmaterial=$cmaterial\"><<
  Prev 10</a>&nbsp ";
  }

// calculate number of pages needing links
  $pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }
echo "</table>";
// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

    echo " <a href=\"$PHP_SELF?s=$news&cmaterial=$cmaterial\">Next 10 >></a>";
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
    echo "<p>Showing results $b to $a of $numrows</p>";

?>

What should I do next?

 

Thank you in advance.

 

Joseph

Edited April 13, 2013 by josephbupe

[jcbones]

You have a lot of variables that need to be set in order to pull your data.  $cmaterial is only 1 of 8 needed to properly form the query string.

 

You can do 1 of 2 things.  

1. Set all the variables in a session.  If POST is set, load the POST data to variables, if not load the SESSION data to the variables.

2. You can set all 8 variables in the URI, if POST is set, load POST into the variables, if not load the GET data into variables.

 

1. means you have no limit to the string lengths.

2. means users can bookmark search results.

[josephbupe]

Hi,

 

Ok, this is what I have now but the search cant return records based on any criteria I use:

$criteria = array('ctitle', 'csubject', 'creference', 'cat_id', 'cmaterial', 'ctechnic', 'cartist', 'csource', 'stolen');
$likes = "";
$url_criteria = '';
foreach ( $criteria AS $criterion ) {
if ( ! empty($_POST[$criterion]) ) {
$value = ($_POST[$criterion]);
$likes .= " AND `$criterion` = '%$value%'";
$url_criteria .= '&'.$criterion.'='.htmlentities($_POST[$criterion]);
} elseif ( ! empty($_GET[$criterion]) ) {
$value = mysql_real_escape_string($_GET[$criterion]);
$likes .= " AND `$criterion` = '%$value%'";
$url_criteria .= '&'.$criterion.'='.htmlentities($_GET[$criterion]);
}
}
$sql = "SELECT * FROM collections WHERE c_id>0" . $likes . " ORDER BY c_id ASC";
echo $sql;

When I echoed the query I got this:

SELECT * FROM collections WHERE c_id>0 AND `cmaterial` = '%wood%' ORDER BY c_id ASC

If this works I want to get the variable into the URL using the foreach loop above.

 

I just need a third eye, please.

 

Joseph

[josephbupe]

Resolved. Thanx