How to display desired columns in mysql? [duplicat

问题:

This question already has an answer here:

• How to extract only columns which has non zero values in mysql and php? 2 answers

I want to display only non zero columns rather than displaying all the columns from the database those who are empty.

Below HTML script and php script which i have tried.

<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>

PHP Script Below

<?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'] == '0000-00-00' ? '' :       $row['samplerecived']) . "</td>";

echo "<td>" . ($row['molbioextraction'] == '0000-00-00' ? '' : $row['molbioextraction']) . "</td>";
echo "<td>" . ($row['molbioextractionqc'] == '0000-00-00' ? '' : $row['molbioextractionqc']) . "</td>";
echo "<td>" . ($row['libraryprep'] == '0000-00-00' ? '' : $row['libraryprep']) . "</td>";
echo "<td>" . ($row['libraryqc'] == '0000-00-00' ? '' : $row['libraryqc']) . "</td>";
echo "<td>" . ($row['sequencing'] == '0000-00-00' ? '' : $row['sequencing']) . "</td>";
echo "<td>" . ($row['datacheck'] == '0000-00-00' ? '' : $row['datacheck']) . "</td>";
echo "<td>" . ($row['resequencing'] == '0000-00-00' ? '' : $row['resequencing']) . "</td>";
echo "<td>" . ($row['qccheck'] == '0000-00-00' ? '' : $row['qccheck']) . "</td>";
echo "<td>" . ($row['analysisstarted'] == '0000-00-00' ? '' : $row['analysisstarted']) . "</td>";
echo "<td>" . ($row['analysiscompleted'] == '0000-00-00' ? '' : $row['analysiscompleted']) . "</td>";
echo "<td>" . ($row['report'] == '0000-00-00' ? '' : $row['report']) . "</td>";
echo "<td>" . ($row['outbound'] == '0000-00-00' ? '' : $row['outbound']) . "</td>";


   echo "</tr>";
   }
   echo "</table>";
?>  

Now output what i am getting is

In the above image, its displaying all the columns which has no values, But i need only those columns which has values.
Help me to do such modification in code.
Thanks in advance.

回答1:

I am pretty sure you posted this the other day and was explained that you cannot do this.

What if column 1 has a date for Sequencing, but column 2 does not?

If there was only going to be one result then you could do this. If all results had 0's in same column then you can do this.

回答2:

if(!empty($row['samplerecived']) && !empty($row['molbioextraction']) && !empty($row['molbioextractionqc']) && !empty($row['libraryprep']) && !empty($row['libraryqc']) && !empty($row['sequencing']) && !empty($row['datacheck']) && !empty($row['resequencing']) && !empty($row['qccheck']) && !empty($row['analysisstarted']) && !empty($row['analysiscompleted']) && !empty($row['report']) && !empty($row['outbound'])) {
    echo "<td>" . ($row['samplerecived'] == '0000-00-00' ? '' :       $row['samplerecived']) . "</td>";
    echo "<td>" . ($row['molbioextraction'] == '0000-00-00' ? '' : $row['molbioextraction']) . "</td>";
    echo "<td>" . ($row['molbioextractionqc'] == '0000-00-00' ? '' : $row['molbioextractionqc']) . "</td>";
    echo "<td>" . ($row['libraryprep'] == '0000-00-00' ? '' : $row['libraryprep']) . "</td>";
    echo "<td>" . ($row['libraryqc'] == '0000-00-00' ? '' : $row['libraryqc']) . "</td>";
    echo "<td>" . ($row['sequencing'] == '0000-00-00' ? '' : $row['sequencing']) . "</td>";
    echo "<td>" . ($row['datacheck'] == '0000-00-00' ? '' : $row['datacheck']) . "</td>";
    echo "<td>" . ($row['resequencing'] == '0000-00-00' ? '' : $row['resequencing']) . "</td>";
    echo "<td>" . ($row['qccheck'] == '0000-00-00' ? '' : $row['qccheck']) . "</td>";
    echo "<td>" . ($row['analysisstarted'] == '0000-00-00' ? '' : $row['analysisstarted']) . "</td>";
    echo "<td>" . ($row['analysiscompleted'] == '0000-00-00' ? '' : $row['analysiscompleted']) . "</td>";
    echo "<td>" . ($row['report'] == '0000-00-00' ? '' : $row['report']) . "</td>";
    echo "<td>" . ($row['outbound'] == '0000-00-00' ? '' : $row['outbound']) . "</td>";
}