List:General Discussion« Previous MessageNext Message »
From:Enrique Sanchez Vela Date:August 24 2005 3:11am
Subject:Re: Treating Two Fields Like One
View as plain text  

--- 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 
Thread
Treating Two Fields Like OneDavid Blomstrom24 Aug
  • Re: Treating Two Fields Like OneEnrique Sanchez Vela24 Aug
  • Re: Treating Two Fields Like Onedouglass_davis24 Aug
    • Re: Treating Two Fields Like OneDavid Blomstrom24 Aug
      • Re: Treating Two Fields Like Onedouglass_davis24 Aug
        • Re: Treating Two Fields Like OneDavid Blomstrom24 Aug
  • Re: Treating Two Fields Like OnePeter Brawley24 Aug
    • Re: Treating Two Fields Like OneDavid Blomstrom24 Aug
    • Re: Treating Two Fields Like OneDavid Blomstrom24 Aug
      • Re: Treating Two Fields Like OneSGreen24 Aug
        • Re: Treating Two Fields Like OneDavid Blomstrom24 Aug
      • Re: Treating Two Fields Like OnePeter Brawley24 Aug
RE: Treating Two Fields Like OneGordon Bruce24 Aug
Re: Treating Two Fields Like OneDavid Blomstrom25 Aug
  • Re: Treating Two Fields Like Onedouglass_davis25 Aug