CODEWITHSUNDEEP
phpmysql
GePraxa
GePraxa

Reputation: 67

List all comments and get the username of the author of another table in PHP

I'm adding a feedback system to a system of students, the idea is that teachers have access to a list of students and can leave a comment about that student.

Table masters:

id
username

Table comments:

id
id_master
id_student
text

Then I go to student.php?id=1 show all results in table comments:

<?php
// See comments student
function SeeComments($id) { 
 $db = DB::getInstance();
 $query = $db->query("SELECT * FROM comments WHERE id_student = ?", array($id));
 $results = $query->results();
 return ($results);
}

$id = $_GET['id'];
$Data = SeeComments($id);

foreach ($Data as $v1) {
 echo "<b>Username - Text</b><br>";
 echo $v1->id_master." - ".$v1->text."<br>";
 echo "---------------------<br>";
}
?>

As I can get the username of the table masters to display the results instead of the id like:

echo $v1->username." - ".$v1->text."<br>";

Upvotes: 1

Views: 119

Answers (1)

M. Eriksson
M. Eriksson

Reputation: 13635

You can do an inner join to get the related data from the masters table.

$query = $db->query("SELECT comments.*, masters.username 
    FROM comments 
    INNER JOIN masters ON comments.id_master = masters.id 
    WHERE comments.id_student = ?", array($id));

Now you should be able to get the username with: $v1->username.

You can read more about inner join's here: http://www.w3schools.com/sql/sql_join_inner.asp

Upvotes: 2

Related Questions