user2519032 user2519032 - 7 months ago 22
SQL Question

PHP select filter

I have a page with list of persons.

Now I want to filter them with a drop down select.

Here's my code:

<main>
<div class="wrapper">
<div class="team-list">
<h3><?php echo $teamTitle; ?></h3>
<div class="filter">
<label for="filter"><?php echo $specialtiesTitle; ?></label>
<form action="" method="post">
<div class="form-item">
<select name="specialties">
<?php
$query = "SELECT * FROM specialties";
$result = mysqli_query($connection, $query);

echo '<option selected="selected" value="All">'. $filterTitle . '</option>';

while($row = mysqli_fetch_assoc($result)) {
$id = $row['id'];
$title = $row['title_'.$lang];

echo '<option value="' . $id . '">' . $title . '</option>';
}
?>
</select>
</div>

<div class="form-item">
<input class="form-submit" type="submit" name="submit" value="<?php echo $filterSubmit; ?>">
</div>
</form>
</div>

<div class="content">
<?php
$query = "SELECT * FROM team";
$result = mysqli_query($connection, $query);

while($row = mysqli_fetch_assoc($result)) {
$id = $row['id'];
$image = $row['image'];
$title = $row['title_'.$lang];

echo '<div class="row">';
echo '<div class="image"><img src="/uploads/' . $image . '"></div>';
echo '<a class="title" href="/team-view?id=' . $id . '">' . $title . '</a>';
echo '<a class="more" href="/team-view?id=' . $id . '">' . $teamMore . '</a>';
echo '</div>';
}
?>
</div>
</div>
</div>
</main>


As you can see from the code the first part has the div filter that receives select from the database.

This line:
echo '<option selected="selected" value="All">'. $filterTitle . '</option>';


Is a additional option with value "All" and the other options are getting from the "specialties" table.

The second part is the content that pulls from the "team" table.
I'm relating the categories from "Specialties" table with the "Team" table.

In the Admin area of my Custom CMS, everything is working and when I change the "Specialties" category, it saves successfully in the database.

Right now the page displays all the fields, but I don't have functionality of filtering.

How can I filter the content?

I know that I have to use:

if(isset($_POST['submit'])) {
//the code inside
}


but what query should I use for such filtering?

EDIT:
By filtering I mean, when I have the list content and from top I have a select drop down and search button. So if I select let's say the first value from the options and click on submit, it should display only the content that has the first category value inside.

In the table "Team" I have many fields and these are the connection:

id
specialties_bg
specialties_en


This is the table "Specialties"

id
title_bg
title_en


Title_bg and title_en has all the options values and id is the connection.

The website is multilingual and that's why I have two fields for different languages.

Answer

Check the below code. If the form is submitted add a condition to the query.

$query = "SELECT * FROM team";
// declare the variable
// check if the form is submitted and have a valid search value
if(isset($_POST['submit']) && (trim($_POST['specialties'])!='') && (trim($_POST['specialties'])!='ALL')) {
   //add the condition
   $query.= " WHERE specialties_en='".trim($_POST['specialties'])."'";
 }

You can modify condition based on the languages with OR condition.

Always check the user input for sql injections.

Comments