CODEWITHSUNDEEP
phphtmlmysqlsqlforms
Mihail Ivanchev
Mihail Ivanchev

Reputation: 425

How to filter based on two arguments from php, in a SQL database

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.

Upvotes: 1

Views: 152

Answers (4)

Mihail Ivanchev
Mihail Ivanchev

Reputation: 425

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>

Upvotes: 0

Kevin Yan
Kevin Yan

Reputation: 1236

$filter_status = $_POST['filter_status']; $course = $_POST['course']; $where = 'WHERE 1'; $where .= $filter_status ? " AND status = {$filter_status}" : ''; $where .= $course ? " AND course = {$course}" : '';

Did you mean this? when user select course and filter_status use this two conditions, on the other hand use one of conditions which is being selected.

The WHERE 1 will always be TRUE, so it can be followed by AND statements

Upvotes: 1

user4628565
user4628565

Reputation:

try to change,

$q .= " WHERE status = '$search_term'";

to

$q .= " WHERE CONCAT_WS(',',status,course) like %'$search_term'%";

you can add as many columns after course.

Upvotes: 1

Sayed
Sayed

Reputation: 1062

Use the term AND or OR in your query after WHERE

WHERE status = '$search_term' AND course = '$something'

Upvotes: 0

Related Questions