Search form query with multiple values - PHP / MYSQL

Question

I'm having a little trouble with a search form I've been creating the functionality for. I basically want a form (on whatever page) to go to this page then list the relevant rows from my database. My problem is that the form has both a text field and a select field (for name and categories) and I've been unable to create the functionality for having these two values search the database together.

So heres what I want to happen: When you only type in the name and not the category, it will display from just the name, vise versa for the category and no name; then when both together it only displays rows with both of them in.

Heres what I have so far:

// 2. Create variables to store values
if(!$_GET['search-category'] == "") {
    $searchName = $_GET['search-name'];
}

if(!$_GET['search-category'] == "select-your-category") {
    $searchCat = $_GET['search-category'];
}



// 2. Create the query for the stored value. Matching it against the name, summary and sub type of my item.
$mainSearch = "SELECT attraction.*, type.type_name, sub_type.sub_type_name ";
$mainSearch .= "FROM attraction ";
$mainSearch .= "INNER JOIN sub_type ON attraction.sub_type = sub_type.sub_type_id ";
$mainSearch .= "INNER JOIN type ON attraction.type = type.type_id ";
$mainSearch .= "WHERE attraction.name LIKE '%" . $searchName . "%' AND (sub_type.sub_type_name LIKE '%" . $searchCat . "%' )";
$mainSearch .= "ORDER BY sub_type_name ASC";

// 2. run query
$result2 = $con->query($mainSearch);
if (!$result2) {
    die('Query error: ' . mysqli_error($result2));
}

Rohit · Accepted Answer · 2013-12-16 11:46:54Z

2

I'd refactor the code to something like -

foreach( $_GET['filters'] as $fname => $fval ) {

    if( !$fval ) continue;

    $where[] = "$fname LIKE '%{$fval}%'";
}

You need to include only those inputs that are non-empty in the query. Also you will need to address security issues like escaping inputs etc.

edited Dec 16, 2013 at 11:46

Rohit

4013 silver badges15 bronze badges

answered Dec 16, 2013 at 11:36

Ashwin Date

3011 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luke Whitehouse Over a year ago

Hi there, I don't completely understand how this works. Would you be able to explain how I'd refactor my code to get this working?

Jigar · Accepted Answer · 2013-12-16 11:33:08Z

0

What you can do is that declare a variable called $search_condition and based on whether $searchName or $searchCat is null or not assign value to $search_condition

For e.g.

if (isset($searchName ) || !is_empty($searchName ))
{
   $search_condition = "WHERE attraction.name LIKE '%" . $searchName;
}
if (isset($searchCat ) || !is_empty($searchCat ))
{
   $search_condition = "sub_type.sub_type_name LIKE '%" . $searchCat . "%'";
}
if ((isset($searchName ) || !is_empty($searchName )) && (isset($searchCat ) || !is_empty($searchCat )))
{
   $search_condition = "WHERE attraction.name LIKE '%" . $searchName . "%' AND (sub_type.sub_type_name LIKE '%" . $searchCat . "%' )";
}

Hope this might help you

Thanks

answered Dec 16, 2013 at 11:33

Jigar

3004 silver badges14 bronze badges

Comments

Strawberry · Accepted Answer · 2013-12-16 11:51:53Z

0

This is a comment, but I want to take advantage of formatting options...

You know, you can rewrite that this way...

// 2. Create the query for the stored value. Matching it against the name, summary and sub type of my item.

$mainSearch = "
SELECT a.*
     , t.type_name
     , s.sub_type_name 
  FROM attraction a 
  JOIN sub_type s
    ON a.sub_type = s.sub_type_id 
  JOIN type t
    ON a.type = t.type_id 
 WHERE a.name LIKE '%$searchName%' 
   AND s.sub_type_name LIKE '%$searchCat%' 
 ORDER 
    BY s.sub_type_name ASC;
    ";

answered Dec 16, 2013 at 11:51

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Comments

Mureinik · Accepted Answer · 2013-12-16 17:02:15Z

0

You can just check that the relevant values aren't empty:

// 2. Create the query for the stored value. 
// Matching it against the name, summary and sub type of my item.
$mainSearch = "SELECT attraction.*, type.type_name, sub_type.sub_type_name ";
$mainSearch .= "FROM attraction ";
$mainSearch .= "INNER JOIN sub_type ON attraction.sub_type = sub_type.sub_type_id ";
$mainSearch .= "INNER JOIN type ON attraction.type = type.type_id ";
$mainSearch .= "WHERE ";
if ($searchName) {
    $mainSearch .= "attraction.name LIKE '%" . $searchName . "%'";
    if ($searchCat) {
        $mainSearch .= " AND ";
    }
}
if ($searchCat) {
    $mainSearch .= "sub_type.sub_type_name LIKE '%" . $searchCat . "%'"
}

$mainSearch .= "ORDER BY sub_type_name ASC";

// Double check that at least one of the search criteria is filled:
if (!$searchName && !$searchCat) {
    die("Must supply either name search or category search");
}

edited Dec 16, 2013 at 17:02

answered Dec 16, 2013 at 11:23

Mureinik

316k54 gold badges400 silver badges405 bronze badges

2 Comments

Luke Whitehouse Over a year ago

Hi there, been getting this into my code and it seems to be working. However, there is still the problem that I now get an error when I don't fill in either or both of the inputs / select elements. How would I create an error message to either stop the form being submitted or to tell the site not to include them in the URI if they are blank i.e. ?search-name=&search-category=

Mureinik Over a year ago

I amended my answer to add this safety check

Collectives™ on Stack Overflow

Search form query with multiple values - PHP / MYSQL

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related