php - 执行 SELECT 语句后统计特定表结果

Question

我是某公司的实习生。他们让我做一个电脑设备的监控系统。我制作了一个过滤器选项来过滤表的结果。过滤器选项包含称为“条件”和“状态”的单选按钮。条件是一列，仅包含可用、已分配、待维修、缺少零件、缺少 eq、有缺陷、翻新和未知条件等值。状态也是一列，只有“新状态”、“旧状态”和“未知状态”值。这些列值也是过滤器选项中的单选按钮。问题是我想在单选按钮标签旁边显示值的数量，但我想不出一种方法来计算这些结果，因为我已经使用 SELECT 语句来显示表结果。有没有办法统计SELECT语句执行后生成的值？每当用户单击单选按钮或过滤器选项发生更改时，就会发生 select 语句。我只是想计算“可用”、“已分配”、“待修复”等。顺便说一句，所有状态、所有条件和工作只是单选按钮，而不是列值。存储在 $state 和 $condition 变量中的是存储在 PHP session 中的单选按钮值，用于过滤表结果。使用单选按钮的过滤已经开始工作。下面是我的代码:

<?php
 $state=$_SESSION['state'];
 $condition=$_SESSION['condition'];
 ?>

 <span style="margin-left:35px;">STATE</span><br><br>
 <form>
 <input type="radio" name="state" id="state" value="allstate" 
  onchange="javascript:handleRadio(this)" <?php echo $state==='allstate' ? 
  'checked' : '' ?>> All State <br>
  <input type="radio" name="state" id="new" value="new" 
 onchange="javascript:handleRadio(this)" <?php echo $state==='new' ? 
 'checked' : '' ?>> New EQ <br>
<input type="radio" name="state" id="old" value="old" 
onchange="javascript:handleRadio(this)" <?php echo $state==='old' ? 
'checked' : '' ?>> Old EQ <br>
<input type="radio" name="state" id="unknown" value="Unknown state" 
onchange="javascript:handleRadio(this)" <?php echo $state==='Unknown state' 
? 'checked' : '' ?>> Unknown State <br>
</form>

                                         <!--    -->
<form action="" method="POST" class="filters2">
<span style="margin-left:150px;">CONDITION</span><br><br>
<input type="radio" name="condition" value="allcondition" 
onchange="javascript:handleRadiocond(this)" <?php echo 
$condition==='allcondition' ? 'checked' : '' ?>> All Condition <br>
<input type="radio" name="condition" value="working" 
onchange="javascript:handleRadiocond(this)" <?php echo 
$condition==='working' ? 'checked' : '' ?>> Working EQ (1 & 2): <br>
<input type="radio" name="condition" value="Available/Unassigned" 
onchange="javascript:handleRadiocond(this)" <?php echo 
$condition==='Available/Unassigned' ? 'checked' : '' ?>> 
(1)Available/Unassigned EQ:<br>
 <input type="radio" name="condition" value="assigned" 
 onchange="javascript:handleRadiocond(this)" <?php echo 
 $condition==='assigned' ? 'checked' : '' ?>>(2)Assigned EQ:<br>
<input type="radio" name="condition" value="repair" 
onchange="javascript:handleRadiocond(this)" <?php echo $condition==='repair' 
  ? 'checked' : '' ?>> For Repair EQ:<br>
<div class="filters3">
<input type="radio" name="condition" value="missingpart" 
onchange="javascript:handleRadiocond(this)" <?php echo 
$condition==='missingpart' ? 'checked' : '' ?>> W/ Missing Parts EQ: <br>
<input type="radio" name="condition" value="missingeq" 
onchange="javascript:handleRadiocond(this)" <?php echo 
$condition==='missingeq' ? 'checked' : '' ?>> Missing EQ: <br>
<input type="radio" name="condition" value="defective" 
onchange="javascript:handleRadiocond(this)" <?php echo 
 $condition==='defective' ? 'checked' : '' ?>> Defective EQ <br>
<input type="radio" name="condition" value="refurbished" 
onchange="javascript:handleRadiocond(this)" <?php echo 
$condition==='refurbished' ? 'checked' : '' ?>> Refurbished: <br>
<input type="radio" name="condition" value="Unknown condition" 
onchange="javascript:handleRadiocond(this)" <?php echo $condition==='Unknown 
 condition' ? 'checked' : '' ?>> Unknown Condition: <br>
 </div>
 </form>

<?php
if($state=='allstate' && $condition=='allcondition'){
 $sql = (
"SELECT *
 from eq_inv
 left JOIN empl_tbl
    on eq_inv.empl_no=empl_tbl.empl_no
 WHERE eq_state='new'
 OR eq_state='old'
 OR eq_state='Unknown state'
 AND (eq_condition='Available/Unassigned'
   OR eq_condition='assigned'
   OR eq_condition='repair'
   OR eq_condition='missingpart'
   OR eq_condition='missingeq'
   OR eq_condition='refurbished'
   OR eq_condition='Unknown condition')
 ORDER BY empl_firstname
      , empl_lastname
      , middlename  ASC"
  );
}

elseif($state=='allstate' AND ($condition=='Available/Unassigned' OR 
$condition=='assigned' OR   $condition=='repair' OR 
$condition=='missingpart' OR $condition=='mmissingeq' OR 
$condition=='defective' OR $condition=='refurbished' OR $condition=='Unknown 
condition')){
$sql = (
"SELECT *
from eq_inv
left JOIN empl_tbl
  on eq_inv.empl_no=empl_tbl.empl_no
WHERE eq_condition='$condition'
AND (eq_state='new'
  OR eq_state='old'
  OR eq_state='Unknown state')
ORDER BY empl_firstname
  , empl_lastname
  , middlename  ASC"
  );
 }
 elseif($condition=='allcondition' AND ($state=='new' OR $state=='old' OR 
 $state=='Unknown state')){
  $sql = (
"SELECT *
from eq_inv
left JOIN empl_tbl
  on eq_inv.empl_no=empl_tbl.empl_no
  WHERE eq_state='$state'
  AND (eq_condition='Available/Unassigned'
    OR eq_condition='assigned'
    OR eq_condition='repair'
    OR eq_condition='missingpart'
    OR eq_condition='missingeq'
    OR eq_condition='refurbished'
    OR eq_condition='Unknown condition')
  ORDER BY empl_firstname
    , empl_lastname
    , middlename  ASC"
  );
  }
  elseif($condition=='working' AND ($state=='new' OR $state=='old' OR 
  $state=='Unknown state')){
        $sql = (
          "SELECT *
          from eq_inv
          left JOIN empl_tbl
            on eq_inv.empl_no=empl_tbl.empl_no
          WHERE eq_state='$state'
          AND (eq_condition='Available/Unassigned'
            OR eq_condition='assigned')
          ORDER BY empl_firstname
            , empl_lastname
            , middlename  ASC"
          );
        }
       elseif($condition=='working' AND ($state=='allstate')){
                $sql = (
                  "SELECT *
                  from eq_inv
                  left JOIN empl_tbl
                    on eq_inv.empl_no=empl_tbl.empl_no
                  WHERE eq_state='new'
                  OR eq_state='old'
                  OR eq_state='Unknown state'
                  AND (eq_condition='Available/Unassigned'
                    OR eq_condition='assigned')
                  ORDER BY empl_firstname
                    , empl_lastname
                    , middlename  ASC"
                  );
                }
           else{
        $sql = (
      "SELECT *
     from eq_inv
      left JOIN empl_tbl
       on eq_inv.empl_no=empl_tbl.empl_no
       WHERE eq_state='$state'
       AND eq_condition='$condition'
      ORDER BY empl_firstname
    , empl_lastname
    , middlename  ASC"
    );
  }
    $result = mysqli_query($conn, $sql);
    while($row = mysqli_fetch_assoc($result)) {
   ?>
      <td>
      <a href="temporarypopup.php"><button>View</button></a>
      </td>
      <td style="font-size:16px;"><STRONG><?php echo $row['eqdesc']; ?> 
      </STRONG></td>
      <td><?php echo $row['eq_inv_id']; ?></td>
      <td style="color:red; font-size:15px;"><strong><a href="timeline.php? emp_no=<?php echo $row['empl_no'];?>" style="color:inherit;"><?php echo 
      $row['empl_firstname']; ?></a></strong></td>
      <td style="color:red; font-size:15px;"><strong><a href="timeline.php? emp_no=<?php echo $row['empl_no'];?>" style="color:inherit;"><?php echo 
       $row['middlename']; ?></a></strong></td>
      <td style="color:red; font-size:15px;"><strong><a href="timeline.php?emp_no=<?php echo $row['empl_no'];?>" style="color:inherit;"><?php echo 
       $row['empl_lastname']; ?></a></strong></td>
      <td><?php echo $row['brand']; ?></td>
        <td><?php echo $row['serial_no']; ?></td>
        <td><?php echo $row['eq_state']; ?></td>
        <td><?php echo $row['eq_condition']; ?></td>
      <td><?php echo $row['curr_equip_loc']; ?></td>
    </tbody>
     <?php
      }

     ?>

Answer 1

您可以使用mysqli_num_rows()来获取查询返回的记录数。

    $result = mysqli_query($conn, $sql);
    $num_rows = mysqli_num_rows($result);

然后在您的 CONDITION 表单中显示您运行查询的条件的值(如果已设置)。

另外请注意，您的 elseif block 中有一个小拼写错误

elseif($state=='allstate' AND ($condition=='Available/Unassigned' OR 
$condition=='assigned' OR   $condition=='repair' OR 
$condition=='missingpart' OR $condition=='mmissingeq' OR 
// ...
// Should probably be
elseif($state=='allstate' AND ($condition=='Available/Unassigned' OR 
$condition=='assigned' OR   $condition=='repair' OR 
$condition=='missingpart' OR $condition=='missingeq' OR