SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complex Database Query Question (Multiple Parents)

    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. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •