php - How can I create a dropdown menu to search my MySQL database?

Question

I have to create a search form to retrieve data from a MySQL database.

I used this to create a dropdown selection of one category. It does work, and the menu is clearly linked to the database, because all of the categories are displayed.

But now I can't figure out how to get the selection of the dropdown menu implemented into a query.

<form class="form-horizontal" action="search_keyword.php">
 Analysegegenstand
  <select>
      <label class="col-md-4 control-label" for="searchinput">Analysegegenstand</label>
    <option disabled selected id="searchinput" name="type_select" type="select">Analysegegenstand w?hlen</option>
    <?php
        include "db_connect.php";
        
        $records = mysqli_query($mysqli, "SELECT Analysegegenstand From Analysegegenstand"); 

        while($data = mysqli_fetch_array($records))
        {
            echo "<option value='". $data['Analysegegenstand'] ."'>" .$data['Analysegegenstand'] ."</option>"; 
        }   
    ?>  
  </select>
  

</fieldset>
</form>

Answer 1

First, you need to fix your <select> and <option> elements. The select tag should have the name attribute. The <option> element doesn't have a name or type attribute. You also have a closing </fieldset> which you never open. Your <label> tag is also pointing to an invalid element. To submit the form, you need to have a submit button.

When the whole HTML is fixed it should look something like this:

<form class="form-horizontal" action="search_keyword.php">
    Analysegegenstand
    <label class="col-md-4 control-label" for="searchinput">Analysegegenstand</label>
    <select name="type_select" id="searchinput">
        <option disabled selected>Analysegegenstand w?hlen</option>
        <?php
        include "db_connect.php";

        $records = mysqli_query($mysqli, "SELECT Analysegegenstand From Analysegegenstand");

        while ($data = mysqli_fetch_array($records)) {
            echo "<option value='" . htmlspecialchars($data['Analysegegenstand']) . "'>" . htmlspecialchars($data['Analysegegenstand']) . "</option>";
        }
        ?>
    </select>

    <input type="submit" value="Search">
</form>

When the form gets submitted the value of your <select> will be available as $_GET['type_select'] because your <select> has a name="type_select".

All you need to do is to construct an SQL query using a prepared statement and execute the query with the selected value. The value you get from the form will be used in the WHERE query to compare it against the column you are trying to search in. The field you want to select need to be listed in the SELECT query too. After preparing, the value will be bound to the statement and then execute on the MySQL database server. It is then up to you to handle the results however you want to.

In your search_keyword.php file:

<?php

if (isset($_GET['type_select'])) {
    include "db_connect.php";

    $stmt = $mysqli->prepare("SELECT searched_fields FROM information WHERE searchable=?");
    $stmt->bind_param('s', $_GET['type_select']);
    $stmt->execute();
    $result = $stmt->get_result();
    foreach ($result as $row) {
        printf("Found: %s
", $row['searched_fields']);
    }
}

Tips:

• You can use LIKE instead of = in SQL to allow users to search using wildcards % and _.
• It is much better to use PDO than mysqli. It's easier and offers more functionality.
• Don't mix HTML and PHP. Keep the database logic separate from presentation logic. Use PHP only to display the data within HTML, but prepare the data in a separate PHP file.
• Use htmlspecialchars() whenever displaying any data within HTML context.