JDean JDean - 7 months ago 17
SQL Question

Parsing empty rows from MySQL table and output as html table without empty cells

I have been trying to create a PHP function that can display a MySQL table as a HTML table using PHP. So far I am able to output any table I choice, yet I am encountering a problem when the MySQL table contains empty rows, because empty cells result in the HTML tables. My code is as such:

<?php
function getTABLE(){
$db_host = 'HOST.com';
$db_user = 'USER1';
$db_pwd = 'PASSWORD';
$database = 'testdb';
$table = 'FAQTable';

if (!mysql_connect($db_host, $db_user, $db_pwd))
die("Can't connect to database");
if (!mysql_select_db($database))
die("Can't select database");

//// sending query and only result cell that are not NULL
$result = mysql_query("SELECT * FROM {$table}");

if (!$result) {
die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);
echo "<h3><center>Table: {$table}</h3>";
echo "<table border='1'><tr>";

//// printing table headers
for($i=0; $i<$fields_num; $i++){
$field = mysql_fetch_field($result);
echo "<td>{$field->name}</td>";
}

//// printing table rows
while($row = mysql_fetch_row($result)){
echo "<tr>";
//// $row is array... foreach( .. ) puts every element
//// of $row to $cell variable
foreach($row as $cell)
echo "<td>$cell</td>";
echo "</tr>";
}
print "</TABLE>";
mysql_close();
}

print getTABLE();

?>


My dilemma is in the "printing table rows" section of the code. I am hoping there is a way in the
while($row = mysql_fetch_row($result))
to only accept rows that have values in them. Any ideas?

I have already tried using the following lines with no luck:

$result = mysql_query("SELECT * FROM {$table} WHERE * IS NOT NULL");
$result = mysql_query("SELECT COUNT(id) FROM {$table} where answer IS NOT NULL or answer <>'' ");
$result = mysql_query('SELECT COUNT(*) FROM {$table} WHERE answer <> ""');
$result = mysql_query("SELECT * FROM {$table} WHERE CHAR_LENGTH>0");
$result = mysql_query("SELECT * FROM {$table} WHERE val1 is <> '' ");
$result = mysql_query("SELECT * FROM {$table} WHERE col1 is <> '' ");

//// Outputs funky count in a separate table, but not the desired table with no empty cells
$result = mysql_query("SELECT COUNT(answer) FROM {$table} WHERE CHAR_LENGTH(answer)>0");
$result = mysql_query("SELECT COUNT(answer) FROM {$table} WHERE LENGTH(answer)>0");

$reslts = mysql_query("SELECT * FROM {$table}");
while($row = mysql_fetch_row($reslts)){
$empty_count = 0;
$count = count($row);
for($i = 0; $i < $count; $i++)
if($row[$i] === '' || $row[$i] === 'NULL')
$empty_count++;
$result = ($count);
}


So Thanks To Paul Spiegal for helping with this PHP function that can output any MySQL Table into a HTML Table to be displayed on a website... The working function is as follows, just change the values for the variables to access any MySQL data:

function getTABLE(){
$db_host = 'www.host.com';
$db_user = 'user1';
$db_pwd = 'password';
$database = 'testdb';
$table = 'MyTable';

if (!mysql_connect($db_host, $db_user, $db_pwd))
die("Can't connect to database");
if (!mysql_select_db($database))
die("Can't select database");

//// sending query
$result = mysql_query("SELECT * FROM {$table}");
if (!$result) {
die("Query to show fields from table failed");
}
$fields_num = mysql_num_fields($result);

echo "<h3><center>Table: {$table}</h3>";
echo "<table border='1'><tr>";

//// printing table headers
for($i=0; $i<$fields_num; $i++){
$field = mysql_fetch_field($result);
echo "<td>{$field->name}</td>";
}

//// printing table rows
while($row = mysql_fetch_row($result)){
echo "<tr>";
if (strlen(implode('', $row )) == 0) {
continue;
}else {
foreach($row as $cell)
echo "<td>$cell</td>";
echo "</tr>";
}
}
print "</TABLE>";
mysql_close();
}

print getTABLE();

Answer

Using implode() function, you can combine all cells into one string. If that string is empty, you skip printing that row.

while($row = mysql_fetch_row($result)){
    if (strlen(implode('', $row)) == 0) {
        continue; // skip this empty row
    } else {
        // TODO: print this row
    }
}