Complex Database Query Question (Multiple Parents)

PHP
1

I’m trying to figure out how to work with some biogeography data, and it’s really confusing. Consider the following hierarchy:

North America (Continent)
Grassland (Biome)
Dakota (Ecoregion)

It’s simple enough; Temperate Grassland is the parent of the Dakota ecoregion, and North America is the parent of the Temperate Grassland biome.

The problem is that biomes aren’t restricted to specific continents. Thus, we could also have the following hierarchy:

Eurasia (Continent)
Grassland (Biome)
Mongolia (Ecoregion)

So I can’t use a standard parent-child script to display this sort of stuff, since each biome has multiple parents.

I’ve been playing with a scheme that looks something like this…

Table1 (A simple list of continents, biomes and ecoregions, with additional information)

Table2 (Ecoregions matched to biomes)
Name | Parent
Steppe | Grassland
Dakota | Grassland
Plains | Grassland
Rockies | Mountains

Table3 (Ecoregions matched to continents)
Name | Parent
Steppe | Eurasia
Dakota | North America
Plains | North America
Rockies | North America

Table4 (Ecoregions matched to states)
Dakota | Montana
Dakota | South Dakota
Dakota | Nebraska
Dakota | Colorado
Plains | Montana
Plains | Colorado
Rockies | Montana
Rockies | Colorado

Table5 (Geography table that matches ID’s to the full names of countries and states)

Because there are so many ecoregions, I just use ID numbers to identify each one except in Table 1, which lists the complete name. Similarly, I identify states with ID’s (e.g. us-ny).

Anyway, I can join tables 1, 4 and 5 to display a list of ecoregions for each state. For example, if I visit MySite/World/Colorado, I see the following:

Dakota, Plains, Rockies

I’d like to display the biomes as well. So my display would look like this:

Grassland Biome
Dakota
Grassland Biome
Plains
Mountains Biome
Rockies

But instead of list “Grassland” twice, I’d like to make it look like this:

Grassland Biome
Dakota
Plains
Mountains Biome
Rockies

Can anyone tell me how to query the above tables to create that kind of display? It’s going to get even more complicated when I add ecozones - roughly comparable to continents. So I suspect I’ll have to do this project the hard way - just map everything out manually. But I thought I’d check first to see if there’s a way to do it with a database.

Thanks.

2

You should be able to do it with something like…

SELECT GROUP_CONCAT(region.name) AS regions…GROUP BY Biome

and then while the result array, each row is a biome; explode the concat’d string on “,” to get the results.

You can also do this in PHP by selecting all records ordered by Biome and then running a “Current” flag setup.

Predefine Cur as empty
Loop records in query
If Current Record’s Biome != Cur
Echo header
Set Cur to current record’s biome
endif
echo Current Record’s region
endloop.

3

OK, I’ll work with that over the next few days. It will take me a few days to get my database tables squared away. Thanks.