MySQL ODBC Connector - GROUP BY Destroys 2nd Function

Author

GROUP BY Destroys 2nd Function

David Blomstrom

2005-10-28, 9:23 am

I have a PHP script that displays data like this:

Eurasia
Eurasiaisland
Africa

Where Eurasia and Africa are mainland parents of
ecological regions and Eurasiaisland is a
parent of an ecological system that is associated with
a continent. For example, Borneo would be
Eurasiaisland.

The finished script will display an animal species'
distribution. Obviously, I don't want to say it lives
in Eurasia Eurasia.

Instead, I want to group them together, so an Old
World species like the leopard might look like this:

Eurasia
Africa

....no matter how many ecological regions it inhabits
on either continent, mainland or island.

The problem is that when I add GROUP BY to my command,
I lose the superscripts. It appears to favor a
particular row, and if that particular row represents
a mainland ecoregion, then EVERYTHING is defined as
mainland.

My script also displays footnotes that will eventually
name the islands it's native to. These, too, disappear
when I use the GROUP BY command.

Is there a simple solution you can think of? If not,
can you think of some sort of workaround, like a
separate table listing islands that I can somehow plug
into the system? Normalization isn't a priority; what
I'm doing is already over my head, and my primary goal
is user friendly - simply coming up with something
that works.

Below are some simple diagrams of my tables. Thanks.

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

____________________
______________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

David Blomstrom

2005-10-28, 11:23 am

--- Jigal van Hemert <jigal@spill.nl> wrote:

> continental
>
>
> 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($r
esult)) {
$Geog[] = $row['Geog2'];
echo($row['Name101']).'';
echo ' ';
}
?>

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($footnot
e);
while($row = mysql_fetch_array($r
esult)) {
$Geog[] = $row['Geog2'];
echo($row['Geog2']).'';
echo($row['Name101']).'';
echo ' ';
}
?>

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/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2005-10-28, 1:23 pm

--=_alternative 005F74A2852570A8_=
Content-Type: text/plain; charset="US-ASCII"

<<<<Everything snipped>>>>

David, is it at all intuitive to organize your geography into a tree-type
structure? Here is an example:

Western Hemisphere (hemisphere)
C. America (continent)
Guatemala (country)
N. America (continent)
Canada (country)
Manitoba (state/province)
Moose
Elk
Cuba (country)
Cuba (island)
Aligator
United States (country)
Montana (state/province)
Moose
Florida (state/province)
Dade (county/parish)
Aligator
Mexico (country)
Chihuahua (state/province)
Desert Rat
S. America (continent)
Brazil (country)
Amazon (river)
Pirhana
Peru (country)
Argentina (country)
Galapagos Is (island)
Galapagos Tortise

With this kind of structure, it is simple to answer questions like:

a) List all of the places to find Aligators
b) Which animals can be found on Islands in the Western Hemisphere?
c) List all animals found in Brazil.

Any type of question that deals with "containment" can be answered from a
data structure like this. Your flat table model will not work for this
type of information. (List the continents in the Western Hemisphere, list
the countries on the continent "S. America",...)

You cannot make your site easy to manage with just a flat data structure.
Sorry, but that is my professional opinion.

For instance, to solve the question of "what countries will I find an
Aligator", you find all of the nodes for Aligator then move "up" the tree
until you find a "country" node for each one. You may need to move up 1 or
2 or more nodes until you get to a "country" but you will eventually get
to one. Keep a list of the countries you find. When you are done, simplify
your list so that each country only appears once. There's the answer. Make
sense?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 005F74A2852570A8_=--

David Blomstrom

2005-10-28, 8:23 pm

--- SGreen@unimin.com wrote:

> <<<<Everything snipped>>>>
>
> David, is it at all intuitive to organize your
> geography into a tree-type
> structure? Here is an example:
>
> Western Hemisphere (hemisphere)
> C. America (continent)
> Guatemala (country)
> N. America (continent)
> Canada (country)
> Manitoba (state/province)
> Moose
> Elk

I see what you're saying, but I'm not sure if it's
workable. I'd need a separate row for every state,
province and nation the moose is native to. Ditto for
each of hundreds of species of rodents and bats.

Instead, I'm linking species to ecoregions, then
linking the ecoregions to geographic regions - which
may just be another version of what you're suggesting.

In fact, I have all the animal species organized into
a tree structure, and I already have a separate tree
structure for nations, states, etc. Maybe I can
combine them somehow.

Thanks.

____________________
______________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links