Display The Number Of Times A Location Appeared In The Respective Table After Corresponding Option From Dropdown List Was Chosen

I would like to ask help in displaying the number of times a location value appeared in a table that corresponds to an option user clicks from dropdown list. Here's what I did so f

Solution 1:

If I understood correctly, I believe your problem is exactly the same as last one. You're just missing a WHERE clause.

See if this works:

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

//query buildings table 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'];
    // the subquery is used to count how many times each location appears
    // for a particular building
    $count = mysqli_query($con, "
        SELECT lo.location_ID, lo.location_name, dt.num_visits
        FROM location lo
        JOIN (
            SELECT location_ID, COUNT(location_ID) AS num_visits
            FROM delivery_transaction 
            WHERE building_ID = {$selectedbldg}
            GROUP BY location_ID
        ) AS dt ON lo.location_ID = dt.location_ID
    ");

    // like before, better to use prepared statement
}
?>

<!-- ... --><sectionclass="row text-center placeholders"><divclass="table-responsive"><tableclass="table table-striped"><thead><tr><th>Location</th><th>Number of Visits</th></tr></thead><tbody><!-- PHP alternative syntax for control structures: easier to read (imo) --><!-- isset function is to ensure variable $count exist as it only gets declared in the IF condition (you would get an error otherwise) --><!-- mysqli_num_rows is to check if there are any results to loop over --><?phpif (isset($count) && mysqli_num_rows($count)) : ?><?phpwhile($row = mysqli_fetch_assoc($count)) : ?><tr><td><?=$row['location_ID'] ?></td><td><?=$row['num_visits'] ?></td></tr><?phpendwhile?><?phpelse : ?><tr><td>No results to display</td></tr><?phpendif?></tbody></table></div></section>

More good stuff to read:

• Aliases

• JOIN with GROUP BY

• Understanding how WHERE works with GROUP BY and Aggregation