elseif搜索查询不起作用

Question

I have a search form that has 3 elements keywords, Industry ( Dropdown ) & location

I am trying to create search function that will query the DB if all 3 elements are selected or based on individual values OR a combination of any elements having $_POST data submitted

Its just not working

My code for the queries is below

        if(isset($_POST['keywords']))
    {
    $keywords = $_POST['keywords'];
    $viewq = mysqli_query($con,"SELECT * FROM `listings` WHERE (`title` LIKE '%$keywords%' OR `description` LIKE '%$keywords%') LIMIT 0,50");
    $resultsfor = $_POST['keywords'];
    }
    elseif(isset($_POST['keywords']) && isset($_POST['location']))
    {
    $keywords = $_POST['keywords'];
    $location = $_POST['location'];
    $viewq = mysqli_query($con,"SELECT * FROM `listings` WHERE (`title` LIKE '%$keywords%' OR `description` LIKE '%$keywords%') AND (`location` LIKE '%$location%') LIMIT 0,50");
    $resultsfor = $keywords .' jobs in '.$location;
    }
    elseif(isset($_POST['keywords']) && isset($_POST['location']) && !empty($_POST['industry']))
    {
    $keywords = $_POST['keywords'];
    $location = $_POST['location'];
    $catno = $_POST['industry'];
    $viewq = mysqli_query($con,"SELECT * FROM `listings` WHERE (`title` LIKE '%$keywords%' OR `description` LIKE '%$keywords%') AND (`location` LIKE '%$location%') AND (`catno` = '$catno') LIMIT 0,50");
    $resultsfor = $keywords .' jobs in '.$location;
    }
    elseif(isset($_POST['industry']) && empty($_POST['location']))
    {
    $industry = $_POST['industry'];
    $viewq = mysqli_query($con,"SELECT * FROM `listings` WHERE `catno` = '$industry' LIMIT 0,50");
    $resultsfor = $_POST['industry']. ' jobs';
    }
    elseif(isset($_POST['industry'])&& isset($_POST['location']))
    {
    $industry = $_POST['industry'];
    $location = $_POST['location'];
    $viewq = mysqli_query($con,"SELECT * FROM `listings` WHERE (`catno` = '$industry') AND (`location` LIKE '%$location%') LIMIT 0,50");
    $resultsfor = $_POST['industry']. ' jobs in '.$location;
    }
    elseif(isset($_POST['location']))
    {
    $location = $_POST['location'];
    $viewq = mysqli_query($con,"SELECT * FROM `listings` WHERE (`location` LIKE '%$location%') LIMIT 0,50");
    $resultsfor = $_POST['location']. ' jobs';
    }

    $view = mysqli_fetch_assoc($viewq);

Would really appreciate some help, Ive spent loads of time on this Thanks in advance

** It does pull data although not correct, If i search for warehouse ( leave industry dropdown blank ) but set location to Leicester it still queries ALL results not Leicester If I select an Industry from the dropdown menu and leave other fields blank, I get all jobs in all categories not just the category selected

Getting myself confused now

Answer 1

I imagine your issues are due to the complicated way you have structured your if-else conditions. A much simpler way would be to do it as follows...

// Get the input variables
$keywords = (isset($_POST['keywords']) && strlen($_POST['keywords']) > 0) ? $_POST['keywords'] : null;
$location = (isset($_POST['location']) && strlen($_POST['location']) > 0) ? $_POST['location'] : null;
$catno = (isset($_POST['industry']) && strlen($_POST['industry']) > 0) ? $_POST['industry'] : null;

$whereUsed = false;
$whereString = "";
$resultsfor = "";

// Add to the WHERE clause if keywords exists.
if ($keywords !== null) {
    if (! $whereUsed) {
        $whereString .= 'WHERE ';
        $whereUsed = true;
    } else {
        $whereString .= ' AND ';
    }
    $whereString .= "(title LIKE '%{$keywords}%' OR description LIKE '%{$description}%')";
    if ($catno === null) {
        $resultsfor .= $keywords;
    }
}
// Add to the WHERE clause if catno exists.
if ($catno !== null) {
    if (! $whereUsed) {
        $whereString .= 'WHERE ';
        $whereUsed = true;
    } else {
        $whereString .= ' AND ';
    }
    $whereString .= "(catno = '{$catno}')";
    $resultsfor .= $catno;
}
// Add to the WHERE clause if location exists.
if ($location !== null) {
    if (! $whereUsed) {
        $whereString .= 'WHERE ';
        $whereUsed = true;
    } else {
        $whereString .= ' AND ';
    }
    $whereString .= "(location LIKE '%{$location}%')";
    if ($catno === null && $keywords === null) {
        $resultsfor = "{$location} jobs";
    } else {
        $resultsfor .= " jobs in {$location}";
    }
}

// Build the SQL query using the WHERE clauses we've built up
$sqlQuery = "SELECT * FROM listings {$whereString} LIMIT 0, 50";

// Execute the query and fetch the response
$viewq = mysqli_query($con, $sqlQuery);
$view = mysqli_fetch_assoc($viewq);

There is no need for the if-else-if format you have in your original code. You are simply adding WHERE clauses based on whether or not a variable is set... so you should do exactly that.

Please note that in your code, and in my example the SQL queries are vulnerable to SQL injection. I strongly suggest looking into prepared statements.