Select and Browse Categories and Subcategories Stored in Separate Tables

3
votes

I'm looking for a solution to list and browse categories and subcategories and their records (classified ads), when you store category levels in separate tables. In the past I have worked with the adjacency model but I have to stick to this database setup now, and it is new to me. I'm using php and mysql.

The site is a classified ad site structured the common way: it has the main category list on its homepage, when you click one of the category links then only its subcategories are listed and the ads that belong to this category, and so on, at every level.

I'm a bit confused in the following areas:

  1. How do you construct the category links when browsing categories in order for the script to know which table it should select categories from if I consider the below mysql structure? Do I need separate parameters at every category level I access like e.g: "mysite.com/?cat2=4" when accessing category "4" in the cat2 table and "mysite.com/?cat3=9" when accessing category "9" in cat3 table in order to identify category levels? If separate parameter not needed, then how can php and mysql tell what table you have to select the categories from?

  2. And most importantly in this case, what is the best way to construct SEO friendly links? And how will mysql know which table to select categories from? I would like to use the most simplest solution that is possible like: mysite.com/electronics/television/sony. As far as I know, I have to include at least the cat_id somewhere in the link... where do I put it? and do I have to include the number of level as well? To complicate it more the category names are in foreign language with accented characters (though I created a function that changes accented characters into latin ones on the fly when generating category links) so I think it is best to select them by their ids.

  3. How is a sample mysql select looks like that selects the child categories of a certain category?

  4. How can I construct breadcrumb navigation?

MYSQL STRUCTURE:

  • Table "cat1" (main category):

    cat1_id | cat1_name

  • Table "cat2" (subcategory):

    cat2_id | cat1_id | cat2_name

  • Table "cat3" (subsubcategory):

    cat3_id | cat2_id | cat3_name

  • Table "ads":

    ad_id | cat1_id | cat2_id | cat3_id | ad_title | ad_description

Thanks and sorry for the long post.

1
phpmysqldatabase-designmod-rewritecategories

1 Answers

1
votes

My favourite pattern for category (and tag) URLs:

http://mysite.com/articles/brains+zombies+legs+frogs

The + symbol is nice for tags, and friendly to spiders (and SEO). Using the text of the categories is important for both spiders and humans as it's meaningful.

As for the SQL, I suggest 2 tables for anything with categories or tags:

Categories (id, name, description)

CategoryRelationships (catID, thingID)

For any given thing, you join Categories to Things via CategoryRelationships. For example:

SELECT * FROM Things t
JOIN CategoryRelationships ON thingID = t.ID
JOIN Categories c ON catID = c.CatID

The result will be a list of Things and their categories, where you have only one definition of each category, and a bunch of links to the categories via the Relationship table.

As for breadcrumbs, they're a slightly different problem. Breadcrumbs either:

  • Provide navigation through your site hierarchy, or
  • Help the user retrace their steps

Depending on the type of breadcrumb you're aiming at, you take a different approach. For a simple site hierarchy set of breadcrumbs, you can simply parse the URL and foreach over the set of segments:

http://mysite.com/people/zombies/brains/brains

Parsing the URI would result in:

people, zombies, brains, brains

For which you would generate links to each segment.