Hide empty columns in MySQL/HTML table

[dsol828]

Hello everyone!

I'm not sure I'm posting this in the right place, so feel free to move as needed.

I've got a mysql query being echoed into an HTML table (see below).

What I'm having trouble with is if a column (field) is empty, I'd like that column and header to not appear in the HTML table.

<?php 
$result = mysql_query("SELECT Team,s1,s2,s3,s4,s5,s6,sT FROM Period WHERE GameID = $GameID");

echo "<table>
<tr>
<th>Team</th>
<th width='25'>1</th>
<th width='25'>2</th>
<th width='25'>3</th>
<th width='25'>4</th>
<th width='25'>5</th>
<th width='25'>6</th>
<th width='25'>T</th>
</tr>";

while($row = mysql_fetch_array($result))
 {
 echo "<tr>";
 echo "<td>" . $row['Team'] . "</td>";
 echo "<td>" . $row['s1'] . "</td>";
 echo "<td>" . $row['s2'] . "</td>";
 echo "<td>" . $row['s3'] . "</td>";
 echo "<td>" . $row['s4'] . "</td>";
 echo "<td>" . $row['s5'] . "</td>";
 echo "<td>" . $row['s6'] . "</td>";
 echo "<td>" . $row['sT'] . "</td>";
 echo "</tr>";
 }
echo "</table>";
?>

A quick word about the data going in the table. The query will only return two rows of data and the two rows have identical length. For example, if row one has a value for s1,s2 and s3...so will row two. This guarantees that the entire s4,s5,s6 column will be empty. sT is a totals row and will always have a value.

Thanks in advance for your help!

[penders]

Instead of echo'ing the HTML directly save it in a variable and keep track of which columns have data. Don't record any columns/fields that have no data. After having gathered your data, step through the fields that contain data (saved previously) to build your column headings.

Something like...

$result = mysql_query("SELECT Team,s1,s2,s3,s4,s5,s6,sT FROM Period WHERE GameID = $GameID"); 
// Array of all fields 'field-name' => 'table-column-name' 
$allFields = array ( 
  'Team' => 'Team', 
  's1' => '1', 
  's2' => '2', 
  's3' => '3', 
  's4' => '4', 
  's5' => '5', 
  's6' => '6', 
  'sT' => 'T', 
); 
// Used to store which fields have values 
$usedFields = array (); 
// Construct body of table 
$htmBody = ''; 
while($row = mysql_fetch_array($result)) 
  $htmBody .= '<tr>'; 
  foreach ($allFields as $field => $colName) { 
    // Check if not empty OR previously not empty 
    // - Assume 'empty' is an empty string in this example 
    if (($row[$field] != '') || isset($usedFields[$field])) { 
      $usedFields[$field] = true; 
      $htmBody .= '<td>'.$row[$field].'</td>'; 
    } 
  } 
  $htmBody .= '</tr>'; 
} 
// Build table header 
// - But only those columns that contained data 
$htmHead = '<tr>'; 
foreach ($usedFields as $field => $dummy) { 
  $htmHead .= '<th>'.$allFields[$field].'</th>'; 
} 
$htmHead .= '</tr>'; 
// Complete Table 
$htmTable = '<table border="1">'.$htmHead.$htmBody.'</table>'; 
echo $htmTable;

[penders]

There is a slight problem with the above code. But I don't think it will affect you because you say that both rows returned will have the same number of columns anyway. But if the first row had an empty column but that column was populated in the second row (in which case it is a 'populated column') then the first row will be missing a table-cell and the table will be rendered incorrectly. If the second row had an empty column, but was populated in the first, it would be OK. To correct this in the first case you would probably need to make 2 passes of the data. The first pass purely checking for populated data.

[rocknbil]

^ ^ ^ Precisely, a better approach might be

echo "<td>" . (empty($row['s1']))?'-':$row['s1'] . "</td>";

Or whatever you want for an "empty cell"

echo "<td>" . (empty($row['s1']))?'&nbsp;':$row['s1'] . "</td>";

[dsol828]

Thanks guys,

I actually think I'll go with another solution. I'll post it here for reference.

<?php
$result = mysql_query("SELECT * FROM Period WHERE GameID = $GameID");
$row = mysql_fetch_array($result);

echo "<table border='1'>
<tr>
<th>Team</th>";
 if ($row['s1'] != 0){echo "<th width='25'>1</th>";}
 if ($row['s2'] != 0){echo "<th width='25'>2</th>";}
 if ($row['s3'] != 0){echo "<th width='25'>3</th>";}
 if ($row['s4'] != 0){echo "<th width='25'>4</th>";}
 if ($row['s5'] != 0){echo "<th width='25'>5</th>";}
 if ($row['s6'] != 0){echo "<th width='25'>6</th>";}
echo " <th width='25'>T</th></tr>";

$result = mysql_query("SELECT * FROM customers WHERE GameID = $GameID");
while($row = mysql_fetch_array($result))
{
 echo "<tr>";
 echo "<td>" . $row['Team'] . "</td>";
 if ($row['s1'] != 0){echo "<td>" . $row['s1'] . "</td>";}
 if ($row['s2'] != 0){echo "<td>" . $row['s2'] . "</td>";}
 if ($row['s3'] != 0){echo "<td>" . $row['s3'] . "</td>";}
 if ($row['s4'] != 0){echo "<td>" . $row['s4'] . "</td>";}
 if ($row['s5'] != 0){echo "<td>" . $row['s5'] . "</td>";}
 if ($row['s6'] != 0){echo "<td>" . $row['s6'] . "</td>";}
 echo "<td>" . $row['sT'] . "</td>";
 }
 echo "</tr></table>";
?>

[rocknbil]

You're missing the point I think . . . say you have 5 rows, and only one has "empty cells". You get

s1|s2|s3|s4|s5|s6
s1|s2|s3|s4|s5|s6
s1|s3|s5|s6
s1|s2|s3|s4|s5|s6
s1|s2|s3|s4|s5|s6

Which will likely make for some very broken output (in your HTML table.)

[dsol828]

I must have not been clear enough in my description.

The table created will always be two rows, and the two rows will always have the same number of columns. 1-3 and Total guaranteed. 4-5-6 are sometimes blank, but the two rows will always be the same "length".

For my needs, the code I posted works just fine.