Re: Treating Two Fields Like One
David Blomstrom wrote: --- [EMAIL PROTECTED] wrote: - In my opinion: The easiest thing for you to do right now would be a table like ID (primary key) NAME PARENTID You don't need PARENT, because you have the PARENTID, you can always retrieve parent. Although... i still think it would have been simpler to do separate tables for each taxonomic level, and this single table design may cause problems down the road if you start adding more fields... but anyway. Keep it simple. One ID is all you need to identify any row. As far as the URL, you could use the scheme i gave earlier for the URLs (if you can absolutely guarantee 100% every NAME-PARENT combination will be unique), but it would be more complicated PHP code and more complicated queries, and links would break when spellings changed. Just use the ID in the URL, it's simple. If you add or delete rows, or change spelling, the keys stay the same, primary keys don't change. Just make sure you don't delete anything's parent and everything will be just fine. * * * * * OK, thanks. I'll give that a try. I'll probably try to combine it with the recursive array described at http://www.sitepoint.com/article/hierarchical-data-database/3 (after I learn how to do it). Let me throw one more curve ball at you, though. I now understand that I can't combine my primary key with the field Name, like 8leo. But do you know if I could combine numerals from this "tree traversal" script with Name? Here's why numerals appeal to me. If I combine fields like Name/Parent ID - e.g. leo/Pan, then it will be more difficult when I want to separate them. If I combine Name with a numeral - e.g. leo/8 - then all I need is a script that weeds out all numerals. I think. Does this make sense? Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com All you need is one ID to identify anything in there. No need to combine anything . Keep it simple. -- http://www.douglassdavis.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Treating Two Fields Like One
--- [EMAIL PROTECTED] wrote: - In my opinion: The easiest thing for you to do right now would be a table like ID (primary key) NAME PARENTID You don't need PARENT, because you have the PARENTID, you can always retrieve parent. Although... i still think it would have been simpler to do separate tables for each taxonomic level, and this single table design may cause problems down the road if you start adding more fields... but anyway. Keep it simple. One ID is all you need to identify any row. As far as the URL, you could use the scheme i gave earlier for the URLs (if you can absolutely guarantee 100% every NAME-PARENT combination will be unique), but it would be more complicated PHP code and more complicated queries, and links would break when spellings changed. Just use the ID in the URL, it's simple. If you add or delete rows, or change spelling, the keys stay the same, primary keys don't change. Just make sure you don't delete anything's parent and everything will be just fine. * * * * * OK, thanks. I'll give that a try. I'll probably try to combine it with the recursive array described at http://www.sitepoint.com/article/hierarchical-data-database/3 (after I learn how to do it). Let me throw one more curve ball at you, though. I now understand that I can't combine my primary key with the field Name, like 8leo. But do you know if I could combine numerals from this "tree traversal" script with Name? Here's why numerals appeal to me. If I combine fields like Name/Parent ID - e.g. leo/Pan, then it will be more difficult when I want to separate them. If I combine Name with a numeral - e.g. leo/8 - then all I need is a script that weeds out all numerals. I think. Does this make sense? Thanks. __ 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]
Re: Treating Two Fields Like One
--- [EMAIL PROTECTED] wrote: > You have confused front end representation with > back-end data design. How > you store your data and create your data > relationships is only marginally > related to what your code makes it look like when it > presents your data > for the user. Why just create longer unique path > names that represent the > actual taxonomic path you have to take to get to an > animal? What's wrong > with: > > http://geozoo/stacks/mamalia/carnivora/canidae/canis/leo/ > http://geozoo/stacks/mamalia/carnivora/felidae/panthera/leo/ > > Those are unique and they accurately model your > heirarchy. It's a good > learning tool too as it documents the actual lineage > of an animal in its > URL. Each level of the path could resolve to > something distinctive about > each taxonomic group > > http://geozoo/stacks/mamalia/carnivora/canidae/ > would take you to a page > about the dog family > http://geozoo/stacks/mamalia/carnivora/ would take > you to a page about the > order of carnivores > ... and so on > > You don't have to worry about reorganziation of your > taxonomic tree > breaking your links (like you were when you wanted > to link based on ID). > You can add and delete nodes in your taxonomy at > will (a deleted node > could show some default "sorry, no profile exists > yet. This site is still > under construction" - type message). > > What's wrong with returning two responses if someone > searched on the > species "leo". What if they didn't know about Canis > leo? That could be a > cool surprise. Let the user figure out which one > they really wanted to > see. That means that with your existing tree, you > have to search the tree > for leo and if there is only one match you show the > matching page, > otherwise you have to present a page that shows them > the options > available. Thanks for the tips. Actually, I have several database-driven websites that are organized just as you describe. However, I'm trying to turn this site into a content management system, with all the articles displayed on the same page. In fact, it will be similar to the Animal Diversity Website, which displays wolf and Canidae at the following URL's: http://animaldiversity.ummz.umich.edu/site/accounts/information/Canis_lupus.html http://animaldiversity.ummz.umich.edu/site/accounts/information/Canidae.html As you can see, they don't just use lupus for wolf; they pair it with its genus name, Canis_lupus. That's what I want to do eventually, but there are so many competing things to figure out - PHP, mod_rewrite, MySQL, spaces between words, terms from two columns, etc. I appreciate what you said about the educational value of a bread crumbs-type URL. However, I have that sequence represented in the links at the top of my page. Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Treating Two Fields Like One
David, >I can easily substitute integers from my primary key for >names, but how do I substitute them for parents? For example: >ID | NAME | PARENT >10 | Canidae | Carnivora >11 | Canis | Canidae >12 | Vulpes |Canidae >I can easily replace Canis with 11, Vulpes with 12. >But they both have the same family - Canidae, which >translates as 10. I could create a new field and >manually, like this: >ID | NAME | PARENT | PARENTID >10 | Canidae | Carnivora | 9 >11 | Canis | Canidae | 10 >12 | Vulpes |Canidae | 10 >But if I add or delete a row, the numerals in my >primary key will change, No. Existing autosequential (auto-increment) values are not affected by new autosequential values. One argument for keeping each level in its own table is the possibility of cleanly enforcing referential integrity, eg a constraint that prevents deletion of a parent row which is referenced in a row of a child tabke. >messing up the values in >PARENTID. No. See above. >Consider the database table code below, which displays >animal names (representing all taxonomic heirarchies) >in a child-parent relationship: >ID | NAME | PARENT >1 | Mammalia | (NULL) >2 | Carnivora | Mammalia >3 | Canidae | Carnivora >4 | Canis | Canidae >5 | leo | Canis >6 | Felidae | Carnivora >7 | Panthera | Felidae >8 | leo | Panthera Sorry, what do you mean by 'database table code'? What I see is a text representation of some rows from a table. >Rows 5 and 8 represent identical species names, leo. >If I type http://geozoo/stacks/leo/ into my browser, >it defaults to Mammalia > Carnivora > Canidae > Canis >> leo, rather than the lion, Mammalia > Carnivora > >Felidae > Panthera > leo >So I need a way to distinguish one leo from the other. I gather yuou have some code that reads what the user types in and tries to complete her request sentence. For the cases where the last name she typed is the name of more than one entity, you need to provide the user an interface for making a choice. One possibility for the case you instance would be a dropdown miniwindow which collects the possibilities, here leo (Canis) or leo (Panthera), and puts them in a dropdown for her to choose. Another possibility is to put up a page or frame that represents the bit of the tree that the user has so far specified, plus one level, and let her walk down the tree. >Would it be possible to somehow combine my >auto-incrementing primary key with the field Name, >converting leo / leo to 5leo / 8leo? Sure. You can query on anything you please, and show any part of that query, or all of it, or none of it. >There are two things I'd have to deal with... >1. I'd need to weed the numerals out of the display, >which should look like this... Sure. The user shouldn't have to be bothered with the details of how you make her job easier. I do not understand why you think your autoincrement values need to change. The whole idea is that, once assigned, they _never_ change. If your update model entails changing primary keys, fix the model. PB - David Blomstrom wrote: --- Peter Brawley <[EMAIL PROTECTED]> wrote: "As you note, the names [of animal taxons] aren't guaranteed to be unique, or to stay the same . . . One way out is to give every table an auto-incrementing integer PK, and use those keys, which will never change, to mark parent-child relationships. I wanted to follow up on this. I can easily substitute integers from my primary key for names, but how do I substitute them for parents? For example: ID | NAME | PARENT 10 | Canidae | Carnivora 11 | Canis | Canidae 12 | Vulpes |Canidae I can easily replace Canis with 11, Vulpes with 12. But they both have the same family - Canidae, which translates as 10. I could create a new field and manually, like this: ID | NAME | PARENT | PARENTID 10 | Canidae | Carnivora | 9 11 | Canis | Canidae | 10 12 | Vulpes |Canidae | 10 But if I add or delete a row, the numerals in my primary key will change, messing up the values in PARENTID. Along similar lines, I have another question... Consider the database table code below, which displays animal names (representing all taxonomic heirarchies) in a child-parent relationship: ID | NAME | PARENT 1 | Mammalia | (NULL) 2 | Carnivora | Mammalia 3 | Canidae | Carnivora 4 | Canis | Canidae 5 | leo | Canis 6 | Felidae | Carnivora 7 | Panthera | Felidae 8 | leo | Panthera Rows 5 and 8 represent identical species names, leo. If I type http://geozoo/stacks/leo/ into my browser, it defaults to Mammalia > Carnivora > Canidae > Canis leo, rather than the lion, Mammalia > Carnivora > Felidae > Panthera > leo So I need a way to distinguish one leo from the other. Would it be possible to somehow combine my auto-incrementing primary key with the field Name, converting leo / leo to 5leo / 8leo? There are two things I'd have to deal with... 1. I'd need to weed the numerals out of the display, which should look like this... "http://geozoo/stacks/l
Re: Treating Two Fields Like One
David Blomstrom <[EMAIL PROTECTED]> wrote on 08/24/2005 03:31:19 PM: > --- Peter Brawley <[EMAIL PROTECTED]> wrote: > > "As you note, the names [of animal taxons] aren't > guaranteed to be unique, or to stay the same . . . > > > One way out is to give every table an > > auto-incrementing integer PK, and > > use those keys, which will never change, to mark > > parent-child relationships. > > I wanted to follow up on this. I can easily substitute > integers from my primary key for names, but how do I > substitute them for parents? For example: > > ID | NAME | PARENT > 10 | Canidae | Carnivora > 11 | Canis | Canidae > 12 | Vulpes |Canidae > > I can easily replace Canis with 11, Vulpes with 12. > But they both have the same family - Canidae, which > translates as 10. I could create a new field and > manually, like this: > > ID | NAME | PARENT | PARENTID > 10 | Canidae | Carnivora | 9 > 11 | Canis | Canidae | 10 > 12 | Vulpes |Canidae | 10 > > But if I add or delete a row, the numerals in my > primary key will change, messing up the values in > PARENTID. > > Along similar lines, I have another question... > > Consider the database table code below, which displays > animal names (representing all taxonomic heirarchies) > in a child-parent relationship: > > ID | NAME | PARENT > 1 | Mammalia | (NULL) > 2 | Carnivora | Mammalia > 3 | Canidae | Carnivora > 4 | Canis | Canidae > 5 | leo | Canis > 6 | Felidae | Carnivora > 7 | Panthera | Felidae > 8 | leo | Panthera > > Rows 5 and 8 represent identical species names, leo. > If I type http://geozoo/stacks/leo/ into my browser, > it defaults to Mammalia > Carnivora > Canidae > Canis > > leo, rather than the lion, Mammalia > Carnivora > > Felidae > Panthera > leo > > So I need a way to distinguish one leo from the other. > > Would it be possible to somehow combine my > auto-incrementing primary key with the field Name, > converting leo / leo to 5leo / 8leo? > > There are two things I'd have to deal with... > > 1. I'd need to weed the numerals out of the display, > which should look like this... > > http://geozoo/stacks/leo/";>leo > > not this... > > http://geozoo/stacks/8leo/";>8leo > > 2. The numerals would have to be fluid, as I will be > adding and deleting rows. Thus, the lion could be 8leo > one day and 9leo the next. > > I can take this to a PHP forum to learn how to > implement it. But I thought someone on this forum > might tell me if it can be done in the first place. > > Thanks. > > David, You have confused front end representation with back-end data design. How you store your data and create your data relationships is only marginally related to what your code makes it look like when it presents your data for the user. Why just create longer unique path names that represent the actual taxonomic path you have to take to get to an animal? What's wrong with: http://geozoo/stacks/mamalia/carnivora/canidae/canis/leo/ http://geozoo/stacks/mamalia/carnivora/felidae/panthera/leo/ Those are unique and they accurately model your heirarchy. It's a good learning tool too as it documents the actual lineage of an animal in its URL. Each level of the path could resolve to something distinctive about each taxonomic group http://geozoo/stacks/mamalia/carnivora/canidae/ would take you to a page about the dog family http://geozoo/stacks/mamalia/carnivora/ would take you to a page about the order of carnivores ... and so on You don't have to worry about reorganziation of your taxonomic tree breaking your links (like you were when you wanted to link based on ID). You can add and delete nodes in your taxonomy at will (a deleted node could show some default "sorry, no profile exists yet. This site is still under construction" - type message). What's wrong with returning two responses if someone searched on the species "leo". What if they didn't know about Canis leo? That could be a cool surprise. Let the user figure out which one they really wanted to see. That means that with your existing tree, you have to search the tree for leo and if there is only one match you show the matching page, otherwise you have to present a page that shows them the options available. Make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Treating Two Fields Like One
I think you misunderstand how auto_increment works. Primary keys using auto_increment are NOT row numbers. If your table has a primary key that is an auto_increment field then when you add a row to the table the value of the primary key of the new row is 1 greater than the max(Value) before the row was added. Once added the value in the field does not change. Let's say you have ID V1 V2 1 a b 2 x y 3 x u 4 b a Now you delete the row with ID = 2. The row where V1=x and V2=u still has a value of 3 in the ID field. >From reading the post I think to need to look at some refernces on handling tree/hierarchie structures in a relational table. Here are 2 references out of many. http://www.sitepoint.com/article/hierarchical-data-database http://www.intelligententerprise.com/001020/celko1_1.jhtml They should help you understand your 2nd question " So I need a way to distinguish one leo from the other." -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 24, 2005 2:31 PM To: mysql@lists.mysql.com Subject: Re: Treating Two Fields Like One --- Peter Brawley <[EMAIL PROTECTED]> wrote: "As you note, the names [of animal taxons] aren't guaranteed to be unique, or to stay the same . . . > One way out is to give every table an > auto-incrementing integer PK, and > use those keys, which will never change, to mark > parent-child relationships. I wanted to follow up on this. I can easily substitute integers from my primary key for names, but how do I substitute them for parents? For example: ID | NAME | PARENT 10 | Canidae | Carnivora 11 | Canis | Canidae 12 | Vulpes |Canidae I can easily replace Canis with 11, Vulpes with 12. But they both have the same family - Canidae, which translates as 10. I could create a new field and manually, like this: ID | NAME | PARENT | PARENTID 10 | Canidae | Carnivora | 9 11 | Canis | Canidae | 10 12 | Vulpes |Canidae | 10 But if I add or delete a row, the numerals in my primary key will change, messing up the values in PARENTID. Along similar lines, I have another question... Consider the database table code below, which displays animal names (representing all taxonomic heirarchies) in a child-parent relationship: ID | NAME | PARENT 1 | Mammalia | (NULL) 2 | Carnivora | Mammalia 3 | Canidae | Carnivora 4 | Canis | Canidae 5 | leo | Canis 6 | Felidae | Carnivora 7 | Panthera | Felidae 8 | leo | Panthera Rows 5 and 8 represent identical species names, leo. If I type http://geozoo/stacks/leo/ into my browser, it defaults to Mammalia > Carnivora > Canidae > Canis > leo, rather than the lion, Mammalia > Carnivora > Felidae > Panthera > leo So I need a way to distinguish one leo from the other. Would it be possible to somehow combine my auto-incrementing primary key with the field Name, converting leo / leo to 5leo / 8leo? There are two things I'd have to deal with... 1. I'd need to weed the numerals out of the display, which should look like this... http://geozoo/stacks/leo/";>leo not this... http://geozoo/stacks/8leo/";>8leo 2. The numerals would have to be fluid, as I will be adding and deleting rows. Thus, the lion could be 8leo one day and 9leo the next. I can take this to a PHP forum to learn how to implement it. But I thought someone on this forum might tell me if it can be done in the first place. Thanks. __ 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Treating Two Fields Like One
--- Peter Brawley <[EMAIL PROTECTED]> wrote: "As you note, the names [of animal taxons] aren't guaranteed to be unique, or to stay the same . . . > One way out is to give every table an > auto-incrementing integer PK, and > use those keys, which will never change, to mark > parent-child relationships. I wanted to follow up on this. I can easily substitute integers from my primary key for names, but how do I substitute them for parents? For example: ID | NAME | PARENT 10 | Canidae | Carnivora 11 | Canis | Canidae 12 | Vulpes |Canidae I can easily replace Canis with 11, Vulpes with 12. But they both have the same family - Canidae, which translates as 10. I could create a new field and manually, like this: ID | NAME | PARENT | PARENTID 10 | Canidae | Carnivora | 9 11 | Canis | Canidae | 10 12 | Vulpes |Canidae | 10 But if I add or delete a row, the numerals in my primary key will change, messing up the values in PARENTID. Along similar lines, I have another question... Consider the database table code below, which displays animal names (representing all taxonomic heirarchies) in a child-parent relationship: ID | NAME | PARENT 1 | Mammalia | (NULL) 2 | Carnivora | Mammalia 3 | Canidae | Carnivora 4 | Canis | Canidae 5 | leo | Canis 6 | Felidae | Carnivora 7 | Panthera | Felidae 8 | leo | Panthera Rows 5 and 8 represent identical species names, leo. If I type http://geozoo/stacks/leo/ into my browser, it defaults to Mammalia > Carnivora > Canidae > Canis > leo, rather than the lion, Mammalia > Carnivora > Felidae > Panthera > leo So I need a way to distinguish one leo from the other. Would it be possible to somehow combine my auto-incrementing primary key with the field Name, converting leo / leo to 5leo / 8leo? There are two things I'd have to deal with... 1. I'd need to weed the numerals out of the display, which should look like this... http://geozoo/stacks/leo/";>leo not this... http://geozoo/stacks/8leo/";>8leo 2. The numerals would have to be fluid, as I will be adding and deleting rows. Thus, the lion could be 8leo one day and 9leo the next. I can take this to a PHP forum to learn how to implement it. But I thought someone on this forum might tell me if it can be done in the first place. Thanks. __ 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]
Re: Treating Two Fields Like One
--- Peter Brawley <[EMAIL PROTECTED]> wrote: > David, > > >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: > > > > As you note, the names aren't guaranteed to be > unique, or to stay the > same either, therefore they won't do as primary > keys, therefore they > won't do as foreign keys. Also our understanding of > these taxonomic > relationships can change even when the names don't, > and it's considered > a design error to make it necessary to edit primary > keys in order to > update database tables. > > One way out is to give every table an > auto-incrementing integer PK, and > use those keys, which will never change, to mark > parent-child relationships. I already have an auto-incrementing integer PK, but I haven't used it in my queries, as I thought I'd need actual names to work with my scripts. But now that you mention it, I'm probably mistaken, so I'll give it a try. Thanks. Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Treating Two Fields Like One
I'll give that a try. Thanks. --- [EMAIL PROTECTED] wrote: > > > David Blomstrom wrote: > > >-- [EMAIL PROTECTED] wrote: > > > > > > > >>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? > >> > >> > > > >Consider the following URL's: > > > >1. stacks/leo > > > >2. stacks/Panthera_leo > > > >The first is "recognized" by my database and > fetches > >information. The second doesn't work. If I > concatenate > >then split the link as you suggest, will it produce > a > >link that looks like #2 yet is recognized by my > >database? > > > >If so, I'll learn how to do those functions. > > > > > > > Yes. > > The problem here is a PHP one, not an SQL one.. > MySql doesn't know > anything about your links, that's not it's job, but > it does know about > fields. It's PHP's job to know about links and make > MySql "recognize" > the links by formatting them in an appropriate way > in the SQL. > > in the taxonomic names, replace spaces with '-' > (look that up... it's > one of the PHP string functions) By the way, I hope > the names don't > have any ' or , in them, if so, you'll need to do > more replacing. > Search for replace on the php site. > > Then, concatenate like this: > $parent.'_'.$name > > Add that to the end of your URL. > > then when you get the URL parameter 'taxon', > explode() it on '_', then > replace all - with spaces in each of the two values > you get. > > > > > >Yes, there will definitely be spaces between words. > I > >haven't yet decided whether I'll replace them with > -, > >_ or . (period). However, I'm leaning towards > >underscores ( _ ) for this particular page and > hyphens > >( - ) on a topics page. > > > >T > > > > don't use periods. those have other meanings in PHP, > and it can get to > be confusing. > > -- > http://www.douglassdavis.com > > __ Yahoo! Mail Stay connected, organized, and protected. Take the tour: http://tour.mail.yahoo.com/mailtour.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Treating Two Fields Like One
David Blomstrom wrote: -- [EMAIL PROTECTED] wrote: 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? Consider the following URL's: 1. stacks/leo 2. stacks/Panthera_leo The first is "recognized" by my database and fetches information. The second doesn't work. If I concatenate then split the link as you suggest, will it produce a link that looks like #2 yet is recognized by my database? If so, I'll learn how to do those functions. Yes. The problem here is a PHP one, not an SQL one.. MySql doesn't know anything about your links, that's not it's job, but it does know about fields. It's PHP's job to know about links and make MySql "recognize" the links by formatting them in an appropriate way in the SQL. in the taxonomic names, replace spaces with '-' (look that up... it's one of the PHP string functions) By the way, I hope the names don't have any ' or , in them, if so, you'll need to do more replacing. Search for replace on the php site. Then, concatenate like this: $parent.'_'.$name Add that to the end of your URL. then when you get the URL parameter 'taxon', explode() it on '_', then replace all - with spaces in each of the two values you get. Yes, there will definitely be spaces between words. I haven't yet decided whether I'll replace them with -, _ or . (period). However, I'm leaning towards underscores ( _ ) for this particular page and hyphens ( - ) on a topics page. T don't use periods. those have other meanings in PHP, and it can get to be confusing. -- http://www.douglassdavis.com
Re: Treating Two Fields Like One
David,
>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:
As you note, the names aren't guaranteed to be unique, or to stay the
same either, therefore they won't do as primary keys, therefore they
won't do as foreign keys. Also our understanding of these taxonomic
relationships can change even when the names don't, and it's considered
a design error to make it necessary to edit primary keys in order to
update database tables.
One way out is to give every table an auto-incrementing integer PK, and
use those keys, which will never change, to mark parent-child
relationships.
It is indeed confusing to try to work out user interface and database
design issues at the same time. The usual suggestion is to solve the
database structure problem before you lay a hand on the UI problem. The
idea is to write a query or query template for every question yoiur
system may ever be asked, and build the tables that those queries need.
PB
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]'");
...except it didn't work.
Is there a way to do this, or can you see any other
MySQL solutions to my problem?
Thanks.
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.14/79 - Release Date: 8/22/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Treating Two Fields Like One
-- [EMAIL PROTECTED] wrote: > 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? Consider the following URL's: 1. stacks/leo 2. stacks/Panthera_leo The first is "recognized" by my database and fetches information. The second doesn't work. If I concatenate then split the link as you suggest, will it produce a link that looks like #2 yet is recognized by my database? If so, I'll learn how to do those functions. > 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. Yes, there will definitely be spaces between words. I haven't yet decided whether I'll replace them with -, _ or . (period). However, I'm leaning towards underscores ( _ ) for this particular page and hyphens ( - ) on a topics page. Thanks. __ 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]
Re: Treating Two Fields Like One
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
Re: Treating Two Fields Like One
--- 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]
