PHP select filter

Question

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 1

Fetching Specific Data From Database :

In order to fetch all data from a table you can do that simply by using SELECT Query in MySQL as :

SELECT * FROM table;

But as now you want to filter the whole data and just get something specific from your database so for that you must should have something unique in order to differentiate the expected data from the whole rest of the data.

TWO WAYS TO DO THAT :

1) Using An Extra Field (Column) In Your Database Table :

You can do that by adding an extra field (column) to your table structure like something which will contain values based on the records in order to put them in a collective based group.

For Instance :
I have a table with alot of records of players now I add a column namely as sport so now I have something unique in order to filter the players.So I can use following SQL SELECT Query to get all those players as:

SELECT * FROM players WHERE sport="sport_name";

2) Filtering Data Through PHP :

If you don't want to get the filtered data from the Database,So you can do the same process in PHP also as:

For Instance :

You already fetched the whole data from the players table and now you want to filter it so you can filter it like this way as :

<?php
//Assuming that $results contains the whole fetched data from table
for ($i=0;$i<=count($results);$i++) {
   if ($results[$i]['sport'] == "cricket") { 
      // Can target any sport located in your database
      //You can pretty much do anything you want..!
      echo $result[$i]['player_name'];
   }
}
?>

Answer 2

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.