combining-two-mysql-queries-into-one-json-array-in-php

Question

  $sql1 =  "select distinct date, count(id) from windows where start_date between '2017-01-01' and '2017-10-31' and (start_date!='0000-00-00') group by date";
  $sql2 =   "select distinct date, count(id) from AIX where start_date between '2017-01-01' and '2017-10-31' and (start_date!='0000-00-00') group by date";

I have these two queries which return output in the form of (date,ID). What i want is to merge these two tables so that i get result in one array as (date,ID1,ID2) where ID1 is of 'windows' table and ID2 is of 'AIX' table and according to date they must print the ID's. I tried using "join" but that didn't work. I used array_merge() in php to combine the results to one json array but that didn't work either. Is there a way to solve this problem?

may be this will help https://stackoverflow.com/questions/17850353/array-merge-on-multidimensional-array — geeth, Nov 02 '17 at 04:43

Tim Biegeleisen · Accepted Answer · 2017-11-02T04:58:53.187

Because you want to combine aggregations from two different tables, I think you will have to aggregate separately first and then somehow combine the results. In the query below, I aggregate over your two tables in subqueries and then join them on the date. This should work assuming that both tables have all dates present and have the same dates.

SELECT
    t1.date,
    t2.cnt1,
    t3.cnt2
FROM calendar t1
LEFT JOIN
(
    SELECT date, COUNT(id) AS cnt1
    FROM windows
    WHERE start_date BETWEEN '2017-01-01' AND '2017-10-31'
    GROUP BY date
) t2
    ON t1.date = t2.date
LEFT JOIN
(
    SELECT date, COUNT(id) AS cnt2
    FROM AIX
    WHERE start_date BETWEEN '2017-01-01' AND '2017-10-31'
    GROUP BY date
) t3
    ON t1.date = t3.date;

As I mentioned above, if either table be completely missing a date, then you will have to do more work. In this case, you could resort to joining with a calendar table to include the missing dates.

Edit:

I have updated the query slightly to use a calendar table. To make this query work, just define a table calendar with a date column containing the full range of dates to cover both of your tables.

Thank you Tim, but the problem is both the tables have different dates and missing dates. How do we join with a calendar table? can you elaborate? — Monisha, Nov 02 '17 at 04:49

combining-two-mysql-queries-into-one-json-array-in-php

1 Answers1