Joining a 3rd MySQL table (redone)

Question

Okay so I will try to re-explain this question as nobody seemed to understand my aim for this. It's 3am so that's probably where I am slacking a bit... But I'll try my best here.

I am currently working on a Posts & Comments section, users can post on their own or other users walls and as such, other users can comment on those posts. Same kind of thing as a social network status or general forum. I have 3 tables...

Users
Statuses
Comments

Users contains the standard...

userid
username
first_name
last_name
photo

Statuses contains any information about a main status posted from each user, these statuses are displayed on the users profiles. So user 1 could post a status on user2's profile. Here is the statuses table design...

status_id (auto-i)
user_id (The users ID whos profile the post was added too)
sender_id (The user who sent the post or wrote it)
date (Date/Time was sent)
rate (This doesn't matter for a moment)
comments (This will count all the comments and display the number)
status (The actual status written out)

These tables worked fine added together in my script which connected both tables and displayed the users information (the one who posted the status) such as their profile photo and name etc... Here is my current script which has no issues at all...

//// GET STATUSES
                                    $user_id = $profile_data['userid'];
                              $data = mysql_query("
                              SELECT 
                     statuses.status_id, 
                     statuses.user_id,
                     statuses.sender_id,
                     statuses.date,
                     statuses.rate,
                     statuses.comments,
                     statuses.status,
                     users.userid, 
                     users.username, 
                     users.first_name, 
                     users.last_name, 
                     users.photo
                              FROM 
                                    statuses
                              LEFT JOIN 
                                    users
                              ON 
                                    statuses.sender_id=users.userid
                              WHERE 
                                    statuses.user_id = '{$profile_data['userid']}' 
                              ORDER BY 
                              `statuses`.`date` DESC
                               ") or die(mysql_error());  

                                  while($status = mysql_fetch_array( $data ))//added this
                                  {
                                  $statusid = $status['status_id'];
                                  $date = $status['date'];
                                  $rate = $status['rate'];
                                  $comments = $status['comments'];
                                  $userid = $status['user_id'];
                                  $senderid = $status['sender_id'];
                                  $statusbody = $status['status'];
                                  $username = $status['username'];
                                  $firstname = $status['first_name'];
                                  $lastname = $status['last_name'];
                                  $photo = $status['photo'];

                                  ?>
<form action="" method="POST" role="form" enctype="multipart/form-data" id="statusupdate" class="facebook-share-box">
            <div class="share">
                <div class="panel panel-default">
                      <div class="panel-heading"><a href="<? echo 'http://basecentre.co.uk/',$status["username"]; ?>"><img alt="" align="left" hspace="20" height="70" width="70" src="<? echo 'http://basecentre.co.uk/userimages/',$status["photo"]; ?>"> </a> <font size="+2"><i class="icon icon-comment-o"></i></font> <a href="<? echo 'http://basecentre.co.uk/',$status["username"]; ?>"><font size="+2"><?php echo $status['first_name']; ?> <?php echo $status['last_name'] ; ?></font></a> | <i class="icon icon-clock-o"></i> <a rel="tooltip" href="#" data-original-title="<? echo "". date('F j, Y, g:i a', strtotime($status['date']) + 60*60) .""; ?>"><?php echo "<strong>". date('j F', strtotime($status['date']) + 60*60) ."</strong>"; ?></a></div>
                      <div class="panel-body">
                        <div class="">
                            <?php echo $status['status']; ?>
                        </div>
                      </div>
                        <div class="panel-footer">
                                <div class="row">
                                <div class="col-md-7">
                                Comment | Like
                                </div>
                                </div>
                        </div>
                    </div>
            </div>
        </form>
                               </br>


<? 
                                  }


    ?>  

I have built a new table for the comments. So I could go to a users profile, read a "status" and then I could add a comment to it... Here is that new table...

comment_id (auto-i)
status_id (added depending on which status you comment on. If the comment is on status id #5, the same number will be sent to this to connect the comments with the correct statuses)
sender_id (the id of the user who is sending the comment which would be $_session['userid'];
date (date the comment was sent)
rate (Doesn't matter yet)
comment (the actual comment written out).

I need to join the new comments table to the other two tables, somehow? I tried adding another "left join" but that didn't work? And I'm not too good with this stuff. My aim is to get each comment correctly displaying on its connected status, in order of the date posted. Most recent at the bottom... With the users info showing so you know who has posted the comment.

Hope this gives you a bit more understanding and I hope someone can help!! Sorry it's pretty long but you guys asked for a bit more info... Thanks!

Edit add database records:

statuses table:

CREATE TABLE IF NOT EXISTS `statuses` (
  `status_id` int(25) NOT NULL AUTO_INCREMENT,
  `user_id` int(25) NOT NULL,
  `sender_id` int(25) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `rate` int(25) NOT NULL DEFAULT '0',
  `comments` int(25) NOT NULL DEFAULT '0',
  `status` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`status_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=30 ;

--
-- Dumping data for table `statuses`
--

INSERT INTO `statuses` (`status_id`, `user_id`, `sender_id`, `date`, `rate`, `comments`, `status`) VALUES
(15, 11, 22, '2014-05-11 21:22:00', 0, 0, 'This is pretty damn cool! '),
(16, 11, 91, '2014-05-11 21:22:35', 0, 0, 'LOL how did you do this shit man?? Alll you gotta do now is add the comments ;) and likes! '),
(14, 11, 22, '2014-05-11 21:21:35', 0, 0, 'Hey budddy how are ya ? '),
(13, 11, 11, '2014-05-11 21:18:10', 0, 0, 'eerer'),
(11, 11, 11, '2014-05-11 21:10:33', 0, 0, 'Ho Ho!'),
(10, 11, 11, '2014-05-11 21:10:27', 0, 0, 'Hey hey ! '),
(28, 11, 11, '2014-05-12 00:47:02', 0, 0, 'LOL just another quick test ;) '),
(29, 22, 22, '2014-05-12 02:30:44', 0, 0, 'I should be able to delete this status... '),
(20, 11, 11, '2014-05-11 21:30:17', 0, 0, 'LOL WINNER'),
(21, 22, 11, '2014-05-11 23:31:18', 0, 0, 'Hey mate :D '),

(19, 11, 11, '2014-05-11 21:24:47', 0, 0, 'Not bad eh guys? ;P ');

Comments table:

--
-- Table structure for table `comments`
--

CREATE TABLE IF NOT EXISTS `comments` (
  `comment_id` int(100) NOT NULL AUTO_INCREMENT,
  `status_id` int(25) NOT NULL,
  `user_id` int(25) NOT NULL,
  `sender_id` int(25) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `rate` int(25) NOT NULL DEFAULT '0',
  `comment` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`comment_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

--
-- Dumping data for table `comments`
--

INSERT INTO `comments` (`comment_id`, `status_id`, `user_id`, `sender_id`, `date`, `rate`, `comment`) VALUES
(1, 28, 11, 11, '2014-05-12 01:23:58', 0, 'Hmmm shall we see if we can get this working too!? Comments, here we come! '),
(2, 28, 11, 22, '2014-05-12 02:55:33', 0, 'This is not aidans comment! another username should appear!');

Users table:

    CREATE TABLE IF NOT EXISTS `users` (
  `userid` int(25) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(25) COLLATE latin1_general_ci NOT NULL,
  `middle_name` varchar(25) COLLATE latin1_general_ci NOT NULL,
  `last_name` varchar(25) COLLATE latin1_general_ci NOT NULL,
  `email_address` varchar(50) COLLATE latin1_general_ci NOT NULL,
  `username` varchar(25) COLLATE latin1_general_ci NOT NULL,
  `password` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `photo` varchar(50) COLLATE latin1_general_ci NOT NULL DEFAULT 'default.png',
  `date1` varchar(25) COLLATE latin1_general_ci NOT NULL,
  `date2` varchar(25) COLLATE latin1_general_ci NOT NULL,
  `date3` varchar(25) COLLATE latin1_general_ci NOT NULL,
  `birthplace` varchar(50) COLLATE latin1_general_ci NOT NULL,
  `gender` varchar(25) COLLATE latin1_general_ci NOT NULL,
  `about` varchar(250) COLLATE latin1_general_ci NOT NULL DEFAULT 'This information has not yet been updated.',
  `user_level` enum('0','1','2','3','4') COLLATE latin1_general_ci NOT NULL DEFAULT '0',
  `signup_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `activated` enum('0','1') COLLATE latin1_general_ci NOT NULL DEFAULT '0',
  `location` varchar(30) COLLATE latin1_general_ci NOT NULL,
  `premium` enum('0','1') COLLATE latin1_general_ci NOT NULL DEFAULT '0',
  `blocked` varchar(5) COLLATE latin1_general_ci NOT NULL DEFAULT '0',
  `fr_alert` varchar(6) COLLATE latin1_general_ci NOT NULL DEFAULT '0',
  `um_alert` varchar(6) COLLATE latin1_general_ci NOT NULL DEFAULT '0',
  PRIMARY KEY (`userid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Membership Information' AUTO_INCREMENT=92 ;

Answer 1

Probably something like this? not exactly sure what your data looks like so it could be wrong.. join on the user that sent the comment and the status id and then order by comment DATE desc then status DATE desc so it'll give the most recent comments by the most recent posts

SELECT -- added alias to your tables so its easier to read
    s.status_id, 
    s.user_id,
    s.sender_id,
    s.date,
    s.rate,
    s.comments as s_comment,
    s.status,
    u.userid as u_id, 
    u.username as u_name, 
    u.first_name as u_first, 
    u.last_name as u_last, 
    u.photo as u_photo,
    o.userid as o_id, 
    o.username as o_name, 
    o.first_name as o_first, 
    o.last_name as o_last, 
    o.photo as o_photo,
    c.comment as c_comment
FROM statuses s
LEFT JOIN users u ON s.sender_id=u.userid
LEFT JOIN comments c ON c.user_id = u.userid AND c.status_id = s.status_id
LEFT JOIN users o on o.userid = c.sender_id
WHERE s.user_id = '{$profile_data['userid']}' 
ORDER BY c.date DESC, s.date DESC

give it a try and let me know what any issues are.. also if you could edit your post with a few lines of the actual data like some of the comments with a few posts and status and like one dummy user i can test this myself and clarify (would be LOADS easier)

Answer 2

This answer slightly changes some of the table structure but the core ideas behind it are the same.

Using the example I've created here http://sqlfiddle.com/#!2/c1fa6e/1

So basically you can INNER JOIN (results from both tables) the status table, against the users table twice, and use the AS operator to create alias names for them. This lets you join users against it to get the profile owners information, as well as the information of the person posting the status. (The example below shows only status that are posted on profile owner #1)

SELECT status.status_id, 
  profile_owner.username AS owner, 
  status_sender.username AS sender
  FROM status
  INNER JOIN users AS profile_owner
    ON profile_owner.user_id = status.user_id
  INNER JOIN users AS status_sender
    ON status_sender.user_id = status.sender_id
  WHERE profile_owner.user_id = 1;

Result

STATUS_ID OWNER     SENDER
1         User 1    User 2
2         User 1    User 4

To then add comments, you LEFT JOIN the comments table (there may not always be comments, in which case these fields will be null), then to get the user details of the commentor we INNER JOIN again the user table, against the commentors ID to get their details

SELECT status.status_id, 
  profile_owner.username AS owner, 
  status_sender.username AS sender,
  status.status,
  status_comment.comment_body,
  commentor.username AS commentor
  FROM status
  INNER JOIN users AS profile_owner
    ON profile_owner.user_id = status.user_id
  INNER JOIN users AS status_sender
    ON status_sender.user_id = status.sender_id
  LEFT JOIN status_comment
    ON status.status_id = status_comment.status_id
  LEFT JOIN users AS commentor
    ON status_comment.commentor_id = commentor.user_id
  WHERE profile_owner.user_id = 1;

Giving these results

STATUS_ID OWNER    SENDER  STATUS                         COMMENT_BODY                          COMMENTOR
1         User 1   User 2  Test status on User1 by User2  User3 commenting on status by User2.  User 3
1         User 1   User 2  Test status on User1 by User2  User4 commenting on status by User2.  User 4
2         User 1   User 4  Test status on User1 by User4  (null)  (null)

This shows User1 has 2 status posted on their profile, one was by User2 and had 2 comments, the other was by User4 and has no comments. You can then parse these results in PHP and display appropriately.

Once you break it down, it's fairly straight forward.

You can view it all in action here and play with it yourself on sqlfiddle.

Update: Using the database information you edited into your post, I have created an SQLFiddle to attempt to show you how to apply this to your existing database.

It was quite straight forward, you just seemed to have translated my example to your code slightly wrong. Below is the correct query with the appropriate table names changed.

SELECT statuses.status_id, 
  profile_owner.first_name AS owner, 
  status_sender.first_name AS sender,
  statuses.status,
  comments.comment,
  commentor.first_name AS commentor
  FROM statuses
  INNER JOIN users AS profile_owner
    ON profile_owner.userid = statuses.user_id
  INNER JOIN users AS status_sender
    ON status_sender.userid = statuses.sender_id
  LEFT JOIN comments
    ON statuses.status_id = comments.status_id
  LEFT JOIN users AS commentor
    ON comments.sender_id = commentor.userid
  WHERE profile_owner.userid = 11;

This pulls all the statuses posted on the profile of user id 11. It also pulls any comments those status may have.

**On a side note...**I'm unsure why you're storing the sender_id and user_id in the comments table. Your comment is already related to a status, so you know whom it's posted against, you only need to store who is making the comment.

Try to think of each entity as it's own object.

You have 3 objects

• User
• Status
• Comment

A status has a user who it's posted against (status.user_id), and the user who posted it (status.sender_id).

A comment has a status it's posted against (comments.status_id), and the user who posted it (comments.sender_id).

There is no need to store the status.userid again in the comments table, as you can always join it to get that information.