Trying to filter the options for a dropdown based on first dropdown selection from same table

Question

So I have a MySQL database I created that creates 2 dropdowns to filter the agents by locations:

Agent | Location
Steve | Florida
Robert| New York
Joe   | New York

Dropdown 1: Location

Dropdown 2: All of the agents in location selected in dropdown 1

I have been trying every Google and Stackoverflow suggestion but nothing is doing what I need. Figured this was kinda of simple. I would appreciate any help. I am getting the data I need but I cannot get it to fill in a dropdown. It creates a dropdown for each option.

<?php
include 'DBController.php';
$db_handle = new DBController();
$element_5Result = $db_handle->runQuery("SELECT DISTINCT element_5 FROM providers ORDER BY element_5 ASC");
?>
<html>
<head>
<link href="style.css" type="text/css" rel="stylesheet" />
</head>
<body>
    <form method="POST" name="search" action="index.php">
        <select id="Place" name="element_5[]" multiple="multiple">
            <option value="0" selected="selected">Select element_5</option>
            <?php
            if (! empty($element_5Result)) {
                foreach ($element_5Result as $key => $value) {
                    echo '<option value="' . $element_5Result[$key]['element_5'] . '">' . $element_5Result[$key]['element_5'] . '</option>';
                }
            }
            ?>
        </select><br> <br>
        <button id="Filter">Search</button>
    </div>

    <?php if (! empty($_POST['element_5'])) { ?>
        <select>
        <?php
        $query = "SELECT * from providers";
        $i = 0;
        $selectedOptionCount = count($_POST['element_5']);
        $selectedOption = "";
        while ($i < $selectedOptionCount) {
            $selectedOption = $selectedOption . "'" . $_POST['element_5'][$i] . "'";
            if ($i < $selectedOptionCount - 1) {
                $selectedOption = $selectedOption . ", ";
            }

            $i ++;
        }
        $query = $query . " WHERE element_5 in (" . $selectedOption . ")";
        $result = $db_handle->runQuery($query);
    }
    if (! empty($result)) {
        foreach ($result as $key => $value) {
             if (! empty($result)) {
                foreach ($result as $key => $value) {
                    echo '<option value="' . $result[$key]['element_1'] . '">' . $result[$key]['element_1'] . '</option>';
                }
             }
        ?>
    </select>
    <?php } ?>
    <?php } ?>
    </form>
</body>
</html>

Can you show us what have you tried so far? Without some of your current code it'll be hard to help you. — Gonras Karols, Sep 13 '22 at 18:13
What is the problem actually? First thing i see is a invalid nested option construction since you generate ` — Jim Panse, Sep 13 '22 at 18:24
I don't know what I need to do to filter the options for the second dropdown based on the first — farmerm3, Sep 13 '22 at 18:31
Your code is open to SQL injection! [Use PDO and parameters](https://stackoverflow.com/a/60496). — Don't Panic, Sep 15 '22 at 10:16

score 0 · Answer 1 · answered Sep 13 '22 at 21:12

Here is a quick example, While i dont have your full code i tried to create an example similar to yours. So please alter my codes according to your field names. If you have trouble implementing it into your codes tell me

FIRST is a query to get your options for the first dropdown and put it into an array

$dropdown1 = mysqli_query($db,"SELECT options FROM dropdown1");
while ($row = mysqli_fetch_assoc($dropdown1)) {
      $dropdown1array[] = $row['options'];
    
    }

SECOND Display the elements in your array for your first dropdown and create your second dropdown

<form method="POST" >
                <select  name="dropdown1[]" multiple="multiple" size="7">
                    <option value="0" >Select dropdown1</option>
                        <?php
                            foreach ($dropdown1array as $value) {
                                echo '<option value="' . $value . '">' . $value . '</option>';    }
                         ?>
                </select>
            <select  name="dropdown2" multiple="multiple" size="7">
                    <option value="0" >Select 2</option>

THIRD

<?php
     
    // Check if form is submitted successfully
    if(isset($_POST["submit"]))
    {
        // Check if any option is selected
        if(isset($_POST["dropdown1"]))
        {
            // Retrieving each selected option and putting it into the second dropdown
            foreach ($_POST['dropdown1'] as $dropdown1){
            
                    echo '<option value="' . $dropdown1 . '">' . $dropdown1 . '</option>';  
        }}
        else{
            echo "Select an option first !!";
    }
}
    
?>

  </select> 
   <input type = 'submit' name = 'submit' value = Submit>    
   </form>

Trying to filter the options for a dropdown based on first dropdown selection from same table

1 Answers1