i'm having problems with storing interactions into a mysql db, let me explain what i see under interactions

i have the following tables:
- blocks (stores about 5k records)
- interactions

the blocks is just a simple list of names and there ids
- id (autoincrement)
- name

the interactions is a list of interactions between 2 blocks
- id (autoincrement)
- blockid_1
- blockid_2
- value

Now i need to retrieve this list and display it as a table
column headers and the first row is a list off all blocks, and the remaining rows will display the interactions (if there is one) between the column and the row header.

At the moment i have some performance issues when i try to display this table, for every interaction i do a separated sql querry, this means a lot of queerys and a verry big load time.

is there a way to retrieve all this info at once? any suggestions on how i can speed things up?

please give a few sample rows, then show what the query result set should look like for those rows

blocks
1 - b1
2 - b2
3 - b3
4 - b4

interactions
1 - 1 - 2 - int1
2 - 1 - 3 - int4
3 - 1 - 4 - int5
4 - 2 - 4 - int6

Resultset
b1 b2 b3 b4
b1 int1 Int4 int5
b2 int6
b3
b4

bascally i need an easy way to build a table that looks like the result, would be cool if this could be done in 1 querry b ut i doubt it ....

that's what i was afraid of

i don't think any query you could possibly write will be able to handle a crosstab result set with 5k columns

and anyhow, i feel it is wrong to do this with SQL -- creating data layouts should be the responsibility of the application

get the data out quickly, easily, with a single join query, not a query inside a loop


Edit: added link

hmm, is it possible to do this with dynamic columns headers?

because in the blocks table there can be a lot off culumns, all cross table joins i can find are with static columns ....

i have something thats working,

but i get some data 2 or 3 times ...

select n2.id as id, n2.name as name,(COUNT(n1.id)-1) as depth, if( (n2.lft+1) = n2.rgt, 0, 1) as haskids,
GROUP_CONCAT(distinct n1.id order by n1.lft SEPARATOR '-') as pht, group_concat(d.status), group_concat(d.owner)
, group_concat(if ( i.block_id_1 = n2.id, i.block_id_2, i.block_id_1)) as top
FROM block AS n1
INNER JOIN block n2 ON n2.lft BETWEEN n1.lft AND n1.rgt
LEFT JOIN interaction AS i ON n2.id = i.block_id_1 OR n2.id = i.block_id_2
LEFT JOIN interaction_data AS d ON i.id = d.interaction_id
WHERE n2.lft BETWEEN (SELECT lft FROM block WHERE id = 1) AND (SELECT rgt FROM block WHERE id = 1) AND n2.build_id <= 1000 OR n2.build_id = 1000
GROUP BY n2.id ORDER BY n2.lft

the group_concats on all fields from the interaction_data table are seen 2,3 or 4 times any idea why?

EDIT: nevermind see my other post about group by