Retreive child id from multilevel tree structure in one table by selecting parent.

Dev Shed Forums Sponsor:

February 6th, 2004, 08:59 AM

Gurt

Contributing User

Join Date: Dec 2003

Location: Amsterdam, NL

Posts: 389

Time spent in forums: 2 Days 13 h 59 m 20 sec
Reputation Power: 7

Retreive child id from multilevel tree structure in one table by selecting parent.

Apologies for the cryptic subject but here's my problem. I have 1 table called "location" with a standard tree structure like

| root_id | parent_id | name |

The table holds continents, countries and, depending on which country, provinces.
The root_id of the lowest level is used in another table that holds (for ease I use this example) fishingspot information. A fishing spot can be listed in a country, or if the country is very big, in a province.
People need to be able to get a list of fishingspots by either selecting a continent, a country or a province, from 1 dropdown list. If they select a continent, all the fishing spots should be shown that "are in the countries, that are in the continent" of which the name was selected. If the spot is in a big country, then the level of provinces is added, so all sport that "are in the provinces, that are in the countries, that are in the continent".
But people could also select a province, or a country.

The highest level (continents) have a parent_id that is "0".

How can I make a select statement, or use a combination of php and mysql that does this (and i'll describe step by step):
- Select the "root_id" from the table "locations" where the name is "name from dropdown list".
- If the "root_id" exists in the "parent_id" column (which means that it is a branch in the structure and not a leaf), select the "root_id" (can be more that one) where the "parent_id" is the same as that "root_id".
- If those "root_id" exist in the "parent_id" column (which means that it still is a branch in the structure and not a leaf)
- repeat until the "root_id" (can be more than one) dont exist in the "parent_id" column (meaning that finally we reached the lowest level) use those root_id's to get the records from the fishingspots table.

Did that make sense? I hope something like this is possible.

Oh and I read somewhere (searched my butt off, but can't find it anymore) that mysql can create two temporary tables so that the tree structure does not point to itself in one table (?) Would that be a solution?

Thanks in advance for any suggestions.
Gurt

__________________
If "Thank you" is too much effort to type, then a simple "ty" will do as well!

Please post back your solution, even if you found it yourself! Don't let others munch on your problem while it's not your problem anymore.
OK....I haven't actually RTFM, but more sort of 'looked into' TFM. Does it show? My pet-project: www.divestart.com

February 6th, 2004, 11:02 AM

Gurt

Contributing User

Join Date: Dec 2003

Location: Amsterdam, NL

Posts: 389

Time spent in forums: 2 Days 13 h 59 m 20 sec
Reputation Power: 7

Ok, I've been thinking about this and I've come up with this:

PHP Code:


		
			
 $spotsquery = " SELECT spotname, spotdescription FROM fishingspot f, locations l WHERE ";



$q1 = mysql_query (" SELECT root_id FROM locations WHERE  name = '$_POST['name']' ")



$q2 = mysql_query (" SELECT root_id FROM locations WHERE parent_id = '$q1' ")

if ( mysql_num_rows( $q2 ) >= 1 ) {

    $arrayq2 = mysql_fetch_array( $q2 )

    foreach ( $arrayq2 as $rootidq2 ) {

        $q3 = mysql_query (" SELECT root_id FROM locations WHERE parent_id = '$rootidq2' ")    

        }

        if ( mysql_num_rows( $q3 ) >= 1 ) {

        $arrayq3 = mysql_fetch_array( $q3 )

        foreach ( $arrayq3 as $rootidq3 ) {

        $q4 = mysql_query (" SELECT root_id FROM locations WHERE parent_id = '$rootidq3' ")    

            }

            $spotsquery .= " f.cat_id = $q4 "; }

        else {

        $spotsquery .= " f.cat_id = $q3 "; }

    else {

    $spotsquery .= " f.cat_id = $q2 "; }

else {

$spotsquery .= " f.cat_id = $q1 ";

}

$spots = mysql_query( $spotsquery )

Does this work? If so:
This only works if the maximum of levels is 3. But I have the feeling that if the number of continents, countries and provinces add up, this mounts to an extensive number of sql executions, which will not benefit performance. Am I correct?

Cheers for any feedback,
Gurt

February 6th, 2004, 08:09 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,581

Time spent in forums: 1 Month 3 Weeks 6 Days 5 h 20 m 45 sec
Reputation Power: 949

okay, if all you really have is 3 levels -- continent, country, province -- then this is the only query you need:

Code:

select l1.root_id   as l1id
     , l1.name      as l1name
     , l2.root_id   as l2id
     , l2.name      as l2name
     , l3.root_id   as l3id
     , l3.name      as l3name
  from location l1
left outer
  join location l2
    on l1.root_id = l2.parent_id 
left outer
  join location l3
    on l2.root_id = l3.parent_id     
 where l1.name = 'Antartica'

if you choose a name at the top level, and it has both countries and provinces under it, you will see them all

if you choose a name at a "leaf" level, you will see nothing under it

you need to run this query a few times to become comfortable with how it works

basically it just goes "down the tree" a max of three levels from wherever it starts, so if your tree is only maximum three levels deep, it will work everywhere

now, all you have to handle is the nulls that you will see in the result columns when the LEFT OUTER joins are not able to extend the tree any deeper

once you're happy with this first query, and that it's actually producing the right results, post here again and i'll show you how to pull all the unique root_ids out of the results produced by the first query using it as a subquery to an outer query

__________________
r937.com | rudy.ca

Last edited by r937 : February 6th, 2004 at 08:11 PM.

February 7th, 2004, 08:48 AM

Gurt

Contributing User

Join Date: Dec 2003

Location: Amsterdam, NL

Posts: 389

Time spent in forums: 2 Days 13 h 59 m 20 sec
Reputation Power: 7

Hi R937,

It works perfectly! The only thing is that the id,s that I need shift to the right in the result table, when the level of hierarchy gets higher. So in case I select a continent, and the hierarchy is 3 levels deep, then i need the l3.root_ids from the table. When I select a province I need the l1.root_id, and the l2.root_id and l3.root_id show NULLs in the fields. So that all works fine.

I dont care about the NULLS, since I dont use them anyway, but maybe I can use them to navigate to the correct column with root_id's that I need?

So, in (crooked php ) :

PHP Code:


		
			
IF (( l2.root_id != 'NULL' ) and (l3.root_id != 'NULL') {

//add the right root_id column to the overall query where the 

//right spots need to be selected by country_id

$spotsquery .= " country_id = '$l3.root_id' "; 

}

elseif (l3.root_id != 'NULL') {

$spotsquery .= " country_id = '$l2.root_id' "; 

}

else {

$spotsquery .= " country_id = '$l1.root_id' "; 

}

Is that a valid way of doing this, or did you have a mySQL solution in minds?

editited
I see that when I select a continent where there is a difference in the hierarchy within countries, so one country does have provinces and another doesn't, the latter will show 'NULL' values in the l3.root_id while the country with the provinces will have the needed l3.root_id. Now I understand your remark about retreiving the unique values!

I can't wait. It's a good thing you told me to experiment with this query first, because now I really see what it does!

editited

Thanks a lot for the help. I greatly appreciate it!

Cheers,
Gurt

Last edited by Gurt : February 7th, 2004 at 09:04 AM.

February 7th, 2004, 08:55 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,581

Time spent in forums: 1 Month 3 Weeks 6 Days 5 h 20 m 45 sec
Reputation Power: 949

sorry, i cannot help you with php, i only do coldfusion

perhaps the php forum?

February 7th, 2004, 09:06 AM

Gurt

Contributing User

Join Date: Dec 2003

Location: Amsterdam, NL

Posts: 389

Time spent in forums: 2 Days 13 h 59 m 20 sec
Reputation Power: 7

I just saw that my php doesn't work for this query, so I'm anxious to see your mysql solution. I edited my previous post to also say:

"I see that when I select a continent where there is a difference in the hierarchy within countries, so one country does have provinces and another doesn't, the latter will show 'NULL' values in the l3.root_id while the country with the provinces will have the needed l3.root_id. Now I understand your remark about retreiving the unique values!
I can't wait. It's a good thing you told me to experiment with this query first, because now I really see what it does!"

Thanks!
Gurt

February 7th, 2004, 09:23 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,581

Time spent in forums: 1 Month 3 Weeks 6 Days 5 h 20 m 45 sec
Reputation Power: 949

what version of mysql are you on?

if less than 4.1, you will need a temp table

if less than 4.0, you will need two temp tables

February 7th, 2004, 09:30 AM

Gurt

Contributing User

Join Date: Dec 2003

Location: Amsterdam, NL

Posts: 389

Time spent in forums: 2 Days 13 h 59 m 20 sec
Reputation Power: 7

Both at home and at provider: 3.23.39

February 7th, 2004, 09:46 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,581

Time spent in forums: 1 Month 3 Weeks 6 Days 5 h 20 m 45 sec
Reputation Power: 949

sorry, only one temp table (i was getting ahead of myself thinking about simulating a view)

Code:

create temporary table selectednames
( id integer
, name varchar(50)
)
;
insert into selectednames
select l1.root_id  
     , l1.name      
  from location l1
 where l1.name = 'Antartica'
; 
insert into selectednames
select l2.root_id   
     , l2.name      
  from location l1
inner
  join location l2
    on l1.root_id = l2.parent_id 
 where l1.name = 'Antartica'
; 
insert into selectednames
select l3.root_id  
     , l3.name     
  from location l1
inner
  join location l2
    on l1.root_id = l2.parent_id 
inner
  join location l3
    on l2.root_id = l3.parent_id     
 where l1.name = 'Antartica'  
; 
select distinct 
     , id
     , name
  from selectednames 
;

#10

February 7th, 2004, 11:23 AM

Gurt

Contributing User

Join Date: Dec 2003

Location: Amsterdam, NL

Posts: 389

Time spent in forums: 2 Days 13 h 59 m 20 sec
Reputation Power: 7

Wow!!!! And you know what? I actually think i understand what happens, i think... not sure though....heh...
The query seems to work (apart from the one comma in "select distinct , id, name..." this for other viewers), but it creates a temporary table , which I can't see in my phpmyadmin

How can I echo out this table so that I know what is in it?
The way I read it, it includes the results from all three root_id columns and all three name columns in 1 table with columns id, and name. Is that right? I only need the lowest level root_ids in this temp table, but can't see what is happening.

Cheers,
Gurt

#11

February 7th, 2004, 11:34 AM

Gurt

Contributing User

Join Date: Dec 2003

Location: Amsterdam, NL

Posts: 389

Time spent in forums: 2 Days 13 h 59 m 20 sec
Reputation Power: 7

Stop the press! I was jumping to conclusions! It works great! I just left out the temporary to see the result, and it does indeed what I thought it does, but having the higher level id,s in the table doesn't matter because they will not match any records in the spots table so nothing shows up.

Even better: When for some reason a spot is listed under Antarctica and also in a sub category of antarctica, they will both show up.

Wow! Your a rockstar! Thanks you so much.
I will post back the link to the website when i'm done, so you can see why i needed this.

Cheers,
Gurt

#12

February 16th, 2004, 05:21 AM

Gurt

Contributing User

Join Date: Dec 2003

Location: Amsterdam, NL

Posts: 389