How to merge 3 table data (SQL) in PHP to plot on dashboard?
<p>I'm trying to plot the fetched sql results on dashboard. Currently, I have 3 tables and details as follows:
1) customers 2) orders 3) exports</p>

<pre><code>customers:
id   | name | details | status    |
101  | ABCD | #123    | Delivered |
102  | EFGH | #456    | Delivered |

orders:
id | ordered_time        |order_id |
1  | 2016-10-04 11:11:05 | 101     |
2  | 2016-10-04 12:11:05 | 102     |
3  | 2016-10-04 09:11:05 | 101     |
4  | 2016-10-04 08:11:05 | 102     |

exports:
id | product     | status                | export_id  |
1  | something1  | Delivered             | 101        |
2  | something2  | Not Delivered         | 101        |
3  | something3  | Not Delivered         | 102        |
4  | something4  | Delivered             | 102        |
</code></pre>

<p><strong>Query1</strong> </p>

<p><code>SELECT distinct customers.name,customers.details,orders.order_id,ordered_time LEFT JOIN orders ON customers.id = orders.order_id WHERE orders.ordered_time = (SELECT Max(ordered_time) FROM orders WHERE  customers.id = orders.order_id) OR orders.order_id is NULL order  by id;</code></p>

<p><strong>Query 2</strong></p>

<p><code>SELECT distinct exports.product,exports.status FROM exports INNER JOIN customers ON customers.id=exports.export_id;</code></p>

<p>Both queries results separately. And most importantly, second query has multiple rows associated to one id of customers table. How do i format/obtain it to get as below: OR How do I merge above two queries to get the below result ?</p>

<pre><code>final data to be plotted on dashboard:
id | name | details | products              | ordered_time        | cust_id | status    |
1  | ABCD | #123    | something1,something2 | 2016-10-04 11:11:05 | 101     | Delivered |
2  | EFGH | #456    | something3,something4 | 2016-10-04 12:11:05 | 102     | Delivered |

  Working PHP code for single query as follows:
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbName", $username, $password);

    $conn-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $query = "SELECT distinct customers.name,customers.details,orders.order_id,ordered_time LEFT JOIN orders ON customers.id = orders.order_id WHERE orders.ordered_time = (SELECT Max(ordered_time) FROM orders WHERE  customers.id = orders.order_id) OR orders.order_id is NULL order  by id;";

    $result = $conn-&gt;prepare($query);
    $result-&gt;execute();
    $row_count = $result-&gt;rowCount();
    $jsonarray_data = array();

    if ($row_count &gt; 0) {
  while($row = $result-&gt;fetch(PDO::FETCH_ASSOC)) {
    $jsonarray_data = array();
    $jsonarray_data["name"] = $row["name"];
    $jsonarray_data["details"] = $row["details"];
    $jsonarray_data["order_id"] = $row["order_id"];
    $jsonarray_data["ordered_time"] = $row["ordered_time"];

    array_push($jsonarray, $jsonarray_data);
  }
}
    $tableData = array(
            "data" =&gt; $jsonarray
        );
    echo json_encode($tableData,JSON_UNESCAPED_SLASHES);
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e-&gt;getMessage();
    }
</code></pre>

<p>Could you please help me out here ?</p>