SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    A quick question

    I would like to ask what is the best way to handle categories in a content managements system.

    Is the best way to have a separate table in the database just for the categories?

    And finally, how to track to which categories an article is assigned in table that contains articles? The problem is, an article can be assigned to more than one category - so a column in articles table won't be enough for that.

    I have been thinking about some smart solution for this but I haven't found any.

  2. #2
    SitePoint Evangelist
    Join Date
    May 2006
    Location
    Denmark
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's called a many-to-many relationship.

    Basically you have the article table, the category table and an intermediate table connecting those two using their primary keys.

    So you could for instance have a table called article_category with the fields "category_id" and "article_id". To get category 123's articles you could then do something like this:

    Code MySQL:
    SELECT a.*, c.category_name FROM category c
      INNER JOIN category_article ca
        ON ca.category_id = c.category_id
      INNER JOIN article a
        ON ca.article_id = a.article_id
      WHERE c.category_id = 123;

  3. #3
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I'm not sure I understand. I need 3 tables just to track which categories an article is assigned to?

    Wouldn't it be easier to just save the categories into one column in the database and divide them with some special characters like for example:

    Code:
    category1&&category2&&category3
    And then I would explode() the string by && to divide it into categories?

    Moreover, I don't understand the query you posted.

  4. #4
    SitePoint Evangelist
    Join Date
    May 2006
    Location
    Denmark
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, in fact it would be a horrible idea to do that. How would you for instance efficiently find all the articles that are in category 2 or 5 using that method? What I described is pretty standard and the best way to do it. You have the two tables and a third table storing the relationships between those two.

    Try to google "many-to-many" and check up on SQL joins. You can read about joins here: http://www.phpfreaks.com/tutorial/data-joins-unions

  5. #5
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Ok, this is the simple way of thinking about it.

    Categories: ID, Name, Parent
    Articles: ID, Name, etc
    Join_Categories_Articles: CategoryID, ArticleID

    That way you can have many rows in join_categories_articles linking as many articles to as many categories as you want.

    Keeping them all in one string is very bad - against all efficiency and normalisation rules in the metaphorical book.

    The query to get them is:
    Code sql:
    SELECT Articles.Name FROM Articles
    	INNER JOIN Join_Categories_Articles
    		ON ArticleID = Articles.ID
    	INNER JOIN Categories
    		ON Categories.ID = CategoryID
    	WHERE Categories.Name = 'PHP'
    This means:
    Code sql:
    SELECT Articles.Name FROM Articles
    Select the Name from the table articles
    Code sql:
    	INNER JOIN Join_Categories_Articles
    		ON ArticleID = Articles.ID
    Joined to the join table - where all article IDs in the Articles table are equal to the article IDs in the join table
    Code sql:
    	INNER JOIN Categories
    		ON Categories.ID = CategoryID
    Joined to the categories table where the category ID of any row in the categories table equals the joined row in the join table
    Code sql:
    	WHERE Categories.Name = 'PHP'
    Where the name of the category is PHP.

    So you're refining 3 tables into 1 table:
    Code:
    Categories:
    ID	|	Name	|	Parent	|
    1	|	Website	|	-1	|
    2	|	HTML	|	1	|
    3	|	PHP	|	1	|
    4	|	JS	|	1	|
    5	|	OOP	|	3	|
    6	|	CSS	|	1	|
    
    Articles:
    ID	|	Name	|
    1	|	XHTML	|
    2	|	Styling Forms	|
    3	|	Login systems made easy	|
    4	|	OOP Shopping Cart	|
    
    Join_Categories_Articles:
    
    Article	|	Category
    1	|	2
    2	|	2
    2	|	6
    3	|	3
    4	|	3
    4	|	5
    
    Joining query will produce a result looking like:
    Article Name	|	Category Name
    XHTML	|	HTML
    Styling Forms	|	HTML
    Styling Forms	|	CSS
    Login Systems Made Easy	|	PHP
    OOP Shopping Cart	|	PHP
    OOP Shopping Cart	|	OOP
    Rounding it to just having PHP articles would result in 'Login Systems Made Easy' and 'OOP Shopping Cart'. Just OOP would be 'OOP Shopping Cart'.
    Last edited by Jake Arkinstall; Sep 8, 2008 at 09:39. Reason: Example!!!
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  6. #6
    Coding and Breathing CoderMaya's Avatar
    Join Date
    Feb 2008
    Location
    Atlit, Israel
    Posts
    470
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, I've never seen that kind of pattern in commercial databases.

    It's usually, for example, Articles: ID, Subject, Text, CategoryID.
    Learn about the new Retro Framework
    Code PHP the way it was meant to be coded!

  7. #7
    SitePoint Evangelist
    Join Date
    May 2006
    Location
    Denmark
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by CoderMaya View Post
    Actually, I've never seen that kind of pattern in commercial databases.

    It's usually, for example, Articles: ID, Subject, Text, CategoryID.
    That's called a one-to-many, "belongs to" or "have many" relationship. I.e. an article belongs to a category. A category has many articles.

    The many-to-many relationship allows the article to belong to multiple categories.

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,053
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Three tables:

    1.) articles
    2.) articles_categories_toc
    3.) categories

    This will also makes the categories flexible. Say in the future you add an events table. Then its just a matter of creating the events_categories_toc table and now both the events and articles share categories. Which would make it easy to select all articles and events that fall under a specific category.

  9. #9
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow arkinstall - that's a lot of information at once. I will try to implement this many-to-many approach I guess.

  10. #10
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And what is the query to get categories the article is assigned to?

    I'm trying this but I get error:

    PHP Code:
    $conn->queryObjectArray("SELECT categories.title FROM categories 
      INNER JOIN intersection
          ON categoryID = categories.id
      INNER JOIN categories
          ON categories.ID = categoryID
      WHERE categories.id = '
    $id'"); 
    My tables are called:

    1) articles (id, title, tagline, date, content)
    2) categories (id, title)
    3) intersection (articleID, categoryID)

    This is the structure of my database:

    Code sql:
    CREATE TABLE `articles` (
      `id` INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'The unique ID of the article',
      `title` VARCHAR(100) NULL COMMENT 'The article title',
      `tagline` VARCHAR(255) NULL COMMENT 'Short summary of the article',
      `date` VARCHAR(100) NULL COMMENT 'Date and time the article was written',  
      `content` text NULL COMMENT 'The article content'
    ) ENGINE = InnoDB;
     
    CREATE TABLE `categories` (
      `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID of the category',
      `title` VARCHAR(100) NULL COMMENT 'The category title',
      PRIMARY KEY (`id`) 
    ) ENGINE = InnoDB;
     
    CREATE TABLE `intersection` (
      `articleId` VARCHAR(6) NULL,
      `categoryId` VARCHAR(6) NULL,
      PRIMARY KEY (`articleID`, `categoryID`)
    ) ENGINE = InnoDB;
    Last edited by risoknop; Sep 8, 2008 at 16:09.

  11. #11
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
    SELECT 
    a
    .title 
    FROM articles 
    as a
    INNER JOIN intersection 
    as i
    ON a
    .id i.articleID
    WHERE
    i
    .categoryID $id
    How about that, try it, untested.

    if $id is an integer then don't quote it.

  12. #12
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    PHP Code:
    SELECT 
    a
    .title 
    FROM articles 
    as a
    INNER JOIN intersection 
    as i
    ON a
    .id i.articleID
    WHERE
    i
    .categoryID $id
    How about that, try it, untested.

    if $id is an integer then don't quote it.
    Hmm doesn't work. I get this error:

    Code:
    Catchable fatal error: Object of class stdClass could not be converted to string in D:\wamp\www\frameworks\cms\viewArticle.php on line 31
    I go sleep now, maybe tomorrow I will get some ideas.

  13. #13
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    That error has nothing to do with the query. Stick that query into PhpMyAdmin with a real id number to see whether it works or not.

  14. #14
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What does the "a" in the query means? There is no table or column named "a"...

  15. #15
    SitePoint Evangelist
    Join Date
    Oct 2005
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Having articles as a basically helps you shorten the query.. if you have multiple tables with similar column names...e.g. primary and foreign keys...then you can shorten your sql statement using AS.

    Long hand

    PHP Code:

    SELECT 

    article
    .title 

    FROM articles

    INNER JOIN intersection

    ON article
    .id intersection.articleID

    WHERE

    intersection
    .categoryID $id
    Short Hand
    PHP Code:
    SELECT 

    a
    .title 

    FROM articles 
    as a

    INNER JOIN intersection 
    as i

    ON a
    .id i.articleID

    WHERE

    i
    .categoryID $id

  16. #16
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok guys thanks for really great help. I have figured it out, this is the query that works:

    Code sql:
    SELECT categories.title FROM categories
    INNER JOIN join_articles_categories
      ON category_id = categories.id
    INNER JOIN articles
      ON articles.id = article_id
    WHERE articles.id = $id
    Last edited by risoknop; Sep 9, 2008 at 16:18.

  17. #17
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I would advise that you name your table in a slightly more descriptive fashion - especially when working on a project that others may work on in the future, or when you have multiple many-to-many relationships
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  18. #18
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I normally use a number 2 for join tables, stated doing that on one project and it kinda stuck, heh.

    eg: articles2categories

  19. #19
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Just something to ask any SQL gurus here (summons rudy), as it's a very related (and my last post made me think of it)
    When using a project using alot of many-to-many relationships, would it be beneficial to use just one table to handle it all?
    Code:
    Joins
    -------
    Table1
    Table2
    Field1
    Field2
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  20. #20
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I have seen systems that do that before - eg Sugar CRM has a 'relationships' table that is that + a few more columns

  21. #21
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm what about this. I just found out that MySQL (at least the version I am using) is case insensitive so I'm using word1_word2 structure instead of word1Word2:

    Code sql:
    CREATE TABLE `articles` (
      `id` INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'The unique ID of the article',
      `title` VARCHAR(100) NULL COMMENT 'The article title',
      `tagline` VARCHAR(255) NULL COMMENT 'Short summary of the article',
      `date` TIMESTAMP NULL COMMENT 'Date and time the article was written',  
      `content` text NULL COMMENT 'The article content'
    ) ENGINE = InnoDB;
     
    CREATE TABLE `categories` (
      `id` INT(6) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID of the category',
      `title` VARCHAR(100) NULL COMMENT 'The category title',
      PRIMARY KEY (`id`) 
    ) ENGINE = InnoDB;
     
    CREATE TABLE `join_articles_categories` (
      `article_id` INT(6) NULL,
      `category_id` INT(6) NULL,
      PRIMARY KEY (`article_id`, `category_id`)
    ) ENGINE = InnoDB;
    Last edited by risoknop; Sep 9, 2008 at 16:17.

  22. #22
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Looks ok, although why does the join table use varchars? Should be ints really, same as the relevant columns in the other 2 tables

  23. #23
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh thanks I forgot to change that

  24. #24
    SitePoint Guru babyboy808's Avatar
    Join Date
    Nov 2004
    Location
    dublin
    Posts
    602
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    arkinstall, Good Job, great explanation!

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by arkinstall View Post
    When using a project using alot of many-to-many relationships, would it be beneficial to use just one table to handle it all?
    no

    don't go there

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •