从最多3个不同的表中请求PDO中的数据

Question

What am I trying to do?

I'm trying to display Invoices data on the Dashboard page of my web application. The tiny road block that I have came upon is displaying data based on integers. For example;

Invoice 1 -> Bill User 1

What I'm trying to achieve is having that integer for either a User or Company to whom the Invoice is assigned display their name, example;

BillContact->1 (becomes) BillContact->John Smith 

Here is the current output of my data in PHP from PDO

If I was using old school MySQL I would write secondary query but I know this isn't much needed in PDO as you can just use UNION or JOIN, problem is I'm not very skilled with PDO or UNION/JOIN function, and would like to use this question as a way to learn how they work and use them in my code for future database queries.

As you can see Contact Billed are there assigned values, which I wish to replace with the Contact's name from Contacts table in the same Database.

Here is my PDO code to pull the current data.

$database->query('SELECT * FROM invoices LIMIT 10');
$rows = $database->resultset();

echo ... table html here

foreach($rows as $row)
                    {
                    ... td html here
                    }

echo ...

And here is the values I wish to pull from Contacts/Companies tables depending on id being passed.

contacts           companies
.firstname         .companyname       
.lastname

Thanks in advance.

Answer 1

Here's a simple join to solve your issue.

try
{
$s = $conn->prepare("SELECT inv.*,c.* from invoices inv, contacts c where inv.contact_billed = c.id");
$s->execute();
}
catch(PDOException $e)
{
echo $e->getMessage();
}