Not sure if this is the correct forum for this question but here goes..
I'd like to extract sales, deliveries and returns statistics from my database for the past 12 months and display in a graph. I'm using jpgraph and have that side organised but i'm unsure on the best way to align the data.
The following 2 lines get my deliveries and returns but obviously there are not always the same products delivered or returned so the arrays may never line up. I can't use the product table to do this as the product names are always changing.
Code: Select all
$query = "SELECT product_id, SUM(amount) AS Amount, price, date, client_id, statement_id, total FROM deliveries WHERE date BETWEEN '$selectStart' AND '$today' AND client_id = '$company_id' GROUP BY product_id";
$query = "SELECT product_id, SUM(amount) AS Amount, return_date, price, client_id, date, statement_id, total FROM returns WHERE date BETWEEN '$selectStart' AND '$today' AND client_id = '$company_id' GROUP BY product_id";Can this be done purely in SQL or will I need to manipulate the data using arrays?Month ->
Product ->
Delivered (define 0 if not in delivered that month)
Returned
Sold
Any guides or pointers would be great.
tia, Will./