Printing database info to php table

[drumdogg]

Hi Guys,

I have a database with around 16 tables within it. I have a search function that will search through each table. There are no relationships between any of the tables. Currently i am printing the database contents to a php table as follows;

echo "<table class='searchTable' border='1';  <tr>"; 
for($i = 0; $i < mysql_num_fields($result); $i++) {     
$field_info = mysql_fetch_field($result, $i);     
echo "<th>{$field_info->name}</th>"; }  

// Print the data 

while($row = mysql_fetch_row($result)) {     
echo "<tr>";     
	foreach($row as $_column) {         
		echo "<td>{$_column}</td>";     }     
		echo "</tr>"; 
		}  
		
		echo "</table>";

By doing it this way prints all the columns as there are in the database.

So what i am really trying to say is that, is there a way i can print the contents of the database to a table and be able to move the columns about. Eg. Would like to print the columns in a particular order unlike the database that they are stored in. Also by using the code above means that i can use one php page can be used instead of one page per database table. Any way i can still do this with one page??

I cant see a way but thought i would ask first.

Thanks

 

[Oz!asM!dwinter]

If you know the field (column) names of the table you can access the fields in a record directly using mysql_fetch_assoc (http://php.net/manual/en/function.mysql-fetch-assoc.php)

a) create an array of fields name you want to display in the table, where the order in the array determines the column order in the table

$tbody = '';
$fields = array( 'field1', 'field2', 'field3' );

$thead = "<tr>\n";
foreach( $fields as $field ){
		$thead .= "<th>$field</th>\n";
}
$thead .="</tr>\n";

$tbody = '';
while($row = mysql_fetch_assoc($result)) {     
	$tbody .= "<tr>\n";     
	foreach( $fields as $field ){
			$tbody .= "<td>{$row[ $field ]}</td>\n";
	}
	$tbody .= "</tr>\n"; 
}  
echo "<table>\n$thead$tbody</table>";

b) directly reference the columns you want

	echo "<tr>";     
	echo "<th>field3</th>";
	echo "<th>field1</th>";
	echo "<th>field2</th>";
	echo "</tr>"; 
while($row = mysql_fetch_assoc($result)) {     
	echo "<tr>";     
	echo "<td>{$row[ 'field3' ]}</td>";
	echo "<td>{$row[ 'field1' ]}</td>";
	echo "<td>{$row[ 'field2' ]}</td>";
	echo "</tr>"; 
}

 

[drumdogg]

Thanks for the reply

Would i be right in saying that that code will only work if the column names are all the same throughout all tables??

For example i have around 16 tables and all have different column names and different amount of columns.

 

[Oz!asM!dwinter]

Yes, if the different tables have different structures (fields) you will have to handle each database table individually, unless there are fields which are common to all the tables, and you only want to display these common fields.