如何回显另一个表中的值？

Question

Hello I have 2 tables named tbl_guard and tbl_records. The tbl_guard has columns guard_id and fullname, while tbl_records has guard_id also. And here is my code:

$query = "SELECT * FROM tbl_records";
$stmt = $dbc->prepare($query);
$stmt->execute();

while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
    extract($row);
    echo "<table>";
    echo "<tr>";
    echo "<td>Name</td>";
    echo "</tr>";
    echo "<tr>";
    echo "{$guard_id}";
    echo "</tr>";
}
echo "</table>";

What I want to do is instead of the guard_id being echoed there in the loop, I want the fullname in the tbl_guard table.

Your help would be very much appreciated!

Answer 1

Use JOIN

$query = "SELECT tbl_records.*, tbl_guard.fullname FROM tbl_records LEFT 
JOIN tbl_guard ON tbl_guard.guard_id = tbl_records.guard_id"; 

In PHP

while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
.
.
.
 echo "{$fullname}";
}