SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot darkwarrior's Avatar
    Join Date
    Dec 2010
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Advice: Joining the best solution here?

    Hello, I'm working on developing a simple CMS for a personal website and I'm encountering a problem. I want to be able to pass variables to display posts of a certain type on a particular page but I'm struggling with the sql logic behind it.

    I have three tables:
    Categories - Contains name, id of category
    Posts - contains name, title, content, date, etc of posts made
    Relationships - Contains categoriy_id and post_id to match posts to multiple categories if applicable.

    My code as it was is just:
    Code PHP:
    $result = mysql_query("SELECT category_name FROM categories WHERE category_id=$catID");
     
    				while($row = mysql_fetch_array($result)) {
    					$id = stripslashes($row['post_id']);
    					$title = stripslashes($row['post_title']);
    					$content = stripslashes($row['post_content']);

    But that query won't return the post details and no info concerning what category a post belongs to exists on the post table. So I'm assuming I need to do a JOIN?

    I've never had to do a JOIN before so is this the right method I should be aiming for and can anyone advise me where to start?

    EDIT: The relationship table


  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    for someone who has never done a join before, you have certainly nailed the design of the relationship table accurately -- no auto_increment id, and a composite primary key consisting of both foreign keys

    well done

    your join query would look like this --
    Code:
    SELECT categories.category_name 
         , posts.name
         , posts.title
         , posts.content
         , posts.postdate
      FROM categories 
    LEFT OUTER
      JOIN relationships 
        ON relationships.category_id = categories.category_id
    LEFT OUTER
      JOIN posts 
        ON posts.post_id = relationships.post_id
     WHERE categories.category_id = $catID
    i used LEFT OUTER JOINs in case you select a category that has no posts in it -- if every category has at least one post in it, use INNER JOINs instead

    one minor point -- BIGINT is probably too big, use INTEGER throughout
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot darkwarrior's Avatar
    Join Date
    Dec 2010
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand the concept at least its the coding part I have no clue on. Thanks for the code provided, I have to head out but I will try incorporating it when I get back and see how it goes.

    Thanks R937

  4. #4
    SitePoint Zealot darkwarrior's Avatar
    Join Date
    Dec 2010
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ha ha, it works, that's amazing. The code seems so simple but put a gun to my head I wouldn't have a clue about LEFT OUTER or INNER. Amazing how powerful that is. I also took your advice on BIGINT, I read up on it and INT seems more than enough.

    Thank you very much for the help.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    another thread, another SQL convert

    rudy.ca | @rudydotca
    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
  •