Display the number of times a location appeared in the respective table after corresponding option from dropdown list was chosen

Question

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 far: `

  $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'];

    //count instances of location_name in delivery_transaction table; 
    $count = mysqli_query($con, "
    SELECT location_ID, COUNT(location_ID) 
    FROM delivery_transaction 
    GROUP BY (location_ID)
    ");
  }  
?>

<!--Building dropdown contents-->
<form name="bldg_form" method="post" action="">
<select name="bldg">
  <option value="">Choose Building</option>;
    <?php while ($row = mysqli_fetch_assoc($bquery)) : ?>
      <option value="<?= $row['building_ID'] ?>" <?= $row['building_name'] == $selectedbldg ? 'selected' : '' ?>><?= $row['building_name'] ?></option>
      <?php endwhile ?>
</select>
<input type="submit" name="view" />
</form>

<section class="row text-center placeholders">
  <!--For the table to display everytime user selects an option from dropdown-->
  <div class="table-responsive">
    <table class="table table-striped">
      <thead>
        <tr>
         <th>Location</th>
         <th>Number of Visits</th>
        </tr>
      </thead>
      <tbody>
        <!--PHP alternative syntax for control structures: if; open brace-a colon (:) and the closing brace-endif-->

        <!--the isset function to check if variable has value assigned or not ; mysqli_num_rows returns the number of rows in the result set-->
        <?php if (isset($count) && mysqli_num_rows($count)) : ?> 
          <?php while($row = mysqli_fetch_assoc($count)) : ?>
            <tr>
              <td><?= $row['location_ID'] ?></td>
              <td><?= $row['COUNT(location_ID)'] ?></td>
            </tr>
          <?php endwhile ?>
        <?php else : ?>
          <tr>
            <td>No results to display</td>
          </tr>
        <?php endif ?>
      </tbody>
    </table>
  </div>
</section>`

Which I believe is literally wrong as it displays all the location IDs:

Please help :(

Sorry to add, I want to display the location name not the location ID based on the table displayed after user chooses the building from option. — rookie_programmer, Jan 25 '18 at 15:43
hi, i think u can make use of "join" statement to get the count and the location name from "Buildings" — Kimberlee Ho, Jan 25 '18 at 15:56
Hi Kimberlee! Thanks for the response. I tried it and it worked. I can display the location name now. However, my problem is whenever user chooses a Building from the dropdown list, it displays the whole summary of which locations were counted. Not what is present for the table connected to the chosen option. :( — rookie_programmer, Jan 25 '18 at 17:42
the objective is to get count of locations for the building? — Kimberlee Ho, Jan 25 '18 at 23:54

Mikey · Accepted Answer · 2018-01-26T00:49:36.167

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
}
?>

<!-- ... -->

<section class="row text-center placeholders">
    <div class="table-responsive">
        <table class="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 -->
            <?php if (isset($count) && mysqli_num_rows($count)) : ?> 
                <?php while($row = mysqli_fetch_assoc($count)) : ?>
                <tr>
                    <td><?= $row['location_ID'] ?></td>
                    <td><?= $row['num_visits'] ?></td>
                </tr>
                <?php endwhile ?>
            <?php else : ?>
                <tr>
                    <td>No results to display</td>
                </tr>
            <?php endif ?>
            </tbody>
        </table>
    </div>
</section>

Display the number of times a location appeared in the respective table after corresponding option from dropdown list was chosen

1 Answers1

Linked