CODEWITHSUNDEEP
phpmysqlarrays
user5434892
user5434892

Reputation:

Join 3 tables to make a array in PHP

I'm facing a problem with a query in mySQL.

Actually my mySQL query looks like this:

___Invoices table

|--------|---------------|
| IVC_Id | IVC_BookingId |
|--------|---------------|
| 1      | 99            |
|--------|---------------|

___Bookings table

|--------|--------------|---------------|---------------|
| BOO_Id | BOO_ClientId | BOO_CompanyId | BOO_BillingId |
|--------|--------------|---------------|---------------|
| 99     | 44           | 46            | 0             |
|--------|--------------|---------------|---------------|

__Kardex table

|--------|----------|----------|-------------|
| KDX_Id | KDX_Type | KDX_Name | KDX_Company |
|--------|----------|----------|-------------|
| 44     | client   | Luka     |             |
| 46     | company  |          | Google      |
| 78     | billing  | Georgia  |             |
|--------|----------|----------|-------------|

How can I link these 3 tables ? What I need at the final is an array like this:

$contact = array (
    "client"  => array("KDX_Id" => 44, "KDX_Name" => "Luka"),
    "company" => array("KDX_Id" => 46, "KDX_Company" => "Google"),
    "billing"   => array() // Noting here because no billing contact was link
);

I started by the query:

SELECT *
 FROM ___Invoices 
 JOIN ___Bookings 
 ON ___Invoices.IVC_BookingId=___Bookings.BOO_Id
 JOIN ___Kardex 
 ON ___Bookings.BOO_ClientId =___Kardex.KDX_Id
 OR ___Bookings.BOO_CompanyId =___Kardex.KDX_Id
 OR ___Bookings.BOO_CompanyId =___Kardex.KDX_Id
 WHERE IVC_Id=1

But it doesn't work.

Any help on this please ?

Thanks.

Upvotes: 0

Views: 246

Answers (1)

Nic3500
Nic3500

Reputation: 8621

Use the UNION operation. This will only work if all 3 queries return the same number of results. Like so:

SELECT KDX_Id, KDX_Type, KDX_Name FROM (
    (SELECT K.KDX_Id, K.KDX_Type, K.KDX_Name, B.BOO_Id
     FROM ___Kardex AS K
     JOIN ___Bookings AS B ON K.KDX_Id = B.BOO_ClientId)
   UNION
    (SELECT K.KDX_Id, K.KDX_Type, K.KDX_Company, B.BOO_Id
     FROM ___Kardex AS K
     JOIN ___Bookings AS B ON K.KDX_Id = B.BOO_CompanyId)
   UNION
    (SELECT K.KDX_Id, K.KDX_Type, K.KDX_Name, B.BOO_Id
     FROM ___Kardex AS K
     JOIN ___Bookings AS B ON K.KDX_Id = B.BOO_BillingId) ) AS TEMP
JOIN ___Invoices AS I ON I.IVC_BookingId = TEMP.BOO_ID
WHERE I.IVC_Id = 1

The result will be something like:

ID    TYPE      NAME (or company)
44    client    Luka             
46    company   Google

Then in your PHP code, loop on every line of result, and use the type (second column) to fill your array.

$contacts = array($results[1] => array($results[0] => $results[1]));

Note: I tested the query, not the PHP, but you get the idea.

Notes on edit: the result of the unions is now defined as TEMP. TEMP is then joined to table ___Invoices, to allow to select which invoice you want to filter on.

Upvotes: 1

Related Questions