0

I'm looking for a solution to get a cursive graph and extract the data right away from MYSQL to keep the memory and less code in PHP to convert it to JSON data and convert into a simple graph with DATE and values.

Maybe somebody passed trough this and can give me a solution.

I need to group by MONTH and by WEEK but based on the date from rows.

I mean:

SELECT p.code, SUM(ii.quantity) as qty, DATE_FORMAT(i.created, '%Y-%m') as date
FROM products p 
LEFT JOIN inv_items ii ON p.id=ii.product_id
LEFT JOIN inv i ON i.id=ii.invid
WHERE p.id = 31910
GROUP BY YEAR(i.created), MONTH(i.created)

So the Result is:

code    qty date
20418101    1   2014-07 
20418101    2   2014-08 
20418101    1   2014-09 
20418101    4   2015-06 
20418101    6   2016-05 
20418101    1   2016-11 
20418101    1   2017-01 
20418101    1   2017-03

But what i need is to keep it cursive, i mean from (2014-07 to 2014-12) (2015-01 to 2015-12) (2016-01 to 2016-12) (2017-01 to CURDATE)

On short selection i need it to convert to week cursive view for example in 2 months (2014-07-WEEK1,2,3,4,5,6,7,8)

Is there a way to get right away from MYSQL this query without passign trough PHP to sanitize ?

I appreciate any word. The example from the picture is an example of the real output from MYSQL what i want is to get cursive months. enter image description here

ndAR
  • 371
  • 2
  • 4
  • 15
  • Maybe I'm not understanding the question; but wouldn't a date function for week and a case statement work? YearWeek() or week()? your date format is pulling out year/month but you need week so why not use one of the week functions? So if you're trying to group weeks 1-8 together then `case when Week(date) < 9 then 'GROUP 1' when Week(date) >=9 and <=16 'GROUP 2' end` but I'm not sure I follow yet; maybe the case isn't needed at all. Using your sample data show some expected results to clairify the question. – xQbert Jun 19 '17 at 14:41
  • Hello @xQbert i added the chart for a better understanding, the week is for later usage and only for short views, this view has 3 years and i need to get the full month of year, as you can see now month 10,11 and 12 from 2014 is missing but i need to keep the same lines. – ndAR Jun 19 '17 at 14:48
  • So are you missing invoices with those dates in your data? If so then you want the database to return data for dates that don't exist? (it's like asking "give me all the data for which you don't have data.. The DB doesn't know what's missing!) If that's the case then you need a date/numbers table or you have to generate dates between date ranges so that all months are accounted for as done:https://stackoverflow.com/questions/14811568/how-to-get-a-list-of-months-between-two-dates-in-mysql you then use this as the 1st table and left join to it. thus ensuring all months are accounted for. – xQbert Jun 19 '17 at 14:54

0 Answers0