Reputation: 425
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
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
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
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
Reputation: 1062
Use the term AND
or OR
in your query after WHERE
WHERE status = '$search_term' AND course = '$something'
Upvotes: 0