显示从下拉列表中选择相应选项后，某个位置出现在相应表中的次数

问题描述

我想寻求帮助，以显示位置值在表格中出现的次数，该次数与用户从下拉列表中单击的选项相对应.

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

我认为这从字面上看是错误的，因为它显示了所有位置ID:

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

请帮助:(

推荐答案

如果我正确理解，我认为您的问题与相同作为最后一个.您只是缺少WHERE子句.

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

看看是否可行:

$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>

更多好东西可供阅读:

• 别名

通过GROUP BY加入

了解WHERE如何与GROUP BY和汇总一起使用

这篇关于显示从下拉列表中选择相应选项后，某个位置出现在相应表中的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！