Heres a pic of my database table

Screen_Shot_2019-11-16_at_2_35_54_AM.png

Im trying to create a search page with drop down selectors to filter through each of the last 8 columns. I want the the drop down selectors to be able to select multiple entries (I have this working already) I also want them to preload values from data already entered into my table columns. (I also got this working with the help of tutorials... although I admit I don't fully understand how this part works)

Using these tutorials i've created a php page that contains 8 drop down selectors that automatically pull values from their respective columns. Id like to be able to filter results from my table using either all (or some) of these column filters. For Example... Say I want to show all entries that fall under Genre=Metal, AND KeySig=E minor, AND Tempo=Fast... I might use a mysql command like mysql> SELECT id, NameUrl, Genre, KeySig, TimeSig, Tempo, Tuning, EntType, Recording, RecYear FROM test_table WHERE Genre = 'Metal' AND KeySig = 'E minor' AND Tempo = 'Fast';

Essentially i'm trying to do the same thing via a php webpage. With the code I have right now only my first drop down selector "Genre" actually filters through anything. The rest of the filters are just there.. they're not set up to do anything yet. I need help pulling $_POST requests from my remaining drop downs and coming up with code that will filter through my columns using multiple variables via the AND operator.

I hope this makes sense... Im not much of a computer guy.. more of a musician. Building this as a tool to help me out with my writing workflow. Hope someone can help - Thanks

DBController.php

     <?php
 class DBController {
     private $host = "localhost";
     private $user = "root";
     private $password = "password";
     private $database = "test";
     private $conn;

         function __construct() {
         $this->conn = $this->connectDB();
          } 
     function connectDB() {
         $conn = mysqli_connect($this->host,$this->user,$this->password,$this->database);
         return $conn;
     }
         function runQuery($query) {
                 $result = mysqli_query($this->conn,$query);
                 while($row=mysqli_fetch_assoc($result)) {
                 $resultset[] = $row;
                 }      
                 if(!empty($resultset))
                 return $resultset;
     }
 }
 ?>

testsearch.php

        <?php
 include 'DBController.php';
 $db_handle = new DBController();
 $GenreResult = $db_handle->runQuery("SELECT DISTINCT Genre FROM test_table ORDER BY Genre ASC");
 $TempoResult = $db_handle->runQuery("SELECT DISTINCT Tempo FROM test_table ORDER BY Tempo ASC");
 $KeySigResult = $db_handle->runQuery("SELECT DISTINCT KeySig FROM test_table ORDER BY KeySig ASC");
 $TimeSigResult = $db_handle->runQuery("SELECT DISTINCT TimeSig FROM test_table ORDER BY TimeSig ASC");
 $TuningResult = $db_handle->runQuery("SELECT DISTINCT Tuning FROM test_table ORDER BY Tuning ASC");
 $EntTypeResult = $db_handle->runQuery("SELECT DISTINCT EntType FROM test_table ORDER BY EntType ASC");
 $RecordingResult = $db_handle->runQuery("SELECT DISTINCT Recording FROM test_table ORDER BY Recording ASC");
 $RecYearResult = $db_handle->runQuery("SELECT DISTINCT RecYear FROM test_table ORDER BY RecYear ASC");
 ?>
 <html>
 <head>
 <link href="style.css" type="text/css" rel="stylesheet" />
 <title>Riff Bank - Search & Upload</title>
 </head>
 <body>
     <h2>Riff Bank - Search & Upload</h2>
     <form method="POST" name="Genre" action="testsearch.php">
         <div id="demo-grid">
             <div class="search-box">
                 <select id="Place" name="Genre[]" multiple="multiple">
                     <option value="0" selected="selected">Select Genre</option>
                     <form method="POST" name="search" action="testsearch.php">
                         <?php
                         if (! empty($GenreResult)) {
                             foreach ($GenreResult as $key => $value) {
                                 echo '<option value="' . $GenreResult[$key]['Genre'] . '">' . $GenreResult[$key]['Genre'] . '</option>';
                             }
                         }
                         ?>
                 </select><br> <br>
                 <form method="POST" name="search" action="testsearch.php">
         <div id="demo-grid">
             <div class="search-box">
                 <select id="Place" name="KeySig[]" multiple="multiple">
                     <option value="0" selected="selected">Select Key</option>
                     <form method="POST" name="search" action="testsearch.php">
                         <?php
                         if (! empty($KeySigResult)) {
                             foreach ($KeySigResult as $key => $value) {
                                 echo '<option value="' . $KeySigResult[$key]['Tempo'] . '">' . $KeySigResult[$key]['KeySig'] . '</option>';
                             }
                         }
                         ?>
                 </select><br> <br>
                 <form method="POST" name="search" action="testsearch.php">
         <div id="demo-grid">
             <div class="search-box">
                 <select id="Place" name="TimeSig[]" multiple="multiple">
                     <option value="0" selected="selected">Select TIme Signature</option>
                     <form method="POST" name="search" action="testsearch.php">
                         <?php
                         if (! empty($TimeSigResult)) {
                             foreach ($TimeSigResult as $key => $value) {
                                 echo '<option value="' . $TimeSigResult[$key]['TimeSig'] . '">' . $TimeSigResult[$key]['TimeSig'] . '</option>';
                             }
                         }
                         ?>
                 </select><br> <br>
                 <form method="POST" name="search" action="index.php">
         <div id="demo-grid">
             <div class="search-box">
                 <select id="Place" name="Tempo[]" multiple="multiple">
                     <option value="0" selected="selected">Select Tempo</option>
                     <form method="POST" name="search" action="index.php">
                         <?php
                         if (! empty($TempoResult)) {
                             foreach ($TempoResult as $key => $value) {
                                 echo '<option value="' . $TempoResult[$key]['Tempo'] . '">' . $TempoResult[$key]['Tempo'] . '</option>';
                             }
                         }
                         ?>
                 </select><br> <br>
                 <form method="POST" name="search" action="testsearch.php">
         <div id="demo-grid">
             <div class="search-box">
                 <select id="Place" name="Tuning[]" multiple="multiple">
                     <option value="0" selected="selected">Select Tuning</option>
                     <form method="POST" name="search" action="testsearch.php">
                         <?php
                         if (! empty($TuningResult)) {
                             foreach ($TuningResult as $key => $value) {
                                 echo '<option value="' . $TuningResult[$key]['Tuning'] . '">' . $TuningResult[$key]['Tuning'] . '</option>';
                             }
                         }
                         ?>
                 </select><br> <br>
                 <form method="POST" name="search" action="testsearch.php">
         <div id="demo-grid">
             <div class="search-box">
                 <select id="Place" name="EntType[]" multiple="multiple">
                     <option value="0" selected="selected">Select Entry Type</option>
                     <form method="POST" name="search" action="testsearch.php">
                         <?php
                         if (! empty($EntTypeResult)) {
                             foreach ($EntTypeResult as $key => $value) {
                                 echo '<option value="' . $EntTypeResult[$key]['EntType'] . '">' . $EntTypeResult[$key]['EntType'] . '</option>';
                             }
                         }
                         ?>
                 </select><br> <br>
                 <form method="POST" name="search" action="testsearch.php">
         <div id="demo-grid">
             <div class="search-box">
                 <select id="Place" name="Recording[]" multiple="multiple">
                     <option value="0" selected="selected">Select Recording Type</option>
                     <form method="POST" name="search" action="testsearch.php">
                         <?php
                         if (! empty($RecordingResult)) {
                             foreach ($RecordingResult as $key => $value) {
                                 echo '<option value="' . $RecordingResult[$key]['Recording'] . '">' . $RecordingResult[$key]['Recording'] . '</option>';
                             }
                         }
                         ?>
                 </select><br> <br>
                 <form method="POST" name="search" action="index.php">
         <div id="demo-grid">
             <div class="search-box">
                 <select id="Place" name="RecYear[]" multiple="multiple">
                     <option value="0" selected="selected">Select Year</option>
                     <form method="POST" name="search" action="index.php">
                         <?php
                         if (! empty($RecYearResult)) {
                             foreach ($RecYearResult as $key => $value) {
                                 echo '<option value="' . $RecYearResult[$key]['RecYear'] . '">' . $RecYearResult[$key]['RecYear'] . '</option>';
                             }
                         }
                         ?>
                 </select><br> <br>
                 <button id="Filter">Search</button>
             </div>
             <?php
                 if (! empty($_POST['Genre'])) {
                     ?>
                     <table cellpadding="10" cellspacing="1">

                 <thead>
                     <tr>
                         <th><strong>id</strong></th>
                         <th><strong>Name</strong></th>
                         <th><strong>Genre</strong></th>
                         <th><strong>Key</strong></th>
                         <th><strong>Time Sig</strong></th>
                         <th><strong>Tempo</strong></th>
                         <th><strong>Tuning</strong></th>
                         <th><strong>Type</strong></th>
                         <th><strong>Recording</strong></th>
                         <th><strong>Year</strong></th>
                     </tr>
                 </thead>
                 <tbody>
                 <?php
                     $query = "SELECT * from test_table";
                     $i = 0;
                     $selectedOptionCount = count($_POST['Genre']);
                     $selectedOption = "";
                     while ($i < $selectedOptionCount) {
                         $selectedOption = $selectedOption . "'" . $_POST['Genre'][$i] . "'";
                         if ($i < $selectedOptionCount - 1) {
                             $selectedOption = $selectedOption . ", ";
                         }

                         $i ++;
                     }
                     $query = $query . " WHERE Genre 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]['id']; ?></div></td>
                         <td><div class="col" id="user_data_2"><?php echo $result[$key]['NameUrl']; ?> </div></td>
                         <td><div class="col" id="user_data_3"><?php echo $result[$key]['Genre']; ?> </div></td>
                         <td><div class="col" id="user_data_4"><?php echo $result[$key]['KeySig']; ?> </div></td>
                         <td><div class="col" id="user_data_5"><?php echo $result[$key]['TimeSig']; ?> </div></td>
                         <td><div class="col" id="user_data_6"><?php echo $result[$key]['Tempo']; ?> </div></td>
                         <td><div class="col" id="user_data_7"><?php echo $result[$key]['Tuning']; ?> </div></td>
                         <td><div class="col" id="user_data_8"><?php echo $result[$key]['EntType']; ?> </div></td>
                         <td><div class="col" id="user_data_9"><?php echo $result[$key]['Recording']; ?> </div></td>
                         <td><div class="col" id="user_data_10"><?php echo $result[$key]['RecYear']; ?> </div></td>
                     </tr>
                 <?php
                     }
                     ?>

                 </tbody>
             </table>
             <?php
                 }
                 ?>  
         </div>
     </form>
 </body>
 </html>
             </div>
     </form>
 </body>
 </html>

Screen_Shot_2019-11-16_at_2_32_45_AM.png

Screen_Shot_2019-11-16_at_2_33_17_AM.png

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.