Translation method

[johnphpnewb]

Thank you phdr. Very informative post. I’ve saved the page that you’ve linked to in the post.

I’m sorry if i am annoying with this subject. PHP and Database design are new to me. I’ve only read basic info about this concept many years ago.

my site functions like this: main page contains icons with photos that represent kingdoms. select a kingdom [animals, plants, fungi etc]. select a phylum [invertebrata, vertebrata etc]. select a class [birds, mammals, spiders, flies etc]. select a family [if birds (Aves) then Turdidae, Cardinalidae, etc]. select a genus [if Turdidae then Turdus displays in a list with species selections as Turdus migratorius.] Along the way of this process, i simply wish to show a breadcrumb title bar with translated names. So selecting Aves will show Birds with Aves as a title attribute in the link. This way, scientific nomenclature is not intrusive but it is still available to you in the event that you wish to know the scientific name.

Using a database is complicated. I include a title.php with every species page. Before this database, i simply read common names from a text file. I think that now i will have to connect to the database every step along the way in order to get the common names.

So: Animalia [connect to retrieve Animals] -> Vertebrata -> [connect to retrieve Animals and Vertebrates] -> Aves [connect to retrieve Animals, Vertebrates and Birds] -> Cardinalidae [connect to retrieve Animals, Vertebrates, Birds and Cardinals] -> Cardinalis cardinalis [connect to retrieve Animals, Vertebrates, Birds, Cardinals, Northern Cardinal].

this is alot of db connectivity. is this normal? is this a bad design? i cannot afford to spend a year trying to master a framework and redesign my site. I like my site design but translating names seems to be a ridiculously difficult task. Further, i am not even sure about how to design the database tables to retrieve this data.

I was thinking that the Kingdom and Phylum could be stored in global variables to cut out db connectivity. I don’t need as many values compared to families and species. Isn’t this a better method? families and species can be stored in a database which brings me to my next problem:

i setup a test database erroneously named ‘genera’. Let’s reimagine this as ‘families’. So my db is avesenus (EN-US bird names table). table named ‘families’. families table contains:
id INT(6) AUTO-INCREMENT, LA char(32), EN char(32).

i think that in order to connect with a species table that i need to use a primary key. is this correct? so should the primary key be Latin name (LA column)? thus, the related species table would be as follows using Paridae: id INT(6) AUTO-INCREMENT, LA char(32), binomial char(128), EN char(32). Thus, i would search where LA = variable, WHERE binomial = LA in a join statement, yes?

another problem is the try catch. it was stated that with errors set to display=off, log=on that i do not need a try catch. However, without a try catch, the fatal error stops execution of my script, so nothing is loaded. A title bar is no reason to halt execution of my script. The page should still load with a default value. I cannot find a way to do this without a try catch block. I hate to be a PITA about this subject but i really want to finish my site.

Is anyone willing to offer some tips? i’m not sure how to implement this database design correctly. All of this new to me.

[JimL]

You only need to connect once per request, but it’s perfectly normal to have multiple queries to the db on a single page load.

So: Animalia [connect to retrieve Animals] -> Vertebrata -> [connect to retrieve Animals and Vertebrates] -> Aves [connect to retrieve Animals, Vertebrates and Birds] -> Cardinalidae [connect to retrieve Animals, Vertebrates, Birds and Cardinals] -> Cardinalis cardinalis [connect to retrieve Animals, Vertebrates, Birds, Cardinals, Northern Cardinal].

If we assume that we change “connect” to “query” then this might be correct. It does sound like a lot though but it’s impossible to know without knowing what data you have (in the database) and what data you need to display on that page.

[phdr]

another problem is the try catch. it was stated that with errors set to display=off, log=on that i do not need a try catch. However, without a try catch, the fatal error stops execution of my script, so nothing is loaded. A title bar is no reason to halt execution of my script. The page should still load with a default value. I cannot find a way to do this without a try catch block. I hate to be a PITA about this subject but i really want to finish my site.

Fatal database errors are things like a connection that fails, syntax errors in the sql query statement, typo/wrong table and column names, wrong data types being inserted/updated (depending on the server mode setting.) Aside from not being able to connection to the database server, these are programming mistakes and won’t exist after you have tested and debugged your code/queries. If you are thinking a query that doesn’t match any data produces a (fatal) error, it doesn’t. This is a successful query that returns an empty result set, which your code would detect and handle using conditional logic. The fetched data is a boolean false/empty() value in this case.

[johnphpnewb]

Hello Jim and phdr, Thank you both for everything. I really cannot Thank You enough.

The rest i up to me. I will finish this database and put it all together. I wouldn’t be this far without the help of this forum. I’ve come a long way now and i am happy with this site. I intend to continue studying and eventually create a database driven, framework dependent site. For now, I will continue with adding database connectivity to simplify as much as possible. I see what you mean about separating code from presentation and i agree with this concept. I definitely need time to implement it.

I hope that you all have a pleasant day and i am certain that this post will be helpful to many php beginners.

[johnphpnewb]

update: i used an associative array to associate the latin/scientific name with a particular language name for kingdom, phlum and class. I save database lookup for family and species.

i have one question about this array: should i keep it in a function to spare memory consumption? i don’t want my site to use too much memory. The array will be erased outside the function, yes?

so now i don’t have to connect to a database just to read static values. Phdr mentioned an array but it didn’t click for some reason. I guess that i needed the key word ‘associate’ to realize that this is the solution to the problem. Thank you, Phdr.