SQL query failing

[josephbupe]

Hi,

 

I want the query initially to return all records where c_id>0 and then filter based on the subsequent criteria supplied through text boxes. However, I am not getting any records printed the first time the page is accessed. When I echo the query I get the following printed instead:

SELECT * FROM collections WHERE ( c_id>0 AND `ctitle` = '' AND `csubject` = '' AND `creference` = '' AND `cobjecttype` = '' AND `cmaterial` = '' AND `ctechnic` = '' AND `csource` = '' AND `cartist` = '' )ORDER BY c_id DESCrequest "Could not execute SQL query" SELECT * FROM collections WHERE ( c_id>0 AND `ctitle` = '' AND `csubject` = '' AND `creference` = '' AND `cobjecttype` = '' AND `cmaterial` = '' AND `ctechnic` = '' AND `csource` = '' AND `cartist` = '' )ORDER BY c_id DESC

And the code is as follows:

$ctitle = mysql_real_escape_string($_POST['ctitle']);
$csubject = mysql_real_escape_string($_POST['csubject']);
$creference = mysql_real_escape_string($_POST['creference']);
$cobjecttype = mysql_real_escape_string($_POST['cobjecttype']);
$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` = '{$ctitle}' AND
    `csubject` = '{$csubject}' AND
    `creference` = '{$creference}' AND
    `cobjecttype` = '{$cobjecttype}' AND
    `cmaterial` = '{$cmaterial}' AND
    `ctechnic` = '{$ctechnic}' AND
    `csource` = '{$csource}' AND
    `cartist` = '{$cartist}'
    )ORDER BY c_id DESC";

Where have I gone wrong here?

 

Joseph

Edited April 10, 2013 by josephbupe

[mikosiko]

That is not enough code to tell, but could be that either your $_POST array is empty or you dont have a database connection open befor to use mysql_real_escape

Edited April 10, 2013 by mikosiko

[josephbupe]

Hi,

 

Thanx for your early response and advice.

 

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

 

My search now works except for a few issues.

 

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 return all records when I click next button after I have done a search based on one of the criterias.

 

Please, 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 the paging class:

<?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)'><<</a></li>";
            else
                $paging.="<li><a href='$url$params$first'><<</a></li>";
            if($page_no==$previous)
                $paging.="<li class='paging-disabled'><a href='javascript:void(0)'><</a></li>";
            else
                $paging.="<li><a href='$url$params$previous'><</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)'>></a></li>";
            else
                $paging.="<li><a href='$url$params$next'>></a></li>";
            if($page_no==$last)
                $paging.="<li class='paging-disabled'><a href='javascript:void(0)'>>></a></li>";
            else
                $paging.="<li><a href='$url$params$last'>>></a></li>";
            $paging.="</ul>";
        }
        return $paging;
    }
}
?>

Your help is highly appreciated in advance.

 

Joseph