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

David Blomstrom 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]'");


this is dangerous on a real website.  see mysql_real_escape_string()

The key just helps you search for the 2 fields at the same time 
quicker.  It doesn't create a new field.  But it's good that you added 
that key though since you are looking up both at the same time..

Two fields are always going to be two fields.  Perhaps you may be 
confusing the role of PHP with that of MySql... Can you just concatenate 
the two strings together after you get them from the database? e.g. 
$parent.$name?  And, just split() or explode() the string when you get 
it from the URL?

Can there be spaces in these names?  If so, maybe use a dash for space, 
that way, there's no chance of ambiguity.  Get very familiar with  the 
PHP string functions too.

-d

--
http://www.douglassdavis.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