Heres a pic of my database table
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>
