--- David Blomstrom <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] ----------------------------------------------------------------------------- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
