--- David Blomstrom <david_blomstrom@stripped> wrote:
> I'm trying to make my first content management
> system
> and am wrestling with a problem that seems to be
> about
> equal parts PHP, Apache mod_rewrite and MySQL. I
> wondered if anyone on this list can suggest a MySQL
> solution - or partial solution.
>
> I'm dealing with a single database table named
> gzanimals that lists animal taxons (orders,
> families,
> species, etc.) in a child-parent relationship (with
> fields named "Name" and "Parent". For example:
>
> NAME | PARENT
> Animalia | (NULL)
> Mammalia | Animalia
> Carnivora | Mammalia
> Felidae | Carnivora
> Panthera | Felidae
> leo | Panthera
>
> The code above illustrates the taxonomic hierarchy
> linking the lion (Panthera leo) to the animal
> kingdom
> (Animalia). With Apache mod_rewrite, my URL's look
> like this:
>
> www.geozoo.org/stacks/Animalia
> www.geozoo.org/stacks/Mammalia
> www.geozoo.org/stacks/Carnivora
> www.geozoo.org/stacks/Felidae
> www.geozoo.org/stacks/Panthera
> www.geozoo.org/stacks/leo
>
> There are two problems with this strategy:
>
> 1. Some species names are shared by more than one
> mammal. For example, there's a marsupial named
> Antechinus leo.
>
> 2. Species are more properly cited with the name of
> their parent (genus), so I eventually want my
> species
> URL's to look like this:
>
> www.geozoo.org/stacks/Panthera_leo
>
> * * * * *
>
> I can manipulate my PHP script and mod_rewrite to
> draw
> from two fields and display Panthera_leo instead of
> leo.
>
> The problem is that I more or less lose contact with
> my database; it recognizes leo as a row identifier,
> while Panthera_leo is an unknown.
>
> * * * * *
>
> You can see a good example of what I'm trying to do
> at
> http://www.geozoo.org/stacks/Carnivora Two separate
> scripts drive the bread crumbs-style links at the
> top
> of the page and the list of "children" (carnivore
> families, in this case) in the column on the right.
>
> If you click Felidae, the children change to a list
> of
> genera that belong to the family Felidae. Click
> Panthera - http://www.geozoo.org/stacks/Panthera -
> and
> the children change to species that belong to the
> genus Panthera.
>
> As you can see, I've modified these so they display
> the genus (parent) + species (child) name, both
> physically and in the link. However, they are
> nonfunctioning, as I haven't yet figured out how to
> to
> deal with the space between the two values.
>
> * * * * *
>
> At this point, I'm thoroughly confused, especially
> since fixes often require tweakingn two or more
> things
> simultaneously - PHP, mod_rewrite and MySQL. But one
> thing I haven't experimented with yet is combined
> fields.
>
> I created a key on two fields - Name and Parent -
> and
> it NameDual. Is there a way to connect with NameDual
> in a PHP script, or is it something that only exists
> in my database?
>
> For example, I thought I might change...
>
> $taxon = mysql_query ("SELECT Name, NameCommon,
> Parent
> FROM gzanimals AS GZA
> WHERE Parent = '$_GET[taxon]'");
>
> to...
>
> $taxon = mysql_query ("SELECT NameDual, Name,
> NameCommon, Parent FROM gzanimals AS GZA
> WHERE Parent = '$_GET[taxon]'");
>
> ...except it didn't work.
>
> Is there a way to do this, or can you see any other
> MySQL solutions to my problem?
>
> Thanks.
>
>
Is NameDual equal to Parent_child?
Hi, here I will only show how incompeten I am but I
would get rid of the problem the following way...
first: use
$newtaxon = html_entity_decode($_GET["taxon"])
so you can get rid of the space encoding. instead of
http://www.geozoo.org/stacks/Panthera%20leo
you will have:
http://www.geozoo.org/stacks/Panthera leo
check:
http://us2.php.net/manual/en/function.html-entity-decode.php
next: use substr_replace to replace spaces with
underscores, if there are no spaces then there will no
be replacements :).
check
http://us2.php.net/manual/en/function.substr-replace.php)
finally: use ...
$taxon = mysql_query ("SELECT Name, NameCommon,
Parent
FROM gzanimals AS GZA
WHERE Parent = '$newtaxon' OR
CONCAT_WS('_',Parent,child) = '$newtaxon' ");
check:
http://dev.mysql.com/doc/mysql/en/string-functions.html
for CONCAT_WS.
regards,
esv.
Enrique Sanchez Vela
email: esanchezvela@stripped
-----------------------------------------------------------------------------
It's often easier to fight for one's || We live in the outer space
principles than to live up to them || Rev. Kay Greenleaf
Adlai Stevenson ||
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
