sql query -> html table ?!

hi,

i need a small function that executes a mysql-query and returns the result in a html table. has anyone this done before and could send me the code please; because i only did this in asp a few years ago and dont wanna repeat all my errors and bugs...?

 

http://www.google.co.uk/search?q=php+sql+query+html+table

http://mail.python.org/pipermail/python-list/2001-October/067587.html

http://www.zend.com/zend/tut/odbc.php?article=odbc&kind=t&id=6867&open=1&anc=0&view=1

And more...

 

<table>
<?php
$result = MYSQL_QUERY("SELECT * FROM tablename WHERE xxx='$xxxxxx'");
if (!$row=mysql_fetch_array($result))
exit();
echo"<tr><td>$row[column1]</td><td>$row[column2]</td></tr>";
?>
</table>

PHP:

or if you need to loop through the results:

<table>
<?php
$SQLcommand = "SELECT * FROM tablename WHERE xxx='xxxxx'";
$SQLresult = MYSQL_QUERY($SQLcommand);

while($row=mysql_fetch_array($SQLresult)) 
{
echo"<tr><td>$row[column1]</td><td>$row[column2]</td></tr>";
}
?>
</table>

PHP:

 

If you insert the following code after line 4 in mad4's second example, you will get a header row with the column names. I'm fairly in-experienced, so, there may be an easier way, if someone has one, feel free to add it here.

$NumFields = mysql_num_fields($SQLresult);
echo "<tr>";
for ($i=0; $i < $NumFields; $i++)
{ 
	echo "<th>" . mysql_field_name($SQLresult, $i) . "</th>"; 
}
echo "</tr>";

PHP:

 

Well...I got bored, and wrote this function for you. All you need to do is pass the function a query and it will return a formatted table.

If anyone has any comments, or improvements, let me know.

function SQLResultTable($Query)
{
	$link = mysql_connect(HOST, USER, PASS) or die('Could not connect: ' . mysql_error()); 		//build MySQL Link
	mysql_select_db(DB) or die('Could not select database');		//select database
	$Table = "";  //initialize table variable
	
	$Table.= "<table border='1' style=\"border-collapse: collapse;\">"; //Open HTML Table
	
	$Result = mysql_query($Query); //Execute the query
	if(mysql_error())
	{
		$Table.= "<tr><td>MySQL ERROR: " . mysql_error() . "</td></tr>";
	}
	else
	{
		//Header Row with Field Names
		$NumFields = mysql_num_fields($Result);
		$Table.= "<tr style=\"background-color: #000066; color: #FFFFFF;\">";
		for ($i=0; $i < $NumFields; $i++)
		{     
			$Table.= "<th>" . mysql_field_name($Result, $i) . "</th>"; 
		}
		$Table.= "</tr>";
	
		//Loop thru results
		$RowCt = 0; //Row Counter
		while($Row = mysql_fetch_assoc($Result))
		{
			//Alternate colors for rows
			if($RowCt++ % 2 == 0) $Style = "background-color: #00CCCC;";
			else $Style = "background-color: #0099CC;";
			
			$Table.= "<tr style=\"$Style\">";
			//Loop thru each field
			foreach($Row as $field => $value)
			{
				$Table.= "<td>$value</td>";
			}
			$Table.= "</tr>";
		}
		$Table.= "<tr style=\"background-color: #000066; color: #FFFFFF;\"><td colspan='$NumFields'>Query Returned " . mysql_num_rows($Result) . " records</td></tr>";
	}
	$Table.= "</table>";
	
	return $Table;
}

//Call the function like this:
echo SQLResultTable("SELECT * FROM TABLE");

PHP:

 

ip076, nice class - how would you add pagination to your class?

 

I think you are three (3) years late.