CODEWITHSUNDEEP
phpmysqlpdo
wobsoriano
wobsoriano

Reputation: 13462

How to echo values from another table?

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!

Upvotes: 0

Views: 768

Answers (3)

wobsoriano
wobsoriano

Reputation: 13462

I got it working already, here is my sql query:

SELECT tbl_records.*, tbl_residents.fname, tbl_residents.lname FROM tbl_records LEFT JOIN tbl_residents ON tbl_residents.stud_id = tbl_records.stud_id

Thanks everyone!

Upvotes: 0

Tamil Selvan C
Tamil Selvan C

Reputation: 20209

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

Upvotes: 1

G-Wak
G-Wak

Reputation: 63

What you want to achieve seems to be a foreign key relation - you have to use JOINS. In your example:

SELECT r.*, g.fullname FROM tbl_records r LEFT JOIN tbl_guard g ON r.`guard_id`=g.`guard_id`

Read MySQL documentation, you will need this more often!

Upvotes: 1

Related Questions