Help with pagination on site search script

[f_dkid]

Hi here,

I'm trying to implement a site search script written in PHP. I'm not advanced in PHP but the script is simple and query the DB and show results without a problem. However, there is no way I can get the pagination (previews/next buttoms at the bottom of the results to work. Any ideas? The site is running locally. Thanks in advance
dkid
here is the code

PHP Code:

// Get the search variable from URL

  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=10;

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

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



// Build SQL Query  


$query = "
select contact_id,
contact_first,
contact_last,
contact_title,
contact_image,
contact_profile,
contact_company,
contact_street,
contact_city,
contact_state,
contact_zip,
contact_country,
contact_phone,
contact_fax,  
contact_cell,
contact_email,
contact_alt_email,
contact_web,
contact_updated,
contact_category,
contact_ad_notice,
contact_status,
contact_product,
contact_dist,
contact_listing,
contact_ins_order,
contact_invoice,
contact_payment,
contact_area,
contact_clietype,
contact_season,
contact_subsc,
contact_prtserv,
contact_onlserv,
contact_last_purch

from contacts where contact_first like \"%$trimmed%\" OR contact_last like \"%$trimmed%\" OR contact_category like \"%$trimmed%\" order by contact_first"; // EDIT HERE and specify your table and field names for the SQL query

//$query = "select * from contacts where contact_first like \"%$trimmed%\"  
//  order by contact_first"; // EDIT HERE and specify your table and field names for the SQL query

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

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
  {
  echo "<h4>Results</h4><br>";
  echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";

// google
//echo "<p><a href=\"http://www.google.com/search?q="
// . $trimmed . "\" target=\"_blank\" title=\"Look up
// " . $trimmed . " on Google\">Click here</a> to try the
// search on google</p>";
  }




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

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

// begin to show results set
echo "<h1>Search Results</h1>";
$count = 1 + $s ;


// display what the person searched for
echo "<p>You searched for: <strong>&quot; " . $var . "&quot;</strong> provided the following results:<br><br></p>";




// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
  $title = $row["contact_first"];
  $id = $row["contact_id"];
  $cat = $row["contact_category"];

  echo $count .'. <a href="contact-details.php?id='.$id.'">'.$title.'</a>.  Found under <strong    >'.$cat.'</strong><br><img src="images/spacer.gif" height="10"><br>';
    //echo "$count. $title" ;
  
  
  $count++ ;
  }

$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 "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt;
  Prev 10</a>&nbsp&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++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

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


    

  echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>";
  }

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



//break after next button
  echo "<br />";


[Sheldon]

I quickly rewrote what you hade, It may work let me know of the bugs

PHP Code:

<?php

$searchtext = "";
if(!empty($_POST['q'])){

    $start = $_GET['start'];
    if(empty($start)) {                      
        $start = 0;
    }
    $range = ($start - 0);
    $limit =  10;
    $here  =  $range + $limit;
    $back  =  $range - $limit;
    $next  =  $range + $limit;
    
    $query  = "SELECT * FROM contacts";
    $result = mysql_query($query) or die("There has been a database error, Please contact the administrator. ");
    $nume = mysql_num_rows($result);
    
    $items = explode(" ", $_POST['q']);

    $sql_search .= "SELECT * FROM contacts WHERE contact_first != ''";

    foreach($items as $item) {

        $sql_search .= " OR contact_first     LIKE   '%{$item}%'";
        $sql_search .= " OR contact_last      LIKE   '%{$item}%'";
        $sql_search .= " OR contact_category  LIKE   '%{$item}%'";

    }
    $n = 1;
    $sql_search .= " ORDER BY contact_first ASC LIMIT $start, $limit";
    $query_search = mysql_query($sql_search);
    $number_rows = mysql_num_rows($query_search);

    if($number_rows >= 1) {
        $spages = "";
        if($number_rows >= 2){
            $spages = "s";
        }
        $searchtext .= "<h3>\"".$_POST['query']."\" returned ".$number_rows." Result".$spages.".</h3>";
        $searchtext .= "<ul>";
        while($result_search = mysql_fetch_assoc($query_search)) {
            $searchtext .= "<li>{$result_search['contact_first']} {$result_search['contact_last']} </a>...</li>";
            $n++;
        }

        $searchtext .= "</ul>";
    }else{
        $searchtext = "<p style=\"padding: 20px 0 50px 0\">You search returned <strong>0</strong> results.</p>";
    }
    
    if($back >=0) {
        print "<a href='$page_name?start=$back'>«&nbsp;PREV</a>&nbsp;|&nbsp;";
    }
    
    $pagenation .="";
    $i = 0;
    $l = 1;
    for($i=0;$i < $nume;$i=$i+$limit){
    if($i <> $range){
        $pagenation .= "<a href=\"{$page_name}?start={$i}\">{$l}</a>";
    }else{
        $pagenation .= "<strong>$l</strong>";
    }     
        $l=$l+1;
    }
    
    if($here < $nume) {
        $pagenation .= "<a href=\"{$page_name}?start={$next}\">NEXT 10</a>";
    }
    
}

echo($searchtext);
echo($pagnation);

[bokeh]

Originally Posted by Sheldon

PHP Code:

<?php

$searchtext = "";
if(!empty($_POST['q'])){

    $start = $_GET['start'];
    if(empty($start)) {                      
        $start = 0;
    }
    $range = ($start - 0);
    $limit =  10;
    $here  =  $range + $limit;
    $back  =  $range - $limit;
    $next  =  $range + $limit;
    
    $query  = "SELECT * FROM contacts";
    $result = mysql_query($query) or die("There has been a database error, Please contact the administrator. ");
    $nume = mysql_num_rows($result);
    
    $items = explode(" ", $_POST['q']);

    $sql_search .= "SELECT * FROM contacts WHERE contact_first != ''";

    foreach($items as $item) {

        $sql_search .= " OR contact_first     LIKE   '%{$item}%'";
        $sql_search .= " OR contact_last      LIKE   '%{$item}%'";
        $sql_search .= " OR contact_category  LIKE   '%{$item}%'";

    }
    $n = 1;
    $sql_search .= " ORDER BY contact_first ASC LIMIT $start, $limit";
    $query_search = mysql_query($sql_search);
    $number_rows = mysql_num_rows($query_search);

    if($number_rows >= 1) {
        $spages = "";
        if($number_rows >= 2){
            $spages = "s";
        }
        $searchtext .= "<h3>\"".$_POST['query']."\" returned ".$number_rows." Result".$spages.".</h3>";
        $searchtext .= "<ul>";
        while($result_search = mysql_fetch_assoc($query_search)) {
            $searchtext .= "<li>{$result_search['contact_first']} {$result_search['contact_last']} </a>...</li>";
            $n++;
        }

        $searchtext .= "</ul>";
    }else{
        $searchtext = "<p style=\"padding: 20px 0 50px 0\">You search returned <strong>0</strong> results.</p>";
    }
    
    if($back >=0) {
        print "<a href='$page_name?start=$back'>«&nbsp;PREV</a>&nbsp;|&nbsp;";
    }
    
    $pagenation .="";
    $i = 0;
    $l = 1;
    for($i=0;$i < $nume;$i=$i+$limit){
    if($i <> $range){
        $pagenation .= "<a href=\"{$page_name}?start={$i}\">{$l}</a>";
    }else{
        $pagenation .= "<strong>$l</strong>";
    }     
        $l=$l+1;
    }
    
    if($here < $nume) {
        $pagenation .= "<a href=\"{$page_name}?start={$next}\">NEXT 10</a>";
    }
    
}

echo($searchtext);
echo($pagnation);

That's no good, the queries should be identical (but without the limit clause) otherwise the number of rows will not correspond to the search result. Also The first should be a count query otherwise you are going to unnecessarilly download a huge chunck of the table to PHP every time the page is requested.

# query one
select
    count(*)
  from tablename
  where contact_first != ''
    or contact_first like '%{$item}%'
    or contact_last like '%{$item}%'
    or contact_category like '%{$item}%'

# query two
select
    *
  from tablename
  where contact_first != ''
    or contact_first like '%{$item}%'
    or contact_last like '%{$item}%'
    or contact_category like '%{$item}%'
  limit $limit
  offset $offset

[f_dkid]

Hi guys, thanks so much. Unfortunately I cannot get the rewritten code to work. However, the one I posted works fine. It just don't turn the pages with the previews/next button. I think the $s value is passed by the form? I don't get it but the $s value exists and the code makes the sum of $s+ - $limit without a problem. But when I click on the "next" link it wont turn the pages with the next 10 rows. However, If i set the $s value manually, for example to 10 instead of 0, the results automatically shows from the 11th row to the 20th. But the preview/next buttons wont work. Hope this make sense. Other than that, my code querys and gives results flawlessly. Thanks again

[Sheldon]

just a quick stab at it tonight.

move you $count = 1 + $s ; in side your query while and your next button goes ahead 10 so you may want to have $next = ($count + 1);

[f_dkid]

Hi guys. Thanks for all the posts. This must be a silly thing, it doesn't work! I can even see how the $s value changes in the address bar , but it keep showing only the first matches, whatever the $limit value is. I assume the code works correctly but for some reason, the $ value don't has any effect on the code althou is correctly passed to the browser. Please help, stocked on this for good!

[Sheldon]

Can you post an example to this, like a link so we can see what is going on?