HighDots Forums  

Multi-Table Join + Parents (PHP/MySQL)

Macromedia Dreamweaver Macromedia Dreamweaver Discussions (macromedia.dreamweaver)


Discuss Multi-Table Join + Parents (PHP/MySQL) in the Macromedia Dreamweaver forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
DavidB
 
Posts: n/a

Default Multi-Table Join + Parents (PHP/MySQL) - 11-06-2005 , 11:00 AM






I have a list of about 30,000 animal species matched to their native
ecoregions in a table named gzecoregions. Another table contains the
list of all the ecoregions AND their parents (biomes) and THEIR parents
(realms) in a parent-child relationship.

Thus, I can use the following query to display a list of ecoregions the
wolf is native to, where $Animal = 'wolf':

Quote:
$area_res = mysql_query("SELECT DISTINCT GWER.ERName FROM gwecoregions
as GWER
INNER JOIN gzecoregions GZER ON GZER.Place = GWER.ERID
WHERE GZER.Animal = '$Animal'");

Quote:
such as...
Alaska Tundra
Canadian Taiga
Siberian Taiga

The problem is I can only display ecoregions, since those are the only
areas listed in table gzecoregions. But I would also like to also
display the ecoregions' parents (biomes) and the biomes' parents
(realms). I discovered that I can't do it using just one table, so I
created two new tables for biomes and realms. But I can't figure out how
to join them so that I can display something like this:

NEARCTIC (realm)
TUNDRA (biome)
Alaska Tundra
BOREAL FOREST (biome)
Canadian Taiga
PALEARCTIC (realm)
BOREAL FOREST (biome)
Siberian Taiga

It should be easy, but I'm doing something wrong. The following diagrams
illustrate the relevant tables and fields...

Quote:
TABLE: gwecorealms
RealmID | Realm | RealmParent
R-NA | Nearctic | na
R-NT | Neotropical | na
R-NT | Neotropical | sa
R-PA | Paleotropical | eur
R-PA | Paleotropical | afr

TABLE: gwecobiomes
BiomeID | Biome | BiomeParent
B11-NA | Tundra | R-NA
B11-PA | Tundra | R-PA
B11-AA | Tundra | R-AA
B11-AN | Tundra | R-AN
B12-NA | Desert | R-NA

TABLE: gwecoregions
ERID | ERName | ERParent
R-NA | Nearctic | na
R-NT | Neotropical | na
B11-NA | Tundra | R-NA
B11-PA | Tundra | R-PA
B11-AA | Tundra | R-AA
NA1424 | Alaska Tundra | B11-NA
PA1340 | Siberian Tundra | B11-PA |

Note that EVERY area, including all ecoregions, biomes and realms, is
included in table gwecoregions. So I display my data via field ERName.

Gwecoregions also has a field named ERParent, which includes the ID of
each region's parent, so everything is listed in a parent-child
relationship.

Below is a copy of my original query, followed by one query I used to
join all four tables. Both queries work, but they produce the same
results - a list of ecoregions only. How can I join them so my script
also displays the parents?

Thanks.

Quote:
$area_res = mysql_query("SELECT DISTINCT GWER.ERName FROM gwecoregions
as GWER
INNER JOIN gzecoregions GZER ON GZER.Place = GWER.ERID
WHERE GZER.Animal = '$Animal'");

$area_res = mysql_query("SELECT * FROM gwecoregions GWER
INNER JOIN gzecoregions GZER ON GZER.Place = GWER.ERID
INNER JOIN gwecobiomes GWEB ON GWEB.BiomeID = GWER.ERParent
INNER JOIN gwecorealms GWERM ON GWERM.RealmID = GWEB.BiomeParent
WHERE GZER.Animal = '$Animal' GROUP BY RealmN, BiomeN, ERN");

Quote:

Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.4
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.