GePraxa GePraxa - 2 months ago 6
MySQL Question

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>";

Answer

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

Comments