php w mysql - joining multiple rows with one.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

php w mysql - joining multiple rows with one.

Post by Heavy »

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:

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'
   )
)?>
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... :cry:

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
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

Gotta change that avatar. I really do look angry...
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post by liljester »

im not too sure what your asking... could you post an expample the tables and a row the way you want it returned?
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

I believe I did...

I found a way to select with one query per table, so that the number of queries are (kind of) minimized.

Then I loop through every row returned from the parent table and insert arrays of the results from every child table, arranged so that they are accessible through intSetID, into every parent row.
This way, I get what I want: A tree like php array representation of the content in the database.

I would still prefer a technically better/faster solution but right now I'll manage with the speed achieved.

I am competing with the performace of libxslt (sablotron), and right now, it seems (benchmarked with apache bench - ab2) that my clumsy solution is 5.3 times faster. Good enough right now.

/Jonas
Post Reply