I'm working on a big animals database and have a
question that's a little hard to explain, though I
suspect it will make sense to the pros.
First, a little background. I'm tentatively setting up
a parent-child relationship and putting all the basic
mammal data in a separate table. Below are some sample
rows to give you an idea of what I have in mind:
ID NAME COMMON NAME PARENT
car | Carnivora | meat eaters | Mammalia
can | Canidae | dog family | Carnivora
can | Canis | wolf & coyote | Canidae
lup | lupus | wolf | Canis
As you can see, lupus' parent is Canis, Canis' parent
is Canidae and Canidae's parent is Carnivora.
The three-letter ID works great in many respects. I
want to use three-letter URL's, so instead of...
mammals.geozoo.org/carnivora/canidae/canis/lupus/
I could use...
mammals.geozoo.org/car/can/can/lup/
The obvious problem is that C-A-N are the first three
letters of both Canidae and Canis, along with many
other scientific names.
So I came up with what I think is a clever solution -
I can simply add numerals to make unique ID's, then
use PHP to strip out the numerals when I display the
data.
I might number the orders, families, genera and
species separately. This is what some codes might look
like:
Carnivora = car1
Artiodactyla = art2
lupus = lup3012
So here's my question:
Rather than sit down and write out codes for more than
14,000 species (nearly 5,000 birds and 9,000 birds
alone), is there some trick I can use to generate
these numerals with MySQL?
For example, could I create a column of codes that
look like this:
art
car
pri
cet
then instruct MySQL to automatically add numerals
sequentially, so it looks like this?:
art1
car2
pri3
cet4
One possibility is to create a separate field filled
with numerical values and place it next to my codes,
like this:
Field 1 Field 2
art | 1
car | 2
tig | 1033
Then I'd have to somehow join the two fields so that
MySQL recognizes the values as...
art1
car2
tig1033
I've already learned how to strip out the numerals
with PHP. But this project would be a lot easier if I
could at least figure out how to generate the
numerical suffixes with MySQL.
Any tips?
Thanks.
__________________________________
Do you Yahoo!?
Vote for the stars of Yahoo!'s next ad campaign!
http://advision.webevents.yahoo.com/yahoo/votelifeengine/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
