panduola — 2010-05-29T14:59:06-04:00 — #1
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.
paul_wilkins — 2010-05-30T02:05:52-04:00 — #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
WHERE categoryId = 4
An inner join is the common way.
You could use something like
SELECT Page.*, PageCategoryNames.name
INNER JOIN (
SELECT PageCategory.pageId, Category.name
INNER JOIN Category
ON PageCategory.categoryId = Category.id
WHERE pageId = 3
) as PageCategoryNames
ON PageCategoryNames.pageId = Page.id
WHERE Page.id = 3
starlion — 2010-05-29T15:31:43-04:00 — #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?
hash — 2010-05-30T08:06:57-04:00 — #4
What Paul said:
id | name
1 | something
2 | orother
id | name
1 | first
2 | second
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.