Fetching Data From Mysql Database to Html Dropdown List

Multi-Select Dropdown Filter in PHP with Database Search

by Vincy. Last modified on May 19th, 2021.

Filters are used to narrow down the results based on particular criteria. We have already created a PHP filter for a list page to shortlist the database results based on the selected filter options.

Search filter with a combo field will allow multi-select. So, we can create a search to filter data based on the bunch of options selected by the user.

View Demo

I have created PHP search filter form with a multi-select box showing the countries list. I have a database table tbl_user to store the user's details like name, gender, country and more.

On submitting the filter by selecting the countries option, the database results will be fetched and listed based on the selected options. I have imploded the array of selected country names and pass them to the query IN clause to fetch the database results.

If you are checking for country state dependent dropdown check the linked article or you may also check the three levels of dependent dropdown lists.

The following screenshot shows the result of the multi-select dropdown filter using PHP

Database Script

The following script is used to create the database structure and dump the data required for this example. Before running this example on your local machine, import this script via your database client.

CREATE TABLE `tbl_user` (   `id` int(8) NOT NULL,   `Name` varchar(255) NOT NULL,   `Gender` varchar(10) NOT NULL,   `Country` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  INSERT INTO `tbl_user` (`id`, `Name`, `Gender`, `Country`) VALUES (1, 'Jack', 'Female', 'Canada'), (2, 'Jane', 'Female', 'Mexico'), (3, 'Emmanuel', 'Male', 'USA'), (4, 'Franck', 'Male', 'USA'), (5, 'Kevin Tomas', 'Male', 'Haiti'), (6, 'Tim Dillon', 'Male', 'Haiti');  ALTER TABLE `tbl_user`   ADD PRIMARY KEY (`id`);  ALTER TABLE `tbl_user`   MODIFY `id` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7; COMMIT;

HTML Search Filter with Multi-Select dropdown

I have created a HTML form with a multi-select dropdown. In this dropdown, I have listed the distinct country names from the tbl_user database.

The user can select multiple countries from this dropdown list. On submitting the filter options, it will be posted to the PHP to process the MySQL data fetch based on the selected values. After fetching the results from the database, it will be listed below the filter.

<?php include 'DBController.php'; $db_handle = new DBController(); $countryResult = $db_handle->runQuery("SELECT DISTINCT Country FROM tbl_user ORDER BY Country ASC"); ?> <div class="search-box">     <select id="Place" name="country[]" multiple="multiple">         <option value="0" selected="selected">Select Country</option>         <?php             if (! empty($countryResult)) {                  foreach ($countryResult as $key => $value) {                      echo '<option value="' . $countryResult[$key]['Country'] . '">' . $countryResult[$key]['Country'] . '</option>';                  }              }         ?>     </select>     <button id="Filter">Search</button> </div>        

MySQL SELECT Query to Filter Database Results Based on Selected Countries

After submitting the filter options to the PHP, the array of selected countries are iterated in a loop. The loop will create the imploded string from the selected countries array.

This string value will be within the IN clause of the SELECTquery to fetch data based on the multiple values. Below code shows how to fetch MySQL result based on multiple countries.

<?php if (! empty($_POST['country'])) {     ?> <table cellpadding="10" cellspacing="1">      <thead>         <tr>             <th><strong>Name</strong></th>             <th><strong>Gender</strong></th>             <th><strong>Country</strong></th>         </tr>     </thead>     <tbody>         <?php     $query = "SELECT * from tbl_user";     $i = 0;     $selectedOptionCount = count($_POST['country']);     $selectedOption = "";     while ($i < $selectedOptionCount) {         $selectedOption = $selectedOption . "'" . $_POST['country'][$i] . "'";         if ($i < $selectedOptionCount - 1) {             $selectedOption = $selectedOption . ", ";         }                  $i ++;     }     $query = $query . " WHERE country in (" . $selectedOption . ")";          $result = $db_handle->runQuery($query); } if (! empty($result)) {     foreach ($result as $key => $value) {         ?>         <tr>             <td><div class="col" id="user_data_1">                     <?php echo $result[$key]['Name']; ?>                 </div></td>             <td><div class="col" id="user_data_2">                     <?php echo $result[$key]['Gender']; ?>                 </div></td>             <td><div class="col" id="user_data_3">                     <?php echo $result[$key]['Country']; ?>                 </div></td>         </tr>         <?php     }     ?>      </tbody> </table> <?php 	} ?>        

View DemoDownload

↑ Back to Top

Fetching Data From Mysql Database to Html Dropdown List

Source: https://phppot.com/php/multi-select-dropdown-filter-in-php-with-database-search/

Share this post