Dependent dropdown using WHERE clause

Question

I am looking for some help. I need to create a dependent dropdown based on the previous input. Everything works in my code, and it sends and receives data from the MySQL database. When I select a game, I would like only the tournaments associated with that game to be displayed. I want to do it using SQL language and have put ****** where I think I can do it.

In MySQL, I have two tables - one called Games with game_name where I receive the data for the first part. The second table is called tournaments with game_name1 and tournaments columns. Finally, I have another input screen that sends the data over so that game_name = game_name1.

require('db.php');
include("auth.php");
$status = "";
if (isset($_POST['new']) && $_POST['new'] == 1) {
    $trn_date = date("Y-m-d H:i:s");
    $main_game = $_REQUEST['main_game'];
    $main_tournament = $_REQUEST['main_tournament'];
    $start_date = $_REQUEST['start_date'];
    $end_date = $_REQUEST['end_date'];
    $number_of_teams = $_REQUEST['number_of_teams'];
    $venue_region = $_REQUEST['venue_region'];
    $venue_city = $_REQUEST['venue_city'];
    $winner = $_REQUEST['winner'];
    $runner_up = $_REQUEST['runner_up'];
    $prize_pool = $_REQUEST['prize_pool'];
    $winners_prize = $_REQUEST['winners_prize'];
    $winner_odds = $_REQUEST['winner_odds'];
    $submittedby = $_SESSION["username"];
    $ins_query = "insert into tournament_info
        (`trn_date`,`main_game`,`main_tournament`,`start_date`,`end_date`,`number_of_teams`,`venue_region`,`venue_city`,`winner`,`runner_up`,`prize_pool`,`winners_prize`,`winner_odds`,`submittedby`)values
        ('$trn_date','$main_game','$main_tournament','$start_date','$end_date','$number_of_teams','$venue_region','$venue_city','$winner','$runner_up','$prize_pool','$winners_prize','$winner_odds','$submittedby')";
    mysqli_query($con, $ins_query)
    or die(mysql_error());
    $status = "New Record Inserted Successfully.
        </br></br><a href='viewti.php'>View Inserted Record</a>";
}
?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>Insert New Tournament Record</title>
    <link rel="stylesheet" href="style.css"/>
</head>
<body>

<div class="sidenav">
    <p><a href="dashboard">Home</a>
    <p>
    <p><a href="insert">Insert New Game Record</a></p>
    <p><a href="view">View Game Records</a>
    <p>
    <p><a href="insertt">Insert New Tournament Record</a></p>
    <p><a href="viewt">View Tournament Records</a>
    <p>
    <p><a href="insertti">Insert New Tournament Info Record</a></p>
    <p><a href="viewti">View Tournament Info Records</a>
    <p>
    <p><a href="logout">Logout</a></p>
</div>
<div align="center" class="main">
    <p><a href="dashboard.php">Dashboard</a>
        | <a href="viewti.php">View Tournament Info Records</a>
        | <a href="logout.php">Logout</a></p>
    <div>
        <h1>Insert New Tournament Record</h1>
        <form name="form" method="post" action="">
            <input type="hidden" name="new" value="1"/>

            <select name="main_game" style="width: 200px;">
                <option>-- Select Game --</option>
                <?php
                $records = mysqli_query($con, "SELECT game_name From Games");

                while ($row = mysqli_fetch_array($records)) {
                    echo "<option value='".$row['game_name']."'>".$row['game_name']."</option>";  // displaying data in option menu
                }
                ?>
            </select>
            <br><br>
            **<select name="main_tournament" style="width: 200px;">
                <option>-- Select Tournament --</option>
                <?php

                $records1 = mysqli_query($con, "SELECT * From tournaments");

                while ($row = mysqli_fetch_array($records1)) {
                    echo "<option value='".$row['tournament']."'>".$row['tournament']."</option>";  // displaying data in option menu
                }
                ?>
            </select>**
            <br><br>
            <p><label>Start Date</label><br><input type="date" name="start_date" placeholder="Start Date"
                                                   style="width: 200px" required/></p>
            <br>
            <p><label>End Date</label><br><input type="date" name="end_date" placeholder="End Date" style="width: 200px"
                                                 required/></p>
            <p><input type="text" name="number_of_teams" placeholder="Number of Teams" required/></p>
            <br>
            <select name="venue_region" style="width: 200px;">
                <option> --Select Region --</option>
                <option value="Europe">Europe</option>
                <option value="Asia">Asia</option>
                <option value="North America">North America</option>
                <option value="South America">South America</option>
                <option value="Africa">Africa</option>
                <option value="Australia">Australia</option>
                <option value="Online">Online</option>
            </select>
            <p><input type="text" name="venue_city" placeholder="Venue City" required/></p>
            <p><input type="text" name="winner" placeholder="Winner" required/></p>
            <p><input type="text" name="runner_up" placeholder="Runner Up" required/></p>
            <p><input type="text" name="prize_pool" placeholder="Prize Pool" required/></p>
            <p><input type="text" name="winners_prize" placeholder="Winners Prize" required/></p>
            <p><input type="text" name="winner_odds" placeholder="Winner Odds"/></p>
            <p><input name="submit" type="submit" value="Submit"/></p>
        </form>
        <p style="color:#FF0000;"><?php echo $status; ?></p>
    </div>
</div>
</body>
</html>

You can do this either client-side with JavaScript (AJAX/fetch) or server-side with traditional forms (GET/POST). — Chris Haas, Apr 28 '22 at 16:48
I would prefer to use the get/post method as that is how to rest of the code is set up to work. Could you please explain how I use the method to accomplish this. Thank you — Josh, Apr 28 '22 at 17:00
There's a couple of ways, and it still depends on whether you want to use any JS at all. At a high level you need to wrap your form controls in a `
` tag. If you don't mind a little JS you can use [this](https://stackoverflow.com/a/7231215/231316) to submit the form. If you don't want any, you'll need to add a submit button. Your form's action can be either GET or POST, which will populate the corresponding PHP variable (`$_GET` and `$_POST`) base on the `name` attribute of the form fields. — Chris Haas, Apr 28 '22 at 17:04
I have now added the full code for you to view. I use the post method to send data to the database. I am just wanting to create a dependent dropdown for whatever I select from the game dropdown menu. Can I use a WHERE clause to accomplish this in $records1 = mysqli_query($con, "SELECT * From tournaments WHERE....."); — Josh, Apr 28 '22 at 17:13
That gets more complicated. If your "Games" and "Tournaments" are relatively small, you could just store those in JS and update the second dropdown. You could also write all of the Tournaments, unfiltered, to the dropdown, and "tag" them somehow (like with a data attribute), and then hide/disable them whenever the first dropdown changes. You could also wrap that first set of fields in a dedicated form, and use a hidden value to indicate which form you are submitting. — Chris Haas, Apr 28 '22 at 17:19
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Apr 29 '22 at 13:46
**Warning:** `mysql_*` extension is deprecated as of PHP 5.5.0 (2013), and has been removed as of PHP 7.0.0 (2015). Instead, either the [mysqli](https://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](https://www.php.net/manual/en/book.pdo.php) extension should be used. See also the [MySQL API Overview](https://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API. — Dharman, Apr 29 '22 at 13:46
i would rather have separate selects, displayed as needed, than updating select options on the fly — Lk77, Apr 29 '22 at 17:33

Lk77 · Accepted Answer · 2022-04-29T17:48:35.337

here how i would do it

You can listen for the change event on the select game :

let gameSelect = document.querySelector('select[name="game"]');
gameSelect.addEventListener('change', (event) => {
       let value = event.target.value
       // hide all irrelevant inputs
       let divs = document.querySelectorAll('div.depend-on-select-game');
       divs.forEach(div => {
           divs.style.display = 'none';
       })
       // show relevant input
       let div = document.querySelector('div.depend-on-select-game.value-' + value);
       div.style.display = 'block'
    })
});

// set the default value here in the style
<div class="depend-on-select-game value-game1" style="display:block">
// your selects / inputs for game1
</div>
<div class="depend-on-select-game value-game2" style="display:none">
// your selects / inputs for game2
</div>

you do it once and put all form elements in here that are specific to a game

it will hide irrelevant inputs and show only the relevant inputs for the selected game

Also hidden inputs/selects won't be submitted to the server

And if the user changes his mind, the data won't be lost and still be there, which won't be the case if you update on the fly the options/value of the select according to the game selected

Dependent dropdown using WHERE clause

1 Answers1