CODEWITHSUNDEEP
phpmysqlsqldatabase
mtrip
mtrip

Reputation: 67

PHP Retrieve information from five different tables with correct order

I develop a chat system where students and staff can exchange different messages. I have developed a database where we have five tables: the staff table, the student, the message and two mapping tables the staff_message and stu_message. These tables contain only the student/staff id and the message id.

My problem is that I cannot order the messages. I mean that I cannot figure out how can I make one SQL statement that will return all messages and be ordered by for example the ID. The code that I have made is this:

$qu = mysqli_query($con,"SELECT * FROM stu_message");
while($row7 = mysqli_fetch_assoc($qu)){
    $que = mysqli_query($con, "SELECT * FROM student WHERE studentid =".$row7['stu_id']);

    while($row8 = mysqli_fetch_assoc($que)) {
        $username = $row8['username'];
    }

    $query3 = mysqli_query($con, "SELECT * FROM message WHERE id=".$row7['mid']);
    while($row6 = mysqli_fetch_assoc($query3)) {
        echo $row6['date']."<strong> ".$username."</strong> ".$row6['text']."<br>";             
    }   
}

$query2 = mysqli_query($con, "SELECT * FROM staff_message");    
while($row3 = mysqli_fetch_assoc($query2)){
    $query = mysqli_query($con, "SELECT * FROM staff WHERE id =".$row3['staff_id']);

    while($row5 = mysqli_fetch_assoc($query)) {
        $username = $row5['username'];
    }

    $query3 = mysqli_query($con, "SELECT * FROM message WHERE id=".$row3['m_id']);
    while($row6 = mysqli_fetch_assoc($query3)) {
        echo $row6['date']."<strong> ".$username."</strong> ".$row6['text']."<br>";             
    }   
}
?>

The result is different from that I want. To be more specific first are shown the messages from the students and then from the staff. My question is, is there any query that it can combine basically all these four tables in one and all messages will be shown in correct order? for example by the id? Thank you in advance!

Upvotes: 0

Views: 56

Answers (1)

Barmar
Barmar

Reputation: 781078

First, use JOIN to get the username corresponding to the stu_id or staff_id, and the text of the message, rather than separate queries.

Then use UNION to combine both queries into a single query, which you can then order with ORDER BY.

SELECT u.id, u.text, u.username
FROM (
    SELECT s.username, m.text, m.id
    FROM message AS m
    JOIN stu_message AS sm ON m.id = sm.mid
    JOIN student AS s ON s.id = sm.stu_id
    UNION ALL
    SELECT s.username, m.text, m.id
    FROM message AS m
    JOIN staff_message AS sm ON m.id = sm.m_id
    JOIN staff AS s ON s.id = sm.staff_id
) AS u
ORDER BY u.id

Upvotes: 2

Related Questions