Page 1 of 1

search results show all results from table if feild is left

Posted: Sun Jul 19, 2009 5:03 am
by hidden_pearl
Hi,

i am stuck with this search code ....

Code: Select all

$query = " SELECT * FROM table WHERE  
     fisrt_name LIKE '$first_name%'
OR last_name LIKE '$last_name%'
OR family_name LIKE '$family_name%'
 
 ";
the problem is: i want to allow users to leave empty feilds in search form for first_name, last_name and family_name. but when a user leave any feild empty then search shows all data from table. all what i want is, that if user leaves any feild empty then it must not include for search parameters.

can anyone please guid me where i am wrong or what should i do.
(please let me know if i have not explained properly)

Regards,

Re: search results show all results from table if feild is left

Posted: Sun Jul 19, 2009 6:58 am
by jackpf
You'll need to do some conditioning then.

Something like this:

Code: Select all

 
$first_name = ($_POST['first_name'] != null) ? "AND `first_name` LIKE '%".mysql_real_escape_string($_POST['first_name'])."%'" : null;
//do this for the rest of the values....and then insert into the query
 
 

Re: search results show all results from table if feild is left

Posted: Mon Jul 20, 2009 4:56 am
by dom_scott
That's basically it, you need to build dynamically your query.
If you use Zend Framework you might find it partiularly useful to make a query using the
select() construct of the database adapter classes.
That will allow you to do something like this (not mentioning full escaping of string input):

Code: Select all

 
// $db is your Zend Db adapter instance 
// and $params is the array of search parameters
 
if (!empty($params))
{
  $query = $db->select()->from("my_table");
  if ($params['city'])
    $query->where("city = ?",$params['city']);
  if ($params['surname'])
    $query->where("surname = ?", $params['surname']);
 
$results = $db->fetchAll($query);
}
else 
  $results = null;
 

Re: search results show all results from table if feild is left

Posted: Mon Jul 20, 2009 8:05 am
by hidden_pearl
thanx to jack and dom .... i will try it and be back with results .... :)