我想从数据库中获取数据。 在我的数据库中有很多空值,但我不想在网页中显示空值。 为了消除这些空值,我使用了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>
如何以任何其他方式删除这些空值??? 有类似的问题,但它不起作用!!
问题是您没有考虑运算符优先级和/或使用运算符或
而不是和
。
如果希望行没有任何null
列,则应使用和
:
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