如何在SQL数据库中基于php的两个参数进行过滤

Question

I have a MySQL database, and the table I need to work with has 9 columns of information. My goal is to be able to filter, based on two arguments. For instance, the table is about students so it has data for first name, last name, id, course they are signed up for, status, occupation age and another 2 fields that are not that important. I need to be able to filter, based on the student's status and/or the course.

So far, I managed to get the php work done, with a form and a select tag, to filter based on status, but I have no idea how to add the second part. The done thing should be able to filter, based on status only, based on course only, or based on the selected status and course. The code looks like this:

if (isset($_POST['filter'])) {
        $search_term = mysqli_real_escape_string($conn, $_POST['filter_status']);
        $q .= " WHERE status = '$search_term'";
    }
    echo $q;
<form method="POST" action="index.php">
    <select name="filter_status" > 
        <option value="confirmed">confirmed</option>
        <option value="declined">declined</option>
        <option value="rejected">rejected</option>
        <option value="pending">pending</option>
        <option value="unconfirmed">unconfirmed</option>
    </select>
    <input type="submit" name="filter">
</form>

This works correctly, I have it a second time for the second criteria, but they don't work together.

Answer 1

Thank you all for your input. It helped nudge me in the right direction. The code that ended up doing what I needed is as follows. It's not very elegant, but it does the job well:

$q = "SELECT *
FROM students";

if (isset($_POST['filter'])) {
    if ($_POST['filter_status'] == null) {
    $search_term2 = mysqli_real_escape_string($conn, $_POST['filter_course']);
    $q .= " WHERE course = '$search_term2'";
} elseif ($_POST['filter_course'] == null) {
    $search_term = mysqli_real_escape_string($conn, $_POST['filter_status']);
    $q .= " WHERE status = '$search_term'";
} else {
    $search_term = mysqli_real_escape_string($conn, $_POST['filter_status']);
    $search_term2 = mysqli_real_escape_string($conn, $_POST['filter_course']);
    $q .= " WHERE status = '$search_term' AND course = '$search_term2'";
}

}

And the form:

<form method="POST" action="index.php">
    <select name="filter_status" > 
        <option value= ""></option>
        <option value="confirmed">confirmed</option>
        <option value="declined">declined</option>
        <option value="rejected">rejected</option>
        <option value="pending">pending</option>
        <option value="unconfirmed">unconfirmed</option>
    </select>

    <select name="filter_course">
    <option value= ""></option> 
        <option value="php">php</option>
        <option value="java">java</option>
    </select>
    <input type="submit" name="filter">
</form>