[SOLVED] Working with Results of 3 Table Join

[oomwrtu]

Wasn't sure what to title this, and may I say thanks in advance to anyone that can help. I have three tables of data: Materials containing id and name, Outputs containing id and quantity, and Inputs containing output_id, input_id, quantity. I'm looking to have a table that displays all of the outputs along the top and all of the inputs along the left side so that there is a large table where it is easy to see how many inputs are needed for a certain amount of outputs. An example can be found: http://www.oomwrtu.com/example.txt

 

I've tried the following but I'm not really sure how to put the data in to the table format that I'm looking for...

 

SELECT
outputs.id AS output_id,
outputs.quantity AS output_quantity,
output_materials.name AS output_name,
inputs.input_id AS input_id,
inputs.quantity AS input_quantity,
input_materials.name AS input_name
FROM
outputs,
inputs,
materials AS output_materials,
materials AS input_materials
WHERE
outputs.id = inputs.output_id AND
outputs.id = output_materials.id AND
inputs.input_id = input_materials.id AND
outputs.type_id='1'

 

I end up getting all of the info that I need but I'm not sure how to process it. I could have done separate queries for each output to make it easier I suppose but if I have more than 10 outputs that will be a lot of individual queries. I'd also eventually like to include a price for each input and output and a cost calculation but I realize that that is dependent on the rest of the system working. Thanks again for your help.

[oomwrtu]

While it doesn't quite do everything yet, it gets there. Don't ask me how it works because it's pretty messy. However, if anyone has any tips on cleaning this mess up let me know:

 

<?php
$outputs = array();
$inputs = array();
foreach ($data as $line) {
array_push($outputs, array($line['output_id'],$line['output_name']));
array_push($inputs, array($line['input_id'],$line['input_name']));
}

$outputs = arrayUnique($outputs);
$inputs = arrayUnique($inputs);

$table = array();
$table[0][0] = " ";
$i = 1;
foreach ($inputs as $rowkey=>$rowvalue) {
$table[$i][0] = $rowvalue[0];
$i++;
}
foreach ($outputs as $column) {
array_push($table[0], $column[0]);
}
foreach ($table[0] as $findthiskey=>$findthisvalue) {
//for ($i=1; $i<count($table[0]); $i++) {
if (isset($findthisvalue) && $findthisvalue != " ") {
	$findthis = $table[0][$findthiskey]; // ouput_id
	$validinputs = array();
	foreach ($data as $testkey=>$testvalue) {
	 	if ($testvalue['output_id'] == $findthis) {
	 		array_push($validinputs, $data[$testkey]);
	 		$data[$testkey] == NULL;
	 	}
	}
	//echo "**************************************\r\n";
	foreach ($validinputs as $inputkey=>$inputvalue) { // go through each input
		foreach ($table as $inputrowkey=>$inputrowvalue) {
			if ($inputvalue['input_id'] == $inputrowvalue[0]){
				$inputrow = $inputrowkey;
			}
		}
		if ($inputrow) {
			//echo " [{$inputrow},{$findthiskey}]=" . $inputvalue['input_quantity'] . " \r\n";
			$table[$inputrow][$findthiskey] = $inputvalue['input_quantity'];
		}
	}
}
}

for ($i=0; $i<=count($inputs); $i++) {
for ($j=0; $j<=count($outputs); $j++) {
	if (!isset($table[$i][$j])) {
		$table[$i][$j] = " ";
	}
}
}

/////// table printer
echo "<table border=\"1\">\r\n";
for ($i=0; $i<=count($table); $i++) {
echo "\t<tr>\r\n";
for ($j=0; $j<=count($table[$i]); $j++) {
	echo "\t\t<td>" . $table[$i][$j] . "</td>\r\n";
}
echo "\t</tr>\r\n";
}
echo "</table>";
?>