'Dependent dropdown using WHERE clause

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>

php mysql sql

Solution 1:^[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1

'Dependent dropdown using WHERE clause

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]