Firstly, suppose I want to list all the subcategories under a category (eg - SELECT * FROM Categories WHERE parentid = $catid). I need to generate a URL for each subcategory that looks like "www.domain.com/category/subcategory/". One way I could imagine doing this is storing the path (eg - "category/subcategory/") in a field "path". But that seems like doubling up data. Another convoluted way would be to generate the path for each subcategory on the fly, via recursive calls to the database. That seems a bit serverload intensive. Could anyone suggest the best way to achieve this?
Secondly, once someone goes to the URL www.domain.com/category/subcategory/, I need to work out the catid of the particular subcategory they're visiting. One way I suppose is a simple query "SELECT catid FROM Categories WHERE category = '$subcategory'" where $subcategory will be the last element of an array exploded from the URI. I just have to make sure every category in the database is unique. But is there a smarter way to do this?
Forgive the lengthy post - I'm still a newbie and really have no clue what is the best way to tackle this kind of programming.
Take a look at these two threads, they deal with the parent/child relationship. Your answer may well be in there.
DB Theory Help for Category Editors Database [webmasterworld.com]
Schema for Articles DB [webmasterworld.com]
Interesting articles although they deal more with designing the database. I've already been down that road and nutted out my design (although reading those articles a few months ago would've been very helpful!), this is more figuring out the best way to interface the database with this new fangled search engine friendly directory structure I'm having to use. Eg - my URLs display all these directories that don't actually exist - it's all a fiction generated by the database and while there are lots of threads and articles about mod_rewrites and .htaccess, I've found absolutely nothing on how to dynamically generate your directories as URL links.
That makes it a bit more complex than it would seem. The inherent problem would be keeping the cat names unique. Are you the only one who will be entering catnames? That would definitely help with that process.
Another thing you might want to consider for the main pages in each category is having a page that has the catid set and then including the template for the category main page. This could be written from the script when you insert the new category. It essentially removes the need for mod rewrite while keeping the fully dynamic nature of the site.
Then at least the structure would be "real". You end up with a bunch of directories with these little files in them that load templates and do db calls but I have used it with a lot of success many times.
Yes, for now. I can manually keep the cat names unique although I'm worried about general subcategories. It's a scifi link directory so there might be one subcategory Star Trek > Fan Fiction and another subcategory Star Wars > Fan Fiction. Rather than use cumbersome titles like "Star Wars Fan Fiction", I was thinking of doing a query like "SELECT * FROM Categories, Categories AS TempCat WHERE Categories.category = $category AND (TempCat.category = $parentcategory AND Categories.parentid = TempCat.catid)". I hope that's how you do aliased tables, it's been a while since I've done one.
> Another thing you might want to consider for the main pages in each category is having a page that has the catid set and then including the template for the category main page. Then at least the structure would be "real". You end up with a bunch of directories with these little files in them that load templates and do db calls but I have used it with a lot of success many times.
Not sure exactly what you mean but if you mean create actual directories for each category, it sounds like I'm losing all the advantages of using mod_rewrite and database driving the whole thing (I'm rather taken with the idea of the whole directory run from a single page index.php)
