文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >FIilter列使用复选框和联接表php mysqli

问FIilter列使用复选框和联接表php mysqli
EN

Stack Overflow用户
提问于 2018-10-25 04:20:54
回答 2查看 47关注 0票数 0

我想从检查的数据中显示列。例如，我检查了块和Web中断，所以只有列块和Web中断。我在寻找这个，但我找不到。

这是我的复选框代码

代码语言：javascript
复制
</div>         

                <label>Select Defect</label>
                <table>
                <tr>
                <td width='300px'><p><input type="checkbox" name="chk[]" value=".1"> Big Hole</p>
                    <p><input type="checkbox" name="chk[]" value=".2"> Big Pin Hole</p>
                    <p><input type="checkbox" name="chk[]" value=".3"> Detection Off</p>
                    <p><input type="checkbox" name="chk[]" value=".4"> Extreme Hole</p>
                    <p><input type="checkbox" name="chk[]" value=".5"> Pin Hole</p>
                </td> 
                <td width='300px'><p><input type="checkbox" name="chk[]" value=".6"> Pin Light Spot</p>
                    <p><input type="checkbox" name="chk[]" value=".7"> Small Hole</p>
                    <p><input type="checkbox" name="chk[]" value=".8"> Uninspected</p>
                    <p><input type="checkbox" name="chk[]" value=".9"> Very Small Dark Spot</p>
                    <p><input type="checkbox" name="chk[]" value=".10"> Very Small Light Spot</p>
                </td>      
                <td width='300px'><p><input type="checkbox" name="chk[]" value=".11"> Web Break</p>
                    <p><input type="checkbox" name="chk[]" value=".12"> Filter Small</p>
                    <p><input type="checkbox" name="chk[]" value=".13"> Edge Filter Small</p>
                    <p><input type="checkbox" name="chk[]" value=".14"> Filter Light Spot</p>
                    <p><input type="checkbox" name="chk[]" value=".15"> Mini Dark Spot</p>
                </td>  
                <td width='300px'><p><input type="checkbox" name="chk[]" value=".16"> Small Dark Spot</p>
                    <p><input type="checkbox" name="chk[]" value=".17"> Lump</p>
                    <p><input type="checkbox" name="chk[]" value=".18"> Intensity Filter</p>
                    <p><input type="checkbox" name="chk[]" value=".19"> Big Dark Spot</p>
                    <p>&nbsp;</p>
                </td>                
              </table>
          </div>

这是datatable

代码语言：javascript
复制
    <thead>
      <th class="text-center" >Date Process</th>
      <th class="text-center" >Big Hole</th>
      <th class="text-center">Big Pin Hole</th>
      <th class="text-center" >Detection Off</th>
      <th class="text-center" >Extreme Hole</th>
      <th class="text-center" >Pin Hole</th>
      <th class="text-center" >Pin Light Spot</th>
      <th class="text-center" >Small Hole</th>
      <th class="text-center" >Uninspected</th>
      <th class="text-center" >Very Small Dark Spot</th>
      <th class="text-center" >Very Small Light Spot</th>
      <th class="text-center" >Web Break</th>
      <th class="text-center" >Filter Small</th>
      <th class="text-center" >Edge Filter Small</th>
      <th class="text-center" >Filter Light Spot</th>
      <th class="text-center" >Mini Dark Spot</th>
      <th class="text-center" >Small Dark Spot</th>
      <th class="text-center" >Lump</th>
      <th class="text-center" >Intensity Filter</th>
      <th class="text-center" >Big Dark Spot</th>
    </thead>

我从数据库中获取数据，并使用联接

代码语言：javascript
复制
  <?php 


  $no = 0;
  $modal=mysqli_query($mysqli,"
  SELECT t_name_file.PROCESS_TIME,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 1 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `1`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 2 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `2`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 3 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `3`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 4 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `4`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 5 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `5`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 6 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `6`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 7 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `7`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 8 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `8`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 9 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `9`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 10 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `10`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 11 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `11`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 12 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `12`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 13 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `13`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 14 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `14`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 15 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `15`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 16 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `16`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 17 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `17`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 18 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `18`,
  COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 19 THEN t_defect_class.DEFECT_CLASS_NAME END) AS `19`
  FROM t_transaction
  INNER JOIN t_name_file ON t_transaction.NAMEFILE_ID=t_name_file.NAMEFILE_ID
  INNER JOIN t_defect_class ON t_transaction.DEFECT_CLASS_ID=t_defect_class.DEFECT_CLASS_ID
  GROUP BY t_transaction.DEFECT_CLASS_ID, t_name_file.PROCESS_TIME ORDER BY t_name_file.PROCESS_TIME");
  while($defect=mysqli_fetch_array($modal)){
  $no++;

?>
  <tr>
      <td class="text-center"><?php echo $defect['PROCESS_TIME']; ?></td>
      <td class="text-center"><?php echo  $defect['1']; ?></td>
      <td class="text-center"><?php echo  $defect['2']; ?></td>
      <td class="text-center"><?php echo  $defect['3']; ?></td>
      <td class="text-center"><?php echo  $defect['4']; ?></td>
      <td class="text-center"><?php echo  $defect['5']; ?></td>
      <td class="text-center"><?php echo  $defect['6']; ?></td>
      <td class="text-center"><?php echo  $defect['7']; ?></td>
      <td class="text-center"><?php echo  $defect['8']; ?></td>
      <td class="text-center"><?php echo  $defect['9']; ?></td>
      <td class="text-center"><?php echo  $defect['10']; ?></td>
      <td class="text-center"><?php echo  $defect['11']; ?></td>
      <td class="text-center"><?php echo  $defect['12']; ?></td>
      <td class="text-center"><?php echo  $defect['13']; ?></td>
      <td class="text-center"><?php echo  $defect['14']; ?></td>
      <td class="text-center"><?php echo  $defect['15']; ?></td>
      <td class="text-center"><?php echo  $defect['16']; ?></td>
      <td class="text-center"><?php echo  $defect['17']; ?></td>
      <td class="text-center"><?php echo  $defect['18']; ?></td>
      <td class="text-center"><?php echo  $defect['19']; ?></td>

      </td>
  </tr>    

<?php } ?>

</div>

当我尝试过滤列时，它是有效的，但对字段则不起作用。

mysqli
php
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2018-10-29 02:08:30

谢谢，现在起作用了。

这是我的密码

代码语言：javascript
复制
    if (isset($_POST['chk'])){
  $defect_query = "";
  foreach ($_POST['chk'] as $id => $data) {
    $defect_query = $defect_query.", COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN ".$data." THEN t_defect_class.DEFECT_CLASS_NAME END) AS `".$data."`";
  }

  $machine      = $_POST['machine'];
  $start_date   = $_POST['start_date'];
  $end_date     = $_POST['end_date'];
  $grade        = $_POST['grade'];
  $chk          = $_POST['chk'];
?>

    <thead>
      <th class="text-center" >Date Process</th>
      <?php
        foreach ($_POST['chk'] as $id => $data) {
          $get_defect_name = mysqli_query($mysqli, "SELECT * FROM `t_defect_class` WHERE `DEFECT_CLASS_ID` = $data");
          $defect_name_table = mysqli_fetch_assoc($get_defect_name);
        ?>
        <th class="text-center"><?php echo $defect_name_table['DEFECT_CLASS_NAME']; ?></th>
        <?php } ?>
    </thead>
<?php



  $modal=mysqli_query($mysqli,"SELECT t_name_file.PROCESS_TIME".$defect_query." FROM t_transaction INNER JOIN t_name_file ON t_transaction.NAMEFILE_ID=t_name_file.NAMEFILE_ID INNER JOIN t_defect_class ON t_transaction.DEFECT_CLASS_ID=t_defect_class.DEFECT_CLASS_ID GROUP BY t_transaction.DEFECT_CLASS_ID, t_name_file.PROCESS_TIME ORDER BY t_name_file.PROCESS_TIME");
  $no = 0;
  while($defect=mysqli_fetch_array($modal)){
  $no++;

?>
  <tr>
      <td class="text-center"><?php echo $defect['PROCESS_TIME']; ?></td>
      <?php foreach ($_POST['chk'] as $id => $data) { ?>
        <td class="text-center"><?php echo  $defect[$data]; ?></td>
      <?php } ?>
  </tr>


<?php 
} 
} ?>
票数 0
EN

Stack Overflow用户

发布于 2018-10-25 04:46:44

您必须解析$_POST数组中的值，并只使用用户选择的列构建查询。

记住要检查是否输入了一些值，否则您将不会选择会产生错误的任何值。另外，我也不确定这些复选框上的值，所以我用整数代替小数来匹配您提供的SQL。

确认用户至少检查了一个框的示例-

代码语言：javascript
复制
if(isset($_POST['chk']){
  // do the example stuff below and build your query
}else{
  // use your existing query string as-is
}

?>

然后构建一个查询，在此基础上选中复选框。使用如注释中所示的数组示例值

代码语言：javascript
复制
<?php
// this would be $_POST['chk'] if the user checked
// those 3 particular checkboxes
$arr=array(7,8,9);

$queryString="SELECT t_name_file.PROCESS_TIME,";

foreach($arr as $conditionValue){
    $queryString.="
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN ".$conditionValue." THEN t_defect_class.DEFECT_CLASS_NAME END) AS '".$conditionValue."' ,";
}
$queryString=trim($queryString,",");
$queryString.="FROM t_transaction
INNER JOIN t_name_file ON t_transaction.NAMEFILE_ID=t_name_file.NAMEFILE_ID
INNER JOIN t_defect_class ON t_transaction.DEFECT_CLASS_ID=t_defect_class.DEFECT_CLASS_ID
GROUP BY t_transaction.DEFECT_CLASS_ID, t_name_file.PROCESS_TIME ORDER BY t_name_file.PROCESS_TIME";


print("\n\n".$queryString."\n\n");

?>

生成

代码语言：javascript
复制
SELECT t_name_file.PROCESS_TIME,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 7 THEN t_defect_class.DEFECT_CLASS_NAME END) AS '7' ,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 8 THEN t_defect_class.DEFECT_CLASS_NAME END) AS '8' ,
COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN 9 THEN t_defect_class.DEFECT_CLASS_NAME END) AS '9' FROM t_transaction
INNER JOIN t_name_file ON t_transaction.NAMEFILE_ID=t_name_file.NAMEFILE_ID
INNER JOIN t_defect_class ON t_transaction.DEFECT_CLASS_ID=t_defect_class.DEFECT_CLASS_ID
GROUP BY t_transaction.DEFECT_CLASS_ID, t_name_file.PROCESS_TIME ORDER BY t_name_file.PROCESS_TIME

最后，在确保查询都正常后，我将在查询字符串中提取hte硬返回.

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/52981309

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档