SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2002
    Location
    UK
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Fetch related rows from tags

    I'm using a 2 table tagging system for articles (myisam) and I'm trying to get related articles based on the tags and category_id of the article currently being viewed.

    What would be the optimal query to achieve this? I only seem to be able to find answers for 3 table tag systems.

    tags:
    Code:
    `article_id` int(11) NOT NULL DEFAULT '0',
    `tag` varchar(99) NOT NULL DEFAULT '',
      PRIMARY KEY (`article_id`,`tag`),
      KEY `tag_ix` (`tag`)
    articles:
    Code:
    `article_id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(255) NOT NULL DEFAULT '',
    `content` text NOT NULL,
    `category_id` int(11) NOT NULL,
    `tags` varchar(255) NOT NULL DEFAULT '',
    PRIMARY KEY (`article_id`), 
    KEY `category_id` (`category_id`)
    Thanks for any help.

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,075
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    Remove tags from the articles table. You'll then only store the tags for each article in the tags table:

    Code:
    select
    a.tag, a.article_id, c.article_title
    from
    tags a
    inner join tags b on a.tag = b.tag and b.article_id <> a.article_id
    inner join articles c on b.article_id = c.article_id
    where
    a.article_id = $currentArticleId

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2002
    Location
    UK
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Kyle. I've added to the WHERE clause: AND dl.category_id = $categoryId and this makes the query go from 0.001 to 0.8 seconds. I really need it to fetch related articles based on the category ID of the current article, is there a better way of doing this?

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,075
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    Adding category id will solve your problem. You'll need to create an index if you want to improve query time.


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
  •