search results show all results from table if feild is left

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
hidden_pearl
Forum Newbie
Posts: 2
Joined: Sun Jul 19, 2009 4:58 am

search results show all results from table if feild is left

Post 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,
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

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

Post 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
 
 
dom_scott
Forum Newbie
Posts: 3
Joined: Wed Apr 29, 2009 10:21 am

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

Post 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;
 
hidden_pearl
Forum Newbie
Posts: 2
Joined: Sun Jul 19, 2009 4:58 am

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

Post by hidden_pearl »

thanx to jack and dom .... i will try it and be back with results .... :)
Post Reply