[phpesp-dev] Help with proposed cross tabulation feature

[angek <ang...@ip...>]

Hi Guys,

	I need some help with a feature that I'm trying to apply to phpESP.
I've finished creating the GUI to the undocumented cross analysis
feature of phpESP but would like to enhance it to also provide cross
tabulation capabilities.
	The attached file should make it clear where I'm heading with this.
You'll see that on the left hand side of the attached file there exists
a radio button next to each question and a check box for each of the
choice ids.Selecting a question's radio button followed by one or more
choice ids for the selected question the existing cross analysis feature
will return the entire result set (exactly as seen for survey results)
but based on the selected criteria.This works fine and I'm in the
process of changing it so that you can cross analyse on more than one
question which can't be done currently.

My dilema is with the 2 radio buttons which are on the right hand side
of the question. (just in case you are wondering about the questions
without these radio buttons remember that cross analysis currently works
only on questions with a tid of 1, 4, 5 and 6).
The radio button in the red column indicates the rows for the cross
tabulation and the radio button in the blue column indicates the columns
for the cross tabulation.

Suppose I select the red radio button for question3 (see attachement)
and the blue radio button for question 1 then the resulting page would
look like this (with the ability to display these results in a number
of different graph types):

-------------------------------------------------------------------
|				   |	Male  |  Female | Totals  |
-------------------------------------------------------------------
|	<35K			   |	2     |    5    |    7    |
-------------------------------------------------------------------
|	36-45K			   |	5     |	   6    |    11   |
-------------------------------------------------------------------
|	46-55K			   |    8     |	   5    |    13   |
-------------------------------------------------------------------
|	56-65K		           |    3     |    1    |     4   |
-------------------------------------------------------------------	
|	>65K			   |    4     |    4    |     8   |
-------------------------------------------------------------------
|	TOTALS			   |    22    |    21   |    43   |
-------------------------------------------------------------------

My problem is as follows:

What's the most appropriate sql statement(s) that will return the result
set (data) as illustated above? 
I know the result set can be returned using subqueries but since mysql
doesn't support these I'm a little stuck as to the most appropriate /
desirable way to do it.

Any help would be greatly appreciated.


Kon