使用多个值搜索表单查询 - 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));
}

Answer 1

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");
}