PHP mysql Joining 3 tables and displaying the correct information in the right places

Question

So Today I need help with joining three tables I think I have succesfully joined them and they display text but not the way I want them to.

I want them to display like this

Name of the user who wrote the status and timestamp when they posted it. This stuff is correct

Status This is also correctly displayed

And here I want a comment to the status to be displayed with the name of the user who wrote it

I can get the comment to display on the correct status but on every other status there is the name of the user is displayed in the comments and also the timestamps are all the same for example the comment time and the status time is the same.

Here is the code that I use to get the information from MySQL

$test5 ="SELECT status, 
    status.pubdate, 
    firstname,
    surname,
    komment,
    komment.pubdate 
FROM status 
INNER JOIN user ON status.user_id = user.user_id 
LEFT JOIN komment ON status.status_id = komment.status_id 
ORDER BY status.pubdate DESC";

and here is the place where it is supposed to display it

$stmt = $db->prepare($test5);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    echo "<section class='statusar'>". "<b>".$row['firstname']." ".$row['surname']."<time>".$row['pubdate']."</time>"."</b>" ."<article>";
    echo $row['status'];
    echo "</article>";


        echo "<section class='komment'><b>".$row['firstname']." ".$row['surname']."<time>".$row['pubdate']."</time></b>"."<article>";
        echo $row['komment'];
        echo "</article></section></section>";
}

I tried to do a if(empty($row['komment']) but that didn't work because then it did a comment box within a box I also tried !empty and that made even more boxes

So how do I make it display the correct time on the comment and the status and make them only display comments on status that have comments and display the correct name for the one who wrote the comment and also I tried $row['komment.pubdate'] but then it displayed an error

I hope you guys understand what I want to do if not ask me and I can try to explain it better

EDIT

User TABLE    
user_id int unsigned AUTO_INCREMENT Primary index
firstname varchar 
surname varchar
username Unik 
password varchar

Status TABLE
status_id int unsigned auto_increment primary
user_id int unsigned index
status varchar
pubdate datetime

Komment TABLE
komment_id int unsigned auto_increment primary
status_id int unsigned index
user_id int unsgined index
komment varchar
pubdate datetime

All the tables also have relations like this

User Table          Status Table         Komment Table
 user_id---Relation---user_id--------------user_id
                     status_id------------status_id

EDIT 2

So now I have gotten one thing correct so if there is no comment on the post it doesnt print anything but I still need to get the comment user name right and I still need the status to show the correct pubdate and the comment to show their own pubdate

EDIT 3

So I fixed one thing I added status.pubdate AS pubdate1 so now I can show the status updates own date and the comments own date

But I still have some problems like if I add a comment from user 1 on user 2s post the comment isnt shown but if I add comment from user 2 on user 2s post it then shows the comment I dont know how I can fix this help please!

EDIT 4

Never mind fixed that now I just need to fix one thing and that is to show the correct username for the comments

I tried to add firstname and surname to the comment table but when I tried to make a relation to between the user table and the comment table it said something about foreign keys

Error foreign key relation could not be added! #1452 - Cannot add or update a child row: a foreign key constraint fails (`projekt`.`#sql-15f8_8d7`, CONSTRAINT `#sql-15f8_8d7_ibfk_1` FOREIGN KEY (`firstname`) REFERENCES `komment` (`firstname`) ON DELETE CASCADE ON UPDATE CASCADE)

Answer 1

Since the authors of status and comment can (most probably will?) be different users, you need to join the user table twice.

SELECT 
    s.status_id
    s.status, 
    s.pubdate AS status_pubdate, 
    sa.firstname AS sa_firstname,
    sa.surname AS sa_surname,
    k.komment,
    k.pubdate AS komment_pubdate,
    ka.firstname AS ka_firstname,
    ka.surname AS ka_surname,
FROM 
    status s 
INNER JOIN user sa ON s.user_id = sa.user_id 
LEFT JOIN komment k ON s.status_id = k.status_id 
LEFT JOIN user ka ON k.user_id = ka.user_id 
ORDER BY s.pubdate DESC

Note the introduced table aliases s (for status), sa (for status author), k (for komment) and ka (for komment author). By using aliases, you can join the same table twice without conflicting (ambiguous) field names. The query above joins the user table twice, sa holds the status' author information, ka the komment's author information.

This way, the query will retrieve one row of data per status/comment combination. It would be easy to use with your processing code, IFF there can only be one comment per status.

$stmt = $db->prepare($test5);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    echo "<section class='statusar'>". "<b>".$row['sa_firstname']." ".$row['sa_surname']."<time>".$row['status_pubdate']."</time>"."</b>" ."<article>";
    echo $row['status'];
    echo "</article>";

    echo "<section class='komment'><b>".$row['ka_firstname']." ".$row['ka_surname']."<time>".$row['komment_pubdate']."</time></b>"."<article>";
    echo $row['komment'];
    echo "</article></section></section>";
}

However, I assume there can be multiple comments per status. In that case, you might want to split up the query. Still, the above query will work and retrieve all comments for all statuses, but the status' information will be repeated for each comment which means you would need to update your processing code to something like this:

$stmt = $db->prepare($test5);
$stmt->execute();
$last_processed_status_id = null;
$section_open = false;
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    if ($row['status_id'] != $last_processed_status_id) {
        if ($section_open) { echo "</section>"; $section_open = false; }
        echo "<section class='statusar'>" . "<b>" . $row['sa_firstname'] . " " . $row['sa_surname'] . "<time>" . $row['status_pubdate'] . "</time>" . "</b>" . "<article>";
        echo $row['status'];
        echo "</article>";
        $last_processed_status_id = $row['status_id'];
        $section_open = true;
    }

    echo "<section class='komment'><b>".$row['ka_firstname']." ".$row['ka_surname']."<time>".$row['komment_pubdate']."</time></b>"."<article>";
    echo $row['komment'];
    echo "</article></section>";
}
if ($section_open) { echo "</section>"; }

Answer 2

use Left outer join

$test5="select a.firstname,a.surname,b.pubdate,c.pubdate 
from user a 
left outer join status b on a.user_id=b.user_id 
left outer join komment c on b.status_id=c.status_id 
order by b.pubdate";

Answer 3

Update ur Query like this

$test5 ="SELECT status, 
    status.pubdate, 
    firstname,
    surname,
    komment,
    komment.pubdate 
FROM status 
INNER JOIN user ON status.user_id = user.user_id 
LEFT JOIN komment ON status.user_id = komment.user_id AND status.status_id = komment.status_id 
ORDER BY status.pubdate DESC";

check left inner join.. added one condition.. status.user_id = komment.user_id

it will help u.. as u want to show ur records.