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:

Screen_Shot_2019-11-13_at_8_08_13_PM.png

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----

Screen_Shot_2019-11-13_at_8_08_50_PM.png

Image-Current index.php Search Results

Screen_Shot_2019-11-13_at_8_09_31_PM.png

(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!

No responses yet... did you get anywhere with your questions yet?

1) Searching across all columns: to do this you need to build a query containing all the columns you want to search in. You could do this automatically by getting the table info from mysql and create a SQL query you can execute. Can you give an example of what you want to achieve? Another option is to use the "Full text search" method of mysql.

I think the reason you haven't received too many responses is because you're question is basically talking about this sophisticated app that searches various fields in a database with filtering, and the code you provided just connects to the database server, so it's hard to know what exactly you're asking or where you're stuck.

However, I would highly, highly suggest you use a PHP templating engine because having php code intermingled within your HTML code gets really confusing really quickly.

Be a part of the DaniWeb community

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