title: Joining a 3rd MySQL table (redone)
body: <p>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. </p>
  
  <p>I am currently working on a Posts &amp; 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...</p>
  
  <pre><code>Users
  Statuses
  Comments
  </code></pre>
  
  <p>Users contains the standard...</p>
  
  <pre><code>userid
  username
  first_name
  last_name
  photo
  </code></pre>
  
  <p>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...</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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...</p>
  
  <pre><code>//// 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'];
  
                                    ?&gt;
  &lt;form action="" method="POST" role="form" enctype="multipart/form-data" id="statusupdate" class="facebook-share-box"&gt;
              &lt;div class="share"&gt;
                  &lt;div class="panel panel-default"&gt;
                        &lt;div class="panel-heading"&gt;&lt;a href="&lt;? echo 'http://basecentre.co.uk/',$status["username"]; ?&gt;"&gt;&lt;img alt="" align="left" hspace="20" height="70" width="70" src="&lt;? echo 'http://basecentre.co.uk/userimages/',$status["photo"]; ?&gt;"&gt; &lt;/a&gt; &lt;font size="+2"&gt;&lt;i class="icon icon-comment-o"&gt;&lt;/i&gt;&lt;/font&gt; &lt;a href="&lt;? echo 'http://basecentre.co.uk/',$status["username"]; ?&gt;"&gt;&lt;font size="+2"&gt;&lt;?php echo $status['first_name']; ?&gt; &lt;?php echo $status['last_name'] ; ?&gt;&lt;/font&gt;&lt;/a&gt; | &lt;i class="icon icon-clock-o"&gt;&lt;/i&gt; &lt;a rel="tooltip" href="#" data-original-title="&lt;? echo "". date('F j, Y, g:i a', strtotime($status['date']) + 60*60) .""; ?&gt;"&gt;&lt;?php echo "&lt;strong&gt;". date('j F', strtotime($status['date']) + 60*60) ."&lt;/strong&gt;"; ?&gt;&lt;/a&gt;&lt;/div&gt;
                        &lt;div class="panel-body"&gt;
                          &lt;div class=""&gt;
                              &lt;?php echo $status['status']; ?&gt;
                          &lt;/div&gt;
                        &lt;/div&gt;
                          &lt;div class="panel-footer"&gt;
                                  &lt;div class="row"&gt;
                                  &lt;div class="col-md-7"&gt;
                                  Comment | Like
                                  &lt;/div&gt;
                                  &lt;/div&gt;
                          &lt;/div&gt;
                      &lt;/div&gt;
              &lt;/div&gt;
          &lt;/form&gt;
                                 &lt;/br&gt;
  
  
  &lt;? 
                                    }
  
  
      ?&gt;  
  </code></pre>
  
  <p>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...</p>
  
  <pre><code>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).
  </code></pre>
  
  <p>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. </p>
  
  <p>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! </p>
  
  <p>Edit add database records:</p>
  
  <p>statuses table:</p>
  
  <pre><code>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 ');
  </code></pre>
  
  <p>Comments table:</p>
  
  <pre><code>--
  -- 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!');
  </code></pre>
  
  <p>Users table:</p>
  
  <pre><code>    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 ;
  </code></pre>