SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Join 4 tables

  1. #1
    SitePoint Enthusiast ivanfx's Avatar
    Join Date
    May 2007
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join 4 tables

    I have 4 tables.

    Users (ID, Title), Posts (ID, User_ID, Post), Comments (ID, User_ID, Post_ID, Comment) and GROUPS (User_A (int), User_B(int))

    Overview: A user can post a POST, other people can post COMMENTS on that POST. Now, every user follows a number of other users, hence the GROUPS.

    What I need to do is retrieve all the latest posts (ordered DESC), the related comments, and user information (related to the post and comment) but only if the user_b is associated with user_a in the groups table. I know user_a (it's the logged user)

    Anyhow, I need it to return something like (lets say it's print_r with PHP)

    Code:
    [0] => 
      [POST] => 
        [id] => 2
        [title] => Hello
      
      [USER] => 
        [id] => 13
        [username] = Mike
     
      [COMMENT] =>
        [0] =>
          [COMMENT] =>
            [id] => 44
            [comment] => hello
          [USER] =>
            [id] => 54
            [username] => John
    
    [1] => ..
    Anyhow, hope you understand
    Freelance CSS and PHP developer. Using CakePHP and jQuery.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    does your posts table have a posted_datetime column, or were you planning on using an auto_increment (which is a poor idea)

    "groups" is an unfortunate name because membership in a group seems to be directional, i.e. if todd and biff are in the same group, do you store todd:biff or biff:todd

    and whose posts should be retrieved, todd's or biff's? or should it be todd's posts and biff's comments?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast ivanfx's Avatar
    Join Date
    May 2007
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @r937 Thanks for the response and the questions

    My idea for the groups table is if Jeff is following Tod, the row has Jeff:Tod

    Tod obviously doesn't need to follow Jeff. If Tod were following Jeff, there would be another row with Tod:Jeff.


    I was thinking of adding an auto_incremented ID to posts, but it does have a datetime column. Why is the autoincrement a wrong idea?


    Thanks!
    Freelance CSS and PHP developer. Using CakePHP and jQuery.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ivanfx View Post
    I was thinking of adding an auto_incremented ID to posts, but it does have a datetime column.
    so what is the ID that you showed? and why didn't you show the datetime column?

    now i don't know whether to believe any of your tables
    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
  •