Generating dynamic html table

[steveha]

Greetings and salutations,

I have a php code issue and I can not think of how to code it. I need some direction.

I have 3 mySQL tables. That contain the following:

TableName: Customer
Fields: ID, Customer Name

TableName: Patches
Fields: ID, Patch Build Tag

TableName: PatchLink
Fields ID, CustomerID, PatchesID

The PatchLink table is a linkage table to reference data in both the patches and customer tables. I need to code a report that looks something like this:

<table>
<tr>
<td></td><td>Patch_build_tag1</td><td>Patch_build_tag2<td>
</tr>
<tr>
<td>Customer1</td><td>X</td><td></td>
</tr>
<tr>
<td>Customer2</td><td></td><td>X</td>
</tr>
<tr>
<td>Customer3</td><td>X</td><td>X</td>
</tr>
</table>

I have some good experience with PHP and pulling data out of/putting into mysql databases, and I can code, although not great at PHP OOP. I just dont know where to start with something like this. I can do a while loop to pull the top row of data from the patches table, and another while loop to pull the customer names from the customer table, but how do I set up the matrix to show which patch is assigned to which customer? I dont have a problem listing the data, but want to create the matrix so it is extremely easy to understand.

[Weedpacket]

Thought I'd make a first cut:

$patches = /* array of rows resulting from "
	Select Distinct
		Customer.'Customer Name',
	,	Patches.'Patch Build Tag'
	from
		Customer, Patches, PatchLink
	where
		PatchLink.CustomerID=Customer.ID
	and	PatchLink.PatchesID=Patches.ID;"*/

$patch_build_tags = $customer_names = array();
$matrix = array();
foreach($patches as $patch)
{
	$patch_build_tags[] = $patch['Patch Build Tag'];
	$customer_names[] = $patch['Customer Name'];
	$matrix[$patch['Customer Name']][] = $patch['Patch Build Tag'];
}
$patch_build_tags = array_unique($patch_build_tags);
$customer_names = array_unique($customer_names);
sort($patch_build_tags);
sort($customer_names);

echo '<table>';
echo '<tr><th></th><th>'.join('</th><th>', $patch_build_tags).'</th></tr>';
foreach($customer_names as $customer_name)
{
	echo '<th>'.$customer_name.'</th>';
	foreach($patch_build_tags as $build_tag)
	{
		echo '<td>';
		if(isset($matrix[$customer_name][$build_tag]))
		{
			echo 'X';
		}
		echo '</td>';
	}
}
echo '</table>';

[Weedpacket]

Thought I'd make a first cut:

$patches = /* array of rows resulting from "
	Select Distinct
		Customer.'Customer Name',
	,	Patches.'Patch Build Tag'
	from
		Customer, Patches, PatchLink
	where
		PatchLink.CustomerID=Customer.ID
	and	PatchLink.PatchesID=Patches.ID;"*/

$patch_build_tags = $customer_names = array();
$matrix = array();
foreach($patches as $patch)
{
	$patch_build_tags[] = $patch['Patch Build Tag'];
	$customer_names[] = $patch['Customer Name'];
	$matrix[$patch['Customer Name']][] = $patch['Patch Build Tag'];
}
$patch_build_tags = array_unique($patch_build_tags);
$customer_names = array_unique($customer_names);
sort($patch_build_tags);
sort($customer_names);

echo '<table>';
echo '<tr><th></th><th>'.join('</th><th>', $patch_build_tags).'</th></tr>';
foreach($customer_names as $customer_name)
{
	echo '<tr>';
	echo '<th>'.$customer_name.'</th>';
	foreach($patch_build_tags as $build_tag)
	{
		echo '<td>';
		if(isset($matrix[$customer_name][$build_tag]))
		{
			echo 'X';
		}
		echo '</td>';
	}
	echo '</tr>';
}
echo '</table>';