php w mysql - joining multiple rows with one.
Posted: Mon Aug 23, 2004 10:50 am
Hi fellas.
I am trying to make a really fast join of data from my clumsy table setup. Data comes to me over the Internet in an XML-file in a way that justifies this setup (but I am ready to rewrite it, if needed).
Here it is:
"parent" table contains a column i have called intSetID.
child tables (I have three of them) also have intSetID, so I can join with those columns as an ON condition.
The relation (described with an Array tree)is kind of like this:
That is how I WAN'T data to be returned.
Is it possible, with mysql 4.0.20, to make some sort of clever join, to make mysql return data that way, or in a way that provides similar access to the data?
I guess not, since subqueries come from version 4.1+.
So I went on Selecting data into PHP arrays.
This way, we need an extra query for every child table we want to join with, for each row of the parent table. It sums up to
(rows_in_parent_table * tables_to_join_with + 1) mysql queries...
That's 101 queries, if I choose to select 50 parent rows on the web page. And this is meant to run on a high load start page...
I am posting because I think this is a bad solution and I don't know what would be fastest.
While I hope for one of you angels to save me, I will try to investigate whether it is possible through concatenation. (But that's not what I want. I wan't to be able to quickly and nicely distinguist between the child rows returned.)
Thanks
/Jonas
I am trying to make a really fast join of data from my clumsy table setup. Data comes to me over the Internet in an XML-file in a way that justifies this setup (but I am ready to rewrite it, if needed).
Here it is:
"parent" table contains a column i have called intSetID.
child tables (I have three of them) also have intSetID, so I can join with those columns as an ON condition.
The relation (described with an Array tree)is kind of like this:
Code: Select all
<?php
$row_in_parent_table = Array(
data_from_parent_table_1 =>'1',
data_from_parent_table_2 =>'2',
data_from_parent_table_3 =>'3',
results => Array(
data_from_child_table_1 =>'data1',
data_from_child_table_2 =>'data2',
data_from_child_table_3 =>'data3'
)
)?>Is it possible, with mysql 4.0.20, to make some sort of clever join, to make mysql return data that way, or in a way that provides similar access to the data?
I guess not, since subqueries come from version 4.1+.
So I went on Selecting data into PHP arrays.
This way, we need an extra query for every child table we want to join with, for each row of the parent table. It sums up to
(rows_in_parent_table * tables_to_join_with + 1) mysql queries...
That's 101 queries, if I choose to select 50 parent rows on the web page. And this is meant to run on a high load start page...
I am posting because I think this is a bad solution and I don't know what would be fastest.
While I hope for one of you angels to save me, I will try to investigate whether it is possible through concatenation. (But that's not what I want. I wan't to be able to quickly and nicely distinguist between the child rows returned.)
Thanks
/Jonas