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

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.

user4628565 · Accepted Answer · 2015-08-03 12:58:37Z

1

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.

answered Aug 3, 2015 at 12:58

user4628565

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

3 Comments

Mihail Ivanchev Over a year ago

this looks great. But I have to have 2 drop down lists with the 2 columns, should the second list also be outputting to $search_term?

Gadawadara Over a year ago

just add another like %'$search_term'%" part e.g. AND like %'$search_term_2'%" for your second list but you can name it like whatever you want

Gadawadara Over a year ago

Or if you want to allow one filter at a time use OR like %'$search_term_2'%"

Kevin Yan · Accepted Answer · 2015-08-03 13:41:20Z

1

$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

edited Aug 3, 2015 at 13:41

answered Aug 3, 2015 at 13:37

Kevin Yan

1,23411 silver badges20 bronze badges

1 Comment

Mihail Ivanchev Over a year ago

Yes, I think so. User must be able to select just one filter, say filter by status, or filter by course, or to be able to select both. I added blank fields to my form, the thing you wrote above should complete the picture. Testing it now. Thanks for the input.

Sayed · Accepted Answer · 2015-08-03 12:54:33Z

0

Use the term AND or OR in your query after WHERE

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

answered Aug 3, 2015 at 12:54

Sayed

1,0623 gold badges12 silver badges27 bronze badges

1 Comment

Mihail Ivanchev Over a year ago

it has to be AND and OR. There has to be an option, where the user can filter both based on status and on course, also, he has to be able to filter based on just one, so it has to be AND and OR. Was thinking, If I have two queries, and the form data is in an if statement, where if one select field is empty, just use data from the second one, if both have data selected, filter like you said, with both queries and AND in the WHERE statement of the query, but I don't know if that will work.

Mihail Ivanchev · Accepted Answer · 2015-08-03 14:35:30Z

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>

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related