Really hoping someone can help me out or point me in the right direction. This is my first ever post in a coding forum so I apologize in advance if I’m breaking any forum rules. I already have a bunch of php code sorted from modifying web resources. I need help altering it further to help me accomplish my goals. Below I’ve included my code, along with screenshots of the code executed in a browser and my MySQL database table.
Background: I’m a professional musician but I have extremely basic computer programming knowledge. Nothing formal.. I use google, YouTube, Forums, and Github to help me build custom applications that help my work flow as a musician... So please bare with me. With that being said; My problem: When ever a musical idea comes to me I record it into my iPhone Voice Memo app and name it. Later on when working with a producer I can reference these ideas. However my phone is filled with literally hundreds of these short recordings and I have a difficult time searching through my ideas by name alone.
I want to store my mp3s in a directory on my website, then create a MySQL database table that stores file paths to my MP3s along with searchable “tags” as my table columns. (Ex. Genre - Time Signature - Tempo - Key, and other important categories) Then I want to create a php page that will allow me to search through the database based off these “tags”. To test things out I’ve created a MySQL database on my local Mac machine. All my info is under a single table. Using information found on the web I’ve also created a couple php pages that allow me to search the database. (Web Resources) - PHP Resource Tutorial Used For This Project
My test table currently looks like this:
A lot of the values are the same now, but they'll change down the line.
Here are the 5 main goals I’m trying to accomplish. I’ve started most of it but I desperately need some help. I’m not even sure if I’m approaching things in the best way. If anyone can help me edit my code to achieve any of these goals I’d be super thankful!
(ONE) At the very top of the page I’d like to create a search field that lets me search for a key word across all columns. (Haven’t started)
(TWO) Then I’d like to create a “multi-select” drop down menu for each column in my database. I’d like the final search results to be reflective of all selected search fields. (Ex. Only show me MP3 entries where Genre=Rock, Tempo=Fast, and Key=E minor) Image-Current index.php Search on Browser----
Image-Current index.php Search Results
(THREE) I want the drop down menu to automatically retrieve and show results that are in the database. (Think I got this sorted out from the web tutorial.. don't know if this is the best way though)
(FOUR) I want my results to pull up an embedded MP3(auto play off) along with the other information in its row. Currently the NameUrl column holds the file name on my webserver. I want to append a file path in my php page and then insert retrieved search results from the NameUrl column along with some other code to embed the MP3 into the page. (Ex. [code to embed a file.. src??] -> Filepath=www.website.com/mp3_folder/ -> NameUrl column (metal_riff_01.mp3)) Not sure what the code is.. could use some help accomplishing this.
(FIVE) Finally, at the bottom of the page I’d like to create an upload feature that will allow me to upload an audio file to my webserver and store related data in my database. I’d like to also do this with predefined drop down selection menus to choose column values. I’m wondering if I’ll have to create extra tables in my database specifying all possible options for each column. (Haven’t started)
I’d like to avoid java script! Only HTML and php. Any help is greatly appreciated! Even if it’s just one of these points. Thank you very much!
CODE
DBController.php
<?php
class DBController {
private $host = "localhost";
private $user = "user";
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;
}
}
?>index.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");
?> <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="index.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="index.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="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="index.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="index.php">
<?php
if (! empty($KeySigResult)) {
foreach ($KeySigResult as $key => $value) {
echo '<option value="' . $KeySigResult[$key]['Tempo'] . '">' . $KeySigResult[$key]['KeySig'] . '</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]['Type']; ?> </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]['Year']; ?> </div></td>
</tr>
<?php
}
?> </tbody> </table> <?php
}
?> </div> </form> </body> </html>Thank You Very Very Much!
