SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Managing page categories

    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.

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    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?

  3. #3
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,729
    Mentioned
    104 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Panduola View Post
    What would be the most efficient way to go about allowing a page to belong to multiple categories?
    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

    Code mysql:
    SELECT DISTINCT pageId
    FROM PageCategory
    WHERE categoryId = 4

    Quote Originally Posted by Panduola View Post
    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
    An inner join is the common way.

    You could use something like

    Code mysql:
    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
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  4. #4
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What Paul said:
    Code:
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •