I have a line graph on my admin panel which I am hoping to use to display payment information grouped by Yearly Quarters from 3 different tables as 3 different items.
I am currently gathering all results separately like this:
$quarter_invoice = array();
$quarter_ads = array();
$quarter_account = array();
$get_invoice = "SELECT SUM(txn_amount) as total, QUARTER(txn_date) as qt, YEAR(txn_date) as year
FROM ap_invoice_payments
GROUP BY year,qt";
if ($result = mysqli_query($conn, $get_invoice)) {
/* fetch associative array */
while ($row = mysqli_fetch_assoc($result)) {
$quarter_invoice[] = "{y: '".$row["year"]." Q".$row["qt"]."', item1: ".$row["total"]."},";
}
}
$get_ads = "SELECT SUM(transfer_amount) as total, QUARTER(transfer_date) as qt, YEAR(transfer_date) as year
FROM ap_acc_transfers WHERE `reference` LIKE '%Ad Campaign Purchase%'
GROUP BY year,qt";
if ($result = mysqli_query($conn, $get_ads)) {
/* fetch associative array */
while ($row = mysqli_fetch_assoc($result)) {
$quarter_ads[] = "{y: '".$row["year"]." Q".$row["qt"]."', item2: ".$row["total"]."},";
}
}
$get_account = "SELECT SUM(snappysites_fee) as total, QUARTER(txn_date) as qt, YEAR(txn_date) as year
FROM ap_acc_payments
GROUP BY year,qt";
if ($result = mysqli_query($conn, $get_account)) {
/* fetch associative array */
while ($row = mysqli_fetch_assoc($result)) {
$quarter_account[] = "{y: '".$row["year"]." Q".$row["qt"]."', item3: ".$row["total"]."},";
}
}
As I output this code, it all works fine and shows like this:
Array ( [0] => {y: '2016 Q1', item1: 692.00}, [1] => {y: '2016 Q2', item1: 809.00}, [2] => {y: '2016 Q3', item1: 290.00}, )
Array ( [0] => {y: '2016 Q3', item2: 8.00}, )
Array ( [0] => {y: '2016 Q2', item3: 1.00}, [1] => {y: '2016 Q3', item3: 2.50}, )
Although what I am trying to achieve is this type of result by combining all 3 queries:
{y: '2016 Q1', item1: 692.00, item2: 0, item3: 0},
{y: '2016 Q2', item1: 809.00, item2: 0, item3: 1.00},
{y: '2016 Q3', item1: 290.00, item2: 8.00, item3: 2.50},
Now I slightly have an idea on how to join tables although none of these tables share the same column information and for some of these results as you can see above, show NULL. Where as in my first attempt, rather than showing 0 if NULL, you don't get any results at all and I'm sure that might cause some problems wouldn't it?
If someone could point me in the right direction to obtain these type of results, that would be really helpful.
