josephbupe Posted April 10, 2013 Share Posted April 10, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
mikosiko Posted April 10, 2013 Share Posted April 10, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
josephbupe Posted April 11, 2013 Author Share Posted April 11, 2013 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.