Managing page categories

Databases
1

Hi there,

I am using PHP and MySQL to manage pages on my web site. I have a custom made content management system.

I have a question… let’s say we have the table “pages” with all of the good stuff. Focus specifically on column “page_category”. Let’s say there’s another table that contains a list of all of the categories.

What would be the most efficient way to go about allowing a page to belong to multiple categories? Right now, I am using a comma delimited mechanism (i.e. if column “page_category” is “1,4”, the page belongs to the respective category IDs 1 and 4). However, I am getting a little tired of the FIND_IN_SETS() and was wondering if there was a better way to do it. implode() and explode() is also annoying.

Also, when displaying a list of pages, if my “page_category” column contains “1,4” what would be the most efficient way, through MySQL, to join the results to the “categories” table (because “1, 4” looks really dumb when the results are displayed; I want the “category_name”)?

Are there any other better application theories out there? Let me know. :cool:

2

When it’s a many-to-many relationship, a separate table is an efficient way to handle such relationships. The table could be called PageCategory and have two columns, pageId, and categoryId.

Each row is for a different piece of information. The first row could say that page 3 is in category 1, and the second row could say that page 3 is in category 4

That way you can use


SELECT DISTINCT pageId
FROM PageCategory
WHERE categoryId = 4

An inner join is the common way.

You could use something like


SELECT Page.*, PageCategoryNames.name
FROM Page
    INNER JOIN (
        SELECT PageCategory.pageId, Category.name
        FROM PageCategory
            INNER JOIN Category
                ON PageCategory.categoryId = Category.id
        WHERE pageId = 3
    ) as PageCategoryNames
        ON PageCategoryNames.pageId = Page.id
WHERE Page.id = 3
3

You could use a bitwise system, though if you have a lot of categories, the numbers could get prohibitively large… MySQL supports bitwise comparisons, so unless i’m completely wrong, WHERE (pages.page_category & categories.num) > 0 should return all categories that page belongs to?

4

What Paul said:


pages
----------
id | name
1  | something
2  | orother

categories
----------
id | name
1  | first
2  | second

page_categories
----------
page_id | category_id
1      | 1
1      | 2
2      | 1

Should allow you to select all pages for a category, or all categories for a page easily.