creating a query to return an x/y matrice of unknown x and y length

[Max Tobiasen]

I'm trying to find a way to store a number of x/y matrices and their content in a SQL table, and creating a single query that will return one of these matrices. The reason for wanting to do this is that I am doing an application where I need to have an unknown number of HTML tables with an unknown number of rows and columns.

I have three tables in a mySQL db: list, labels, and content.

The idea is to be able to create a matrix with entries from list along one axis, entries from labels along another axis, and entries from content as the intersection of the two. The content table has a field that corresponds to a field in labels, and a field that corresponds to a field in list so that I am able to place it correctly in the matrix.

Like so:

-----------|-------label(1)--------|-----------label(2)----|---------label(n)-----
-----------+-------------------------+-------------------------+----------------------
list(1)----| content(list1, label1) | content(list1, label2) | content(list1, labeln)
----------+-------------------------+-------------------------+----------------------
list(2)----| content(list2, label1) | content(list2, label2) | content(list2, labeln)
----------+-------------------------+-------------------------+----------------------
list(n)----| content(listn, label1) | content(listn, label2) | content(listn, labeln)

There must be a way of doing this in a single SQL query, but I can't quite figure out how.

Currently I have this PHP code to do it, but it is quite a mess, lots of unnecessary loops, and gets problematic once I want to sort the query based on the content table.

(simplified for easier reading)

//first get the labels in an array
$q = "SELECT column_name, type, label
FROM label
WHERE module_id = $currmod
ORDER BY column_number
";
$result = mysql_query($q,$conn);


$counter = 1;
while($row = mysql_fetch_array($result)){
$_SESSION['list']['column_name'][$counter] = $row['column_name'];
$_SESSION['list']['type'][$counter] = $row['type'];
$_SESSION['list']['label'][$counter] = $row['label'];
$_SESSION['list']['length'] = $counter;
$counter ++;
}



$q = "SELECT name, id FROM list Where moduleid = $moduleid";
$result = mysql_query($q,$conn) or die(mysql_error());
$c = 1;
while($row = mysql_fetch_array($result)){
$tablecolumn = 0;
while($c<=$_SESSION['list']['length']) { // for each record in column_counter array find out which content post matches
$tablecolumn = $tablecolumn++;
$label = $_SESSION['list']['label'][$c];
$q1 = "SELECT * FROM content Where list_id = '$row[id]' and label ='$label'"; // get items that correspons to current row
$result1 = mysql_query($q1,$conn) or die(mysql_error()) ;
while($array = mysql_fetch_array($result1)){
$list['list'][$c][$tablecolumn] = array('1', $array[content_date], 0) ;
}
$c++;
}
$list['list']['columns'] = $tablecolumn;
}


So the question is: can I create the array in a single SQL query, and if so how?

I don't necessarily need a full answer, just an idea of how to do this properly. I have no problems trying to think for myself :-)

I'm quite new to both PHP and MYSQL so forgive me if my code is a mess, or if this is a stupid question...



Edited 3 time(s). Last edit at 03/07/2008 05:04PM by Max Tobiasen.