Query for PHP/MySql AND/OR for an advanced search

Question

I'm new to php and mysql so I need some help with my query. This is my sql query

SELECT * FROM table1 WHERE (Name LIKE '%$keyword%' OR ZipCode LIKE '%$keyword%') AND Category LIKE '$category' AND Country LIKE '$country' LIMIT $start, $limit

Now what I want to do exaclty is

This query should search column names with Name or ZipCode using a text field with the name keyword. That is either keyword matches the Name column or the ZipCode.
The query should also work if no keyword is entered and only a country is selected from a dropdown.
The query should also work if no keyword or country is input and only a category is selected from a dropdown.

In more simple words if either of the things keyword, country or category is selected the search should display results accordingly. Moreover it should also work if all the fields are input. I managed to do half of it but sometimes it gives me wrong results. Please help.

You should really add your code; but as a rough guide - you need to check to see which parameters have been set in `$_REQUEST`, and then build your query appropriately - there's no need to search Name or ZipCode if keyword isn't set. — andrewsi, Nov 09 '12 at 16:48
Also please be sure to read about [SQL injection](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain) — juanrpozo, Nov 10 '12 at 13:33

score 10 · Accepted Answer · answered Nov 09 '12 at 17:02

10

You can check which of the fields are set and make query accordingly at runtime. something like this you can do:

$query = "";
$keyword = $_REQUEST['keyword'];
$country = $_REQUEST['country'];
$category = $_REQUEST['category'];
if(isset($keyword)){//if keyword set goes here
   $query = "SELECT * FROM table1 WHERE Name LIKE '%$keyword%' OR ZipCode LIKE '%$keyword%' OR country LIKE '%$keyword%'";
   if(isset($category)){
     $query .= "AND category LIKE '$category'";
   }
   if(isset($country)){
     $query . = "AND country LIKE '$country'"
   }
}else if (isset($category)){ //if keyword not set but category set then goes here
  $query = "SELECT * FROM table1 WHERE category LIKE '$category'";
  if(isset($country)){
    $query . = "AND country LIKE '$country'";
  }
}else if(isset($country)){//if only country set goes here
  $query = "SELECT * FROM table1 WHERE country LIKE '$country'"
}

answered Nov 09 '12 at 17:02

Smita

4,634
2
25
32

This is the best answer. Thankyou so much @Smita this was really easy to understand and very helpful. :) – Hamza Kazi Nov 09 '12 at 17:06
As was already pointed out, this is pretty vulnerable to `array('keyword' => 'anything; DROP TABLE table1; -- ')` kind of sql injection attack. – Patrick Dougall Aug 24 '16 at 21:34
as Patrick Dougall correctly mentioned, this reply is awesome to get the idea, but bear in mind, this is idea, do not ever put this code in the production enviroment. at very bare least do the mysqli real escape, but better, construct the query with :params and do it with PDO. than for simplicity you can bind all variables, do `$variable = isset($_POST["variable"]) ? $_POST["variable"] : "";` and finally exec. – Gall Annonim Sep 06 '17 at 08:06

Kickstart · Answer 2 · 2012-11-10T13:26:05.200

I would suggest using several different queries (one for each combination of search parameters), using php to either decide which to use or dynamically build one with php.

Note that using a like starting with a % is likely to be quite slow.

If you really wanted to do it as one statement (and I don't recommend it) then:-

SELECT * 
FROM table1 
WHERE (Name LIKE '%$keyword%' OR ZipCode LIKE '%$keyword%' OR '$keyword' = '') 
AND (Category = '$category' OR '$category' = '' )
AND (Country = '$country' OR '$country' = '' )
LIMIT $start, $limit

Or to dynamically build it up

$ConditionArray = array();
if ($keyword != '') $ConditionArray[] = "(Name LIKE '%$keyword%' OR ZipCode LIKE '%$keyword%' OR '$keyword' = '')";
if ($category != '') $ConditionArray[] = "Category = '$category'";
if ($country != '') $ConditionArray[] = "Country = '$country'";

if (count($ConditionArray) > 0)
{
    $query = "
    SELECT *
    FROM table 1
    WHERE ".implode(' AND ', $ConditionArray);
}

You're right but for this particular task I am not worried about that. I think I should try making several queries but is this not possible with only one query using OR/AND ? — Hamza Kazi, Nov 09 '12 at 16:59
Hey thanks for the suggestions. I was able to achieve the results a bit differently but yes i decided to make my queries dynamically and not just one single query because that is just confusing. Thanks again :) — Hamza Kazi, Nov 13 '12 at 18:26

Query for PHP/MySql AND/OR for an advanced search

2 Answers2

Linked