[SOLVED] Creating a table on the fly with mySQL results

[dmccabe]

Ok I will explain this the best I can and I hope someone understands and can help.

 

I have 3 tables

 

tbl_Vehicles

-REG

-STATUS_ID

-LOCATION_ID

 

tbl_status

-ID

-NAME

 

tbl_location

-ID

-NAME

 

I want to create a table like this:

 

/                \ LOCATION1 - LOCATION2 - LOCATION3

STATUS1            1                4                    2

STATUS2            3                7                    9

STATUS3            9                2                    7

 

I want the data in the table to be generated on the fly by reading the names of the locations and status' for the header row and the left column, then I want to automatically fill the values in as well, by reading for the vehicle table the number of vehicles at location1 with a status of status1.

 

I cant show you the code I have so far, or rather I can but it just isnt even close to getting it right.

 

I know the code for the selecting the data from the tables, but I am stuck on how to put the results in the right places.

[dmccabe]

lol as always, after asking I sorted it

 

$status_query = "SELECT ID,NAME FROM `tbl_status`";
$location_query = "SELECT ID,NAME FROM `tbl_locations`";

$status_result = mysql_query($status_query);
$location_result = mysql_query($location_query);

echo "<table><tr><th> </th>";
while ($row = mysql_fetch_array($location_result)) {
	echo "<th>{$row['NAME']}</th>";
}
echo "<th>Totals</th></tr>";
while ($status= mysql_fetch_array($status_result)) {
	echo "<tr><td>{$status['NAME']} </td>";
	$total = 0;
	$location_result = mysql_query($location_query);

	while ($location = mysql_fetch_array($location_result)) {
		$query = "SELECT * from `tbl_vehicles` WHERE `STATUS_ID` = '{$status['ID']}' AND `LOCATION_ID` = '{$location['ID']}'";
		$result = mysql_query($query) or die(mysql_error());
		$count = mysql_num_rows($result);
		$total = $total + $count;
		echo "<td><a href='http://intranet/dev/temp/remarketing/reporting.php?LOCATION_ID={$location['ID']}&STATUS_ID={$status['ID']}&submit=Search'>$count</a></td>";

	}
	echo "<td>$total</td></tr>";
}
echo "</table>";