How To Display Corresponding Mysql Columns In Html Based On Option Selected From Dynamic Dropdown List

I would like to ask for help in displaying the corresponding mysql table columns after the user selects an option from the dynamic dropdown box. I really don't know where I went wr

Solution 1:

If I understood correctly, you have one dropdown to show all the buildings.

Upon submission, show a table of deliveries based on the selected building.

That is 2 queries (you only have one).

Here's how I would do it:

// never a bad idea to turn on your PHP error reporting in development
error_reporting(E_ALL);
ini_set('display_errors', 1);

$con = new mysqli("localhost" ,"root" ,"" ,"user_databases");

// always query buildings bc we need it for the dropdown
$bquery = mysqli_query($con, "SELECT building_ID, building_name FROM buildings");

$selectedbldg = null;

// if the form was submitted
if (!empty($_POST['bldg'])) {
    // store selected building_ID
    $selectedbldg = $_POST['bldg'];
    // query deliveries based on building; 
    // note the additional condition (I'm assuming building_ID is an integer)
    $dquery = mysqli_query($con, "
        SELECT  delivery_status, starting_time, arrival_time, duration, buildings.building_name, 
                location.location_name
        FROM    delivery_transaction, buildings, location 
        WHERE   delivery_transaction.building_ID = buildings.building_ID
        AND     delivery_transaction.location_ID = location.location_ID
        AND     buildings.building_ID = {$selectedbldg}
    ");

    // though it is not part of the scope of your question, 
    // you should probably use prepared statement above
}
?>


<formname="bldg_form"method="post"action=""><selectname="bldg"><optionvalue="">Choose Building</option>;
        <?phpwhile ($row = mysqli_fetch_assoc($bquery)) : ?><optionvalue="<?=$row['building_ID'] ?>"<?=$row['building_name'] == $selectedbldg ? 'selected' : ''?>><?=$row['building_name'] ?></option><?phpendwhile?></select><inputtype="submit"name="view" /></form><sectionclass="row text-center placeholders"><divclass="table-responsive"><tableclass="table table-striped"><thead><tr><th>Delivery Status</th><th>Starting Time</th><th>Arrival Time</th><th>Duration</th><th>Location</th></tr></thead><tbody><?phpif (isset($dquery) && mysqli_num_rows($dquery)) : ?><?phpwhile($row = mysqli_fetch_assoc($dquery)) : ?><tr><td><?=$row['delivery_status'] ?></td><td><?=$row['starting_time'] ?></td><td><?=$row['arrival_time'] ?></td><td><?=$row['duration'] ?></td><td><?=$row['location_name'] ?></td></tr><?phpendwhile?><?phpelse : ?><tr><td>No results to display</td></tr><?phpendif?></tbody></table></div></section>

Good to read:

• When should I use prepared statements?

• mysqli_prepare()

• Alternative syntax for control structures

• Ternary operator