results into matrix using php?

[Guest_imported]

I'm new to programming, so any help would be much appreciated.
I've been trying to work out how to insert results from a database query into a two-dimensional matrix using php.
We used a star schema for our datawarehouse. Thus we have a price fact table and we need to perform aggregate functions on this table. This part is relatively simple.
I've managed to connect to the oracle database and retrieve the data but only display the results in a normal table. What I'd like to do is display the results in a matrix so that I can avoid repeating fields. For instance if there are three variables:

var1 = Car_Make
var2 = Area
var3 = Avg_Price

I would like to display the resulting Car_Make on the x axis, Area on the y axis, and Avg_Price data as the associated value to both the Car_Make and Area in the appropriate cell eg:

Area Car_Make

Ford Holden N......

Boston 3500 6700

Seaview 2000 3400


M.....


If anyone has any suggestion or can point me in the right direction I'd really appreciate it.

Regards
Nhu'ron

 

[Broccoli2]

Nhu'ron

What you're asking for is what some programmers have called a 'crosstab'. Here are a few pointers which might help. (replace 'mysql' with the appropriate function for the database you're using)

You need two SQL calls. SQL 1 retrieves the column data, and SQL 2 retrieves the rows. We create SQL 2 from the results of SQL 1. Here goes...

$t=Array();
$t[Title]=Array("My Matrix");
$sql1 = "SELECT DISTINCT Area FROM yourtable";
$sql2 = "SELECT Car_Make";
if (!$r=mysql_query($sql1)) {error_report($sql1);} else {
  while ($row=mysql_fetch_assoc($r)) {
    $t[Title][]=$row[Area];
    $sql2 .= ", SUM(IF(Area='".$row[Area]."',1,0)) AS '".$row[Area]."'";
  }
}
$sql2 .= " GROUP BY Car_Make";
if (!$r=mysql_query($sql2)) {error_report($sql2);} else {
  while ($row=mysql_fetch_assoc($r)) $t[]=$row;
}

print_r($t);

The result is a matrix of the data in $t. This example is a simple tally, but you can change the '1' in the IF statement to any SQL variable.

A more in-depth explanation of this technique is available at

http://mysql.com/articles/wizard/index.html

Hope this helps,

-Rob