CODEWITHSUNDEEP
phpmysql
Digital fortress
Digital fortress

Reputation: 737

Get only non empty fields from mySQL

I have a long table and I need to display column names and their respective values only if its not null. So, here is what I came up with as a concept but the implementation doesn't work.

$sql = "Select * from audittable where id = " . $id;
$result = mysql_query($sql) or die(mysql_error());


$sqlfields = 'SHOW COLUMNS FROM audittable where field not in ("audit_id","last_by","last_datetime","id")';
$fieldsquery = mysql_query($sqlfields);

echo "<table style='width:100%;padding:8px'>
    <tr class='heading'><td>Field Name</td><td>Value</td></tr>";
while ($row = mysql_fetch_array($result)) {

    foreach ($rowfields = mysql_fetch_array($fieldsquery)) {
        if ($rowfields["field"] == $row[0] && $row[0]!=""){
   ?>
<tr>
    <td><?=$row[0]?></td>
    <td><?=$row[1]?></td>

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

I'm not sure the logic is right (two loops), any ideas ?

Maybe I wasnt clear enough, the table includes 50 fields, most probably 3 or 4 of them will have a value per row, I need to display only those columns that has values

Thanks

Upvotes: 1

Views: 12561

Answers (5)

melchy
melchy

Reputation: 1

try this to print out only fieldname and result of the columns that have some values:

/* fetch associative array */
   $i=1;
   while($a = $result->fetch_assoc()) {
     $i++;
     foreach ($a as $key => $value) {
     if($value != NULL) {
        echo $key." = ".$value."<br/>";
        }
     }
     echo "-- <br />";    
   } 
   ...

Upvotes: 0

User123456
User123456

Reputation: 2738

Try this:

SELECT * from audittable where id = " . $id." AND (NAME IS NOT NULL OR NAME > "" )

Upvotes: 0

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

I think the logic is sound actually, but the problem I see is that you're not actually checking against the field or for null here:

if ($rowfields["field"] == $row[0] && $row[0] != "")

so I think you have a couple options. First, you could do this:

if ($row[$rowfields["field"]] != null
    && $row[$rowfields["field"]] != "")

or you could do this:

if (!empty($row[$rowfields["field"]]))

however, there is a caveat with empty. The PHP manual says:

The following things are considered to be empty: 
  * "" (an empty string)
  * 0 (0 as an integer)
  * 0.0 (0 as a float)
  * "0" (0 as a string)
  * NULL
  * FALSE
  * array() (an empty array)
  * $var; (a variable declared, but without a value)

so if you have any integer or float fields that would be 0 but you don't consider those empty this wouldn't work.

Upvotes: 0

Pramod Kumar Sharma
Pramod Kumar Sharma

Reputation: 8012

Check is null

$sql = "Select * from audittable where id = " . $id." AND YOUR_FIELD_NAME IS NOT NULL";

Upvotes: 2

Ivo Pereira
Ivo Pereira

Reputation: 3500

Apart of checking it is NULL, I usually check if it is not set as empty.

SELECT * from audittable where id = " . $id." AND (NAME IS NOT NULL OR NAME != '' )

Upvotes: 2

Related Questions