如何只提取在MySQL和PHP中具有非零值的列？ [英] How to extract only columns which has non zero values in mysql and php?
本文介绍了如何只提取在MySQL和PHP中具有非零值的列？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我试图在网页上提取并显示，只有mysql数据库中的非零列。哪个列的日期是0000-00-00，我不想显示网页上的那些列。
下面是HTML代码和php脚本分别
< form action =search.phpmethod =post>
< p>
< lable>输入号码< / lable>
< input type =textname =soidid =soid>
< / p>
< p><按钮>< img src =http://icons.iconarchive.com/icons/harwen/pleasant/256/Search-icon.pngheight =50/> SEARCH< /按钮>< / p为H.
< / form>
< / html>
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\\\
，$ conn-> connect_error）;
exit（）;
$ result = mysqli_query（$ conn，SELECT * FROM $ dbname.statusinfo WHERE soid ='$ userinput1'）或死（mysqli_error
（$ conn） ）;
echo< p>< font size = 4> SO_NUMBER：$ userinput1;
echo< table border ='1'>
< tr>
< style>
th {
color：blue;
}
td {
颜色：黑色;
}
< / style>
< th>示例第b个提取QC第b个b第b个库提取第b个提取QC第b个b第b个库提取第b个提取QC第b个b第b个库提取第b个提取QC第b个库第b个提取QC第b $ b个库QC第b $ b个测序第
个数据校验第
个第RE测序< th>第b个< th>
QC Check
分析开始
分析已完成
< th>报告< th>
>出站< th>
< / tr>;
while（$ row = mysqli_fetch_assoc（$ result））
{
echo< tr>;
回声< br />;
echoDepartment：。$ row ['dept'];
回显< td> 。 $ row ['samplerecived']。 < / TD> 中;
回显< td> 。 $ row ['molbioextraction']。 < / TD> 中;
回显< td> 。 $ row ['molbioextractionqc']。 < / TD> 中;
回显< td> 。 $ row ['libraryprep']。 < / TD> 中;
回显< td> 。 $ row ['libraryqc']。 < / TD> 中;
回显< td> 。 $ row ['sequencing']。 < / TD> 中;
回显< td> 。 $ row ['datacheck']。 < / TD> 中;
回显< td> 。 $ row ['resequencing']。 < / TD> 中;
回显< td> 。 $ row ['qccheck']。 < / TD> 中;
回显< td> 。 $ row ['analysisstarted']。 < / TD> 中;
回显< td> 。 $ row ['analysiscompleted']。 < / TD> 中;
回显< td> 。 $ row ['report']。 < / TD> 中;
回显< td> 。 $ row ['outbound']。 < / TD> 中;
回声< / tr>;
}
echo< / table>;
？>
现在我得到网页上显示的所有列，我只需要那些有日期记录的列0000-00-00记录列。
这是mysql表。
我得到的输出是
i只想显示非零列。
在此先感谢
解决方案
试试这个：
$ result = mysqli_query（$ conn，SELECT * FROM $ dbname.statusinfo WHERE soid ='$ userinput1'AND date_column<>'0000-00-00'）或die（ mysqli_error（$康恩））;
尽管使用mysql，您甚至可以执行此操作：
$ result = mysqli_query（$ conn，SELECT * FROM $ dbname.statusinfo WHERE soid ='$ userinput1'AND date_column>'0000-00-00' ）或死（mysqli_error（$ conn））;
希望这可以帮助您
编辑
不幸的是，我不知道你想要什么使用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\\\
，$ conn-> connect_error）;
exit（）;
$ result = mysqli_query（$ conn，SELECT * FROM $ dbname.statusinfo WHERE soid ='$ userinput1'）或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 $ bqccheck=>QC Check，
analyzestarted=>分析开始，
analysiscompleted=>分析完成，
=报告，
出站=>出站，
）;
？>
< style>
th {
颜色：蓝色;
}
td {
颜色：黑色;
}
< / style>
< table border ='1'>
< tr>
<？php foreach（$ arrayHeadings as $ key => $ name）：？>
< th><？= $ name; ？>< /第>
<？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>
编辑
如果字段包含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\\\
，$ conn-> connect_error）;
exit（）;
$ result = mysqli_query（$ conn，SELECT * FROM $ dbname.statusinfo WHERE soid ='$ userinput1'）或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 $ bqccheck=>QC Check，
analyzestarted=>分析开始，
analysiscompleted=>分析完成，
=报告，
出站=>出站，
）;
？>
< style>
th {
颜色：蓝色;
}
td {
颜色：黑色;
}
< / style>
< table border ='1'>
< tr>
<？php foreach（$ arrayHeadings as $ key => $ name）：？>
<？php if（$ row [$ key]！=0000-00-00）：？>
< th><？= $ name; ？>< /第>
<？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>
I am trying to extract and display on web page, only non zero columns from mysql database. which ever column's date is 0000-00-00,i dont want to dispaly those columns on web page.
Below is the HTML code and php script respectively
<form action="search.php" method="post">
<p>
<lable>ENTER SO NUMBER</lable>
<input type="text" name="soid" id="soid" >
</p>
<p><button><img src="http://icons.iconarchive.com/icons/harwen/pleasant/256/Search-icon.png" height="50" />SEARCH</button></p>
</form>
</html>
PHP script,
<?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));
echo "<p><font size= 4>SO_NUMBER:$userinput1";
echo "<table border='1'>
<tr>
<style>
th{
color: blue;
}
td{
color: black;
}
</style>
<th>Sample Recived</th>
<th>Mol-Bio Extraction</th>
<th>Extraction QC</th>
<th>Library Prep</th>
<th>Library QC</th>
<th>Sequencing</th>
<th>Data Check</th>
<th>RE Sequencing</th>
<th>QC Check</th>
<th>Analysis Started</th>
<th>Analysis Completed</th>
<th>Report</th>
<th>Outbound</th>
</tr>";
while($row = mysqli_fetch_assoc($result))
{
echo "<tr>";
echo "<br />";
echo "Department:".$row['dept'] ;
echo "<td>" . $row['samplerecived'] . "</td>";
echo "<td>" . $row['molbioextraction'] . "</td>";
echo "<td>" . $row['molbioextractionqc'] . "</td>";
echo "<td>" . $row['libraryprep'] . "</td>";
echo "<td>" . $row['libraryqc'] . "</td>";
echo "<td>" . $row['sequencing'] . "</td>";
echo "<td>" . $row['datacheck'] . "</td>";
echo "<td>" . $row['resequencing'] . "</td>";
echo "<td>" . $row['qccheck'] . "</td>";
echo "<td>" . $row['analysisstarted'] . "</td>";
echo "<td>" . $row['analysiscompleted'] . "</td>";
echo "<td>" . $row['report'] . "</td>";
echo "<td>" . $row['outbound'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>
Now i am getting all column displayed on web page, i need only those columns which has date recorded not 0000-00-00 recorded column.
this is the mysql table.
Output i am getting is
i want to display only non zero columns.
Thanks in advance
解决方案
Try this:
$result = mysqli_query($conn, "SELECT * FROM $dbname.statusinfo WHERE soid = '$userinput1' AND date_column <> '0000-00-00' ") or die(mysqli_error($conn));
Although, with mysql you may even be able to do this:
$result = mysqli_query($conn, "SELECT * FROM $dbname.statusinfo WHERE soid = '$userinput1' AND date_column > '0000-00-00' ") or die(mysqli_error($conn));
Hope this helps
EDIT
I can see what you want now that you amended the question :) Unfortunately I do not know of a way to what you want using SQL (someone may).
You are outputting the column headings and so not outputting a particular column would cause them to appear in the wrong columns so you will just have to output nothing where time is 0000-00-00
This is how I would do it in PHP though. (and if I have missed your point again I may shoot myself :))
<?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
The table headings are not output if the field contains 0000-00-00. This relies on only one element being output at a time.
<?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>
这篇关于如何只提取在MySQL和PHP中具有非零值的列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
