--- Jigal van Hemert <[EMAIL PROTECTED]> wrote:
> > ANIMALS TABLE
> > Canis_lupus | wolf
> > Panthera_tigris | tiger
> >
> > JOIN TABLE
> > SPECIES | ECOREGION
> > Canis_lupus | NA1008
> > Canis_lupus | NA1010
> >
> > ECOREGIONS TABLE
> > ID | NAME | Geog | Geog2
> > NA1008 | Alaska tundra | na | na
> > IM1003 | Philippine rainforest | eur | phl
> > (Note that mainland ecoregions feature the
> continental
> > ID in each of the last two columns, while island
> > ecoregions feature the island's ID in the last
> > column.)
> >
> > GEOGRAPHY TABLE
> > ID | NAME
> > na | North America
> > phl | Philippines
>
>
> Maybe you can start by rewriting the problem is
> pseudo queries:
> "I want a list of the NAMEs from the GEOGRAPHY table
> for a certain
> species from the JOIN table for which the ecoregions
> and the geog are
> listed in the ecoregions table. Each NAME should
> only appear once."
> Or something like that.
Well, I've already made one major change. I can see
that this is going to be way too complex for me no
matter what, so I split it into TWO queries. This
query displays the native continents:
<?
$query = "SELECT * FROM gwecoareasexp AS GW
LEFT JOIN geog101exp as GG on GG.ID101 = GW.Geog
WHERE IDX = 'IM0123' OR IDX = 'PA0408' OR IDX =
'AT1011' GROUP BY Geog";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
$Geog[] = $row['Geog2'];
echo($row['Name101']).'';
echo '<br />';
}
?>
For example, if the species discussed is the yak, it
would display this:
Eurasia
The giraffe page would display this:
Africa
The puma:
North America
South America
For species that are native only to continental
mainlands, that's all there is to it. I don't even
have to worry about them in the second query.
The second query so far looks like this:
<?php
$footnote = "SELECT * FROM gwecoareasexp AS GW
LEFT JOIN geog101exp as GG on GG.ID101 = GW.Geog2
WHERE IDX = 'IM0123' OR IDX = 'IM0123' OR IDX =
'PA0408' OR IDX = 'AT1011'";
$result = mysql_query($footnote);
while($row = mysql_fetch_array($result)) {
$Geog[] = $row['Geog2'];
echo($row['Geog2']).'';
echo($row['Name101']).'';
echo '<br />';
}
?>
Its purpose is to identify species that are native to
islands, then display a text message depending on
whether or not that animal is also native to a
continent.
For example, the following data tells us that we can
forget about the yak, which is a purely mainland
species.
yak | eur | eur
Philippine eagle | eur | phl
tiger | eur | eur
tiger | eur | bal
The Philippine eagle page might display a message like
this:
Eurasia*
*Philippines only
The tiger page might display this:
Eurasia*
*Including Bali
I haven't even plugged in my animal species table yet.
I'm just using the WHERE clause to select groups of
ecoregions from my table gweocoregions and experiment
with them.
It's easy to eliminate mainland species, because their
values in the fields Geog and Geog2 are identical; eur
| eur for the Eurasia yak, for example.
For my second query, I would join Geog2 to the field
ID101 in my geography table. So if Geog2 contains
"phl", it would display "Philippines" from the
geography table.
If that's still too complex, I might just hand code
arrays grouping each continent's islands together and
somehow draw the data from them.
For example...
'
$Eurasia = array("Philippines", "Borneo", "Sumatra")
Then my PHP script could say something like, "Display
this message if any name in this array appears and
there is also a mainland region - like eur | eur - but
display the other message if a name in this array
shows up but there's no mainland ecoregion."
It's hard to even explain it, but that's a start.
Thanks.
__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
