populate specific no columns html table from mysql

6 columns of similar data required

[kiwibrit]

I need to populate an html table with similar groups of data repeated 6 times across the width. If I wanted just one column (with 2 sub columns in it)the this would do fine:

while($row = mysql_fetch_array($result)){
echo "<td>";
echo $row['foo'];
echo "</td><td>";
echo $row['foofoo'];
echo "</td></tr>\n";
echo "<tr>\n";
}
echo "<td>&nbsp;</td><td>&nbsp;</td></tr>\n";

I would get

foo1 foofoo1
foo2 foofoo2
foo3 foofoo3
foo4 foofoo4
foo5 foofoo5
foo6 foofoo6

etc

but what I want is

foo1 foofoo1 foo2 foofoo2 foo3 foofoo3 foo4 foofoo5 foo6 foofoo6 etc

Try as I might, I cant' do it, everything I try winds me up with:
foo1 foofoo1 foo1 foofoo1 foo1 foofoo1 foo1 foofoo1 foo1 foofoo1 foo1 foofoo1
foo2 foofoo2 foo2 foofoo2 foo2 foofoo2 foo2 foofoo2 foo2 foofoo2 foo2 foofoo2
etc.

I'd appreciate help getting my desired result.

[rocknbil]

This is where the fact that mysql_fetch_array serves up both the associative and indexed arrays comes in handy. It kind of relies on the field_names function here, it all works.

$field_list = field_names('your_table_name');
$num_fields = count($field_list);

$output_rows=null; 
while($row = mysql_fetch_array($result)){ 
 // 
 $output_rows .= "\n<tr>\n"; 
 for ($i=0;$i<$num_fields;$i++) { // array=zero based 
 // Don't leave empty TD's. IE hates them. 
 if (! $row[$i] or empty($row[$i])) { $row[$i] = '&nbsp;'; } 
 $output_rows .= "<td>" . $row[$i] . "</td>\n"; 
 } 
 $output_rows .= "<tr>\n"; 
} 
if ($output_rows) { 
 $final = ' 
 <table border="1"> 
 <th> 
 '; 
 for ($i=0;$i<$num_fields;$i++) { 
 $final .= "<td>" . $field_list[$i] . "</td>"; 
 } 
 $final .= " 
 </th> 
 $output_rows 
 </table> 
 "; 
} 
else { $final = "<p>No records were found.</p>"; } 
echo $final; 

And the function for the field names:

// Returns a list array of table field names. 
function field_names($table) { 
 if (! isset($table)) { die("No table in field names"); } 
 $fields = Array (); 
 $counter=0; 
 $result = mysql_query("show columns from $table"); 
 if (!$result) { die("Could not get table names: " . mysql_error()); } 
 while ($row = mysql_fetch_array($result,MYSQL_NUM)) { 
 $fields[$counter]=$row[0]; 
 $counter++; 
 } 
 return $fields; 
} 

What you get is a table with semantic headers if records are found and outputs an alternate if not found. Echo once. May have typos but this will do what you want.

[kiwibrit]

Hmm. Thanks. I think I can see how that should work, and it looks elegant - but I am getting an error. The function definition seems OK - and when I then run as a test:

$field_list = field_names($table);
$num_fields = count($field_list);
echo $num_fields;

I get the expected result - so the definition appears good.

But, when I run

while($row = mysql_fetch_array($result)){ 
 // 
 $output_rows .= "\n<tr>\n"; 
 for ($i=0;$i<$num_fields;$i++) { // array=zero based 
 // Don't leave empty TD's. IE hates them. 
 if (! $row[$i] or empty($row[$i])) { $row[$i] = '&nbsp;'; } 
 $output_rows .= "<td>" . $row[$i] . "</td>\n"; 
 } 
 $output_rows .= "<tr>\n"; 
} 
if ($output_rows) { 
 $final = ' 
 <table border="1"> 
 <th> 
 '; 
 for ($i=0;$i<$num_fields;$i++) { 
 $final .= "<td>" . $field_list[$i] . "</td>"; 
 } 
 $final .= " 
 </th> 
 $output_rows 
 </table> 
 "; 
} 
else { $final = "<p>No records were found.</p>"; } 
echo $final;

I get the error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource .....

No records were found.

Bearing in mind the definition seems to have been proved correct, I don't understand that. Any thoughts?

[kiwibrit]

Hang fire on that - I think I am getting it sorted - I'll report back when I have a result, or have a further problem.

[kiwibrit]

What I got was a table with the first row consisting of

<th><td>name-a</td><td>name-b</td></th>

and thereafter perfectly coded rows. Resulted in Firefox in a table where the heading row was misaligned from the main body of the table.
The requirement has changed a bit since I first posted - I'll raise another thread if I find I can't resolve what I am doing on my own.

Many thanks for your advice - I learned some valuable stuff researching your method

[rocknbil]

Right, on the error, guessing you figured out you needed to execute the query before the while loop . . . . as for this,

<th><td>name-a</td><td>name-b</td></th>

You can do any number of things to make it more elegant. The first, of course, is to use "better" field names.

first_name
last_name

And in your get field names function, nix the underscores, convert to ucase, style the TH and you have a perfect table head.

The other more clunky way is to build and maintain arrays associating field names with output values.

'name-a' => 'First Name',
'name-b' => 'Last Name',