SQL Search results and Pagination

Question

Thanx for your early response and advice.

Please, bear with me as I am still not well vested with PHP MySQL programming.

My search works fine except for two issues as follows:

1. By default, I want the query to return all records WHERE c_id > 0 when the page is accessed. c_id is the primary key. Currently, the page does not display any records when I access it. This was not the case before until I added this criteria for category saved as numeric value for a foreign key:

   AND cat_id = ' ".$cat_id." '

2. Also, I want to be able to paginate search results. I was reading some posts and there was a mention of attaching a search term to the link. I do not know how that should be with multiple criteria like in my case. My current pagination returns all records when I click next button after I have done a search based on the criterias.

Here is the whole code I have:

Search page:

<?php

$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']);

$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);

//    pagination code
    $PAGING=new PAGING($sql);

//    There are two optional parameters as well:
//    $records = 16 //Number of records to be displayed per page
//    $pages = Number of pages to be displayed in the paging
    $sql=$PAGING->sql;

if (mysql_num_rows($sql_result)>0) {

//    The following line gives us an SQL statement with appropriate limits applied
    $sql_result=mysql_query($sql) or die($sql." - ".mysql_error());

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

search results .....................

<?=$PAGING->show_paging("gallery.php")?>

And you might also want to look at the paging class I am using:

<?php
/************************************************
*    ========================================    *
*    Perfect MySQL Paging                        *
*    ========================================    *
*    Script Name: class.paging.php                *
*    Developed By: Khurram Adeeb Noorani            *
*    Email: khurramnoorani@gmail.com                *
*    My CV: http://www.visualcv.com/kanoorani    *
*    Twitter: http://www.twitter.com/kanoorani    *
*    Date Created: 08-JULY-2009                    *
*    Last Modified: 08-JULY-2009                    *
************************************************/
?>
<?php
class PAGING
{
    var $sql,$records,$pages;
    /*
    Variables that are passed via constructor parameters
    */
    var $page_no,$total,$limit,$first,$previous,$next,$last,$start,$end;
    /*
    Variables that will be computed inside constructor
    */
    function PAGING($sql,$records=24,$pages=4)
    {
        if($pages%2==0)
            $pages++;
        /*
        The pages should be odd not even
        */
        $res=mysql_query($sql) or die($sql." - ".mysql_error());
        $total=mysql_num_rows($res);
        $page_no=isset($_GET["page_no"])?$_GET["page_no"]:1;
        /*
        Checking the current page
        If there is no current page then the default is 1
        */
        $limit=($page_no-1)*$records;
        $sql.=" limit $limit,$records";
        /*
        The starting limit of the query
        */
        $first=1;
        $previous=$page_no>1?$page_no-1:1;
        $next=$page_no+1;
        $last=ceil($total/$records);
        if($next>$last)
            $next=$last;
        /*
        The first, previous, next and last page numbers have been calculated
        */
        $start=$page_no;
        $end=$start+$pages-1;
        if($end>$last)
            $end=$last;
        /*
        The starting and ending page numbers for the paging
        */
        if(($end-$start+1)<$pages)
        {
            $start-=$pages-($end-$start+1);
            if($start<1)
                $start=1;
        }
        if(($end-$start+1)==$pages)
        {
            $start=$page_no-floor($pages/2);
            $end=$page_no+floor($pages/2);
            while($start<$first)
            {
                $start++;
                $end++;
            }
            while($end>$last)
            {
                $start--;
                $end--;
            }
        }
        /*
        The above two IF statements are kinda optional
        These IF statements bring the current page in center
        */
        $this->sql=$sql;
        $this->records=$records;
        $this->pages=$pages;
        $this->page_no=$page_no;
        $this->total=$total;
        $this->limit=$limit;
        $this->first=$first;
        $this->previous=$previous;
        $this->next=$next;
        $this->last=$last;
        $this->start=$start;
        $this->end=$end;
    }
    function show_paging($url,$params="")
    {
        $paging="";
        if($this->total>$this->records)
        {
            $page_no=$this->page_no;
            $first=$this->first;
            $previous=$this->previous;
            $next=$this->next;
            $last=$this->last;
            $start=$this->start;
            $end=$this->end;
            if($params=="")
                $params="?page_no=";
            else
                $params="?$params&page_no=";
            $paging.="<ul class='paging'>";
            $paging.="<li class='paging-current'>Page $page_no of $last</li>";
            if($page_no==$first)
                $paging.="<li class='paging-disabled'><a href='javascript:void(0)'>&lt;&lt;</a></li>";
            else
                $paging.="<li><a href='$url$params$first'>&lt;&lt;</a></li>";
            if($page_no==$previous)
                $paging.="<li class='paging-disabled'><a href='javascript:void(0)'>&lt;</a></li>";
            else
                $paging.="<li><a href='$url$params$previous'>&lt;</a></li>";
            for($p=$start;$p<=$end;$p++)
            {
                $paging.="<li";
                if($page_no==$p)
                    $paging.=" class='paging-active'";
                $paging.="><a href='$url$params$p'>$p</a></li>";
            }
            if($page_no==$next)
                $paging.="<li class='paging-disabled'><a href='javascript:void(0)'>&gt;</a></li>";
            else
                $paging.="<li><a href='$url$params$next'>&gt;</a></li>";
            if($page_no==$last)
                $paging.="<li class='paging-disabled'><a href='javascript:void(0)'>&gt;&gt;</a></li>";
            else
                $paging.="<li><a href='$url$params$last'>&gt;&gt;</a></li>";
            $paging.="</ul>";
        }
        return $paging;
    }
}
?>

Your help is appreciated in advance.

Answer 1

Range Results

Examples:

 SELECT * FROM `your_table` LIMIT 0, 10 

This will display the first 10 results from the database.

 SELECT * FROM `your_table` LIMIT 5, 5 

This will show records 6, 7, 8, 9, and 10

Is this what you were asking for?