0111010001110000 0111010001110000 - 1 month ago 9
PHP Question

Requesting data in PDO from up to 3 different tables

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
enter image description here

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


enter image description here

Thanks in advance.

Answer

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();
}