尝试这个:
$result = mysqli_query($conn, "SELECT * FROM $dbname.statusinfo WHERE soid = '$userinput1' AND date_column <> '0000-00-00' ") or die(mysqli_error($conn));
不过,使用 mysql 你甚至可以这样做:
$result = mysqli_query($conn, "SELECT * FROM $dbname.statusinfo WHERE soid = '$userinput1' AND date_column > '0000-00-00' ") or die(mysqli_error($conn));
希望这可以帮助
EDIT
既然您修改了问题,我就可以看到您想要什么了:)
不幸的是我不知道如何使用 SQL 来实现你想要的(有人可能会)。
您正在输出列标题,因此不输出特定列会导致它们出现在错误的列中,因此您只需在时间为 0000-00-00 的地方不输出任何内容
这就是我在 PHP 中的做法。 (如果我再次错过了你的观点,我可能会开枪自杀:))
<?php
$userinput1 = $_POST['soid'];
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "status";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_errno) {
printf("Connect failed: %s\n", $conn->connect_error);
exit();
}
$result = mysqli_query($conn, "SELECT * FROM $dbname.statusinfo WHERE soid = '$userinput1' ") or die(mysqli_error($conn));
$arrayHeadings = array(
"dept" => "Department",
"samplerecived" => "Sample Recived",
"molbioextraction" => "Mol-Bio Extraction",
"molbioextractionqc" => "Extraction QC",
"libraryprep" => "Library Prep",
"libraryqc" => "Library QC",
"sequencing" => "Sequencing",
"datacheck" => "Data Check",
"resequencing" => "RE Sequencing",
"qccheck" => "QC Check",
"analysisstarted" => "Analysis Started",
"analysiscompleted" => "Analysis Completed",
"report" => "Report",
"outbound" => "Outbound",
);
?>
<style>
th{
color: blue;
}
td{
color: black;
}
</style>
<table border='1'>
<tr>
<?php foreach($arrayHeadings as $key => $name): ?>
<th><?= $name; ?></th>
<?php endforeach; ?>
</tr>
<tr>
<?php while($row = mysqli_fetch_assoc($result)): ?>
<?php foreach($arrayHeadings as $key => $name): ?>
<?php if($row[$key] != "0000-00-00"): ?>
<td><?= $row[$key]; ?></td>
<?php else: ?>
<td></td>
<?php endif; ?>
<?php endforeach; ?>
<?php endwhile; ?>
</tr>
</table>
EDIT
如果字段包含 0000-00-00,则不会输出表标题。
这依赖于一次仅输出一个元素。
<?php
$userinput1 = $_POST['soid'];
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "status";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_errno) {
printf("Connect failed: %s\n", $conn->connect_error);
exit();
}
$result = mysqli_query($conn, "SELECT * FROM $dbname.statusinfo WHERE soid = '$userinput1' ") or die(mysqli_error($conn));
$arrayHeadings = array(
"dept" => "Department",
"samplerecived" => "Sample Recived",
"molbioextraction" => "Mol-Bio Extraction",
"molbioextractionqc" => "Extraction QC",
"libraryprep" => "Library Prep",
"libraryqc" => "Library QC",
"sequencing" => "Sequencing",
"datacheck" => "Data Check",
"resequencing" => "RE Sequencing",
"qccheck" => "QC Check",
"analysisstarted" => "Analysis Started",
"analysiscompleted" => "Analysis Completed",
"report" => "Report",
"outbound" => "Outbound",
);
?>
<style>
th{
color: blue;
}
td{
color: black;
}
</style>
<table border='1'>
<tr>
<?php foreach($arrayHeadings as $key => $name): ?>
<?php if($row[$key] != "0000-00-00"): ?>
<th><?= $name; ?></th>
<?php endif; ?>
<?php endforeach; ?>
</tr>
<tr>
<?php while($row = mysqli_fetch_assoc($result)): ?>
<?php foreach($arrayHeadings as $key => $name): ?>
<?php if($row[$key] != "0000-00-00"): ?>
<td><?= $row[$key]; ?></td>
<?php endif; ?>
<?php endforeach; ?>
<?php endwhile; ?>
</tr>
</table>