时间戳

首页 > 解决方案 > “IS NOT NULL”在 sql 查询中不起作用

php - “IS NOT NULL”在 sql 查询中不起作用

问题描述

我想从数据库中获取数据。

我的数据库中有很多空值，但我不想在网页中显示空值。

为了消除我使用过的这些空值，IS NOT NULL但由于空值显示在网页中，它没有工作

<?php
session_start();
include('database.php');
$sql = "SELECT * FROM class8 WHERE teacher_id = :id AND physics8 IS NOT NULL";  
$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $_SESSION['teacher_id']);
$result = $stmt->execute();

?>


<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css" integrity="sha384-9aIt2nRpC12Uk9gS9baDl411NQApFmC26EwAOH8WgZl5MYYxFfc+NcPb1dKGj7Sk" crossorigin="anonymous">
    <link href="css/teacherdashboard.css" rel="stylesheet">
    <link rel="stylesheet" href="https://pro.fontawesome.com/releases/v5.10.0/css/all.css" integrity="sha384-AYmEC3Yw5cVb3ZcuHtOA93w35dYTsvhLPVnYs9eStHfGJvOvKxVfELGroGkvsg+p" crossorigin="anonymous"/>
    <title>teacher dashboard</title>
    <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js" integrity="sha384-OgVRvuATP1z7JjHLkuOU7Xw704+h835Lr+6QL9UvYjZE3Ipu6Tp75j7Bh/kR0JKI" crossorigin="anonymous"></script>

</head>
<style>
    td
    {
        font-family: sans-serif;
        text-align: center;

        font-weight: bold;
    }

    th
    {
        text-align: center;
    }
    </style>
<body>


<nav class="navbar navbarheight navbar-expand-lg navbar-white bg-white">
<a class="navbar-brand" href="teacherdashboard.php"><img  class="logo" height="20%" width="50%" srcset="image/logo1.png" border="0" alt="Null"></a>
  <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNavDropdown" aria-controls="navbarNavDropdown" aria-expanded="false" aria-label="Toggle navigation">
    <span class="navbar-toggler-icon"></span>
  </button>
  <div class="collapse navbar-collapse" id="navbarNavDropdown">
    <ul class="navbar-nav">
      <li class="nav-item active">
       <button type="button" onclick="window.location.href='teacherlogout.php';" class="student  btn btn-danger">Logout</button>
      </li>
      </li>
    </ul>
  </div>
</nav>

<div style="margin-top: 10%;" class="container tablediv">
<table class="table table-bordered table-condensed table1 ">
 <thead class="thead-dark">
    <tr>
         <th class="col2">physics</th>

    </tr>
 </thead>
 <?php  while ($row = $stmt->fetch(PDO::FETCH_OBJ)) { ?>
    <tr>

         <td> <?php echo $row->physics8 ?></td>

    </tr>
 <?php } ?>
 </table>

</div>


<?php include('footer.html') ?>
</body>
</html>

如何以任何其他方式删除这些空值？？？有类似的问题，但它不起作用！

标签： phpmysql

解决方案

问题是您没有考虑运算符优先级和/或您使用运算符OR而不是AND.
如果您想要没有任何null列的行，那么您应该使用AND：

SELECT * FROM class8 
WHERE teacher_id = :id 
AND physics8 IS NOT NULL AND chemistry8 IS NOT NULL AND maths8 IS NOT NULL AND biology8 IS NOT NULL AND english8 IS NOT NULL

但是，如果您想要至少一列不存在的行，null则使用括号：

SELECT * FROM class8 
WHERE teacher_id = :id 
AND (
  physics8 IS NOT NULL OR 
  chemistry8 IS NOT NULL OR 
  maths8 IS NOT NULL OR 
  biology8 IS NOT NULL OR 
  english8 IS NOT NULL
)

或与COALESCE()：

SELECT * FROM class8 
WHERE teacher_id = :id 
AND COALESCE(physics8, chemistry8, maths8, biology8, english8) IS NOT NULL

编辑。
如果您只想要 1 个非空列，则：

SELECT COALESCE(physics8, chemistry8, maths8, biology8, english8) 
FROM class8 
WHERE teacher_id = :id 
AND COALESCE(physics8, chemistry8, maths8, biology8, english8) IS NOT NULL

推荐阅读