Select records, and group by week into an array?

[heavencore]

OK, i am a bit confused how to attempt this, i have a few ideas, but none are very efficient.

 

I have a table in a mySQL database called "tblOrderArchive" which is a table of orders on my site, i have a graph class which requires two arrays of values one for X axis one for Y, ok easy enough so far, but the scenario is as follows:

 

I need to make a graph as follows:

X axis - Week by Week

Y axis - Orders Taken

Graphname - "Orders per week"

 

To do so i need to generate arrays as follows:

xAxisData('21/05/2006','28/05/2006')

YAxisData('234','242')

 

etc

 

What would be the best way to get this data from my table in a week by week format? the table Schema is as follows:

 

id - int

client_fk - int

date - DataTime Type

htmlField - text

value - Decimal(11,2)

 

Any help would be VERY gratefull. 

 

Regards,

 

HeavenCore

[Wildbug]

There's a YEARWEEK() MySQL function that you can use to group your dates into weeks.

 

Something like:

SELECT YEARWEEK(date) AS week,MIN(date),SUM(value) FROM tblOrderArchive GROUP BY week

# or:

SELECT YEARWEEK(date) AS week,* FROM tblOrderArchive ORDER BY week,date

 

...depending on what you want.

[heavencore]

thanks alot m8!

 

i adpated that as follows:

 

SELECT YEARWEEK( date ) AS Week, Sum( total ) 
FROM orders
GROUP BY Week

 

However the date Returned is listed as

 

yyyyww eg 3rd week in 2007 would be 200703

 

i'm guessing date() can be used to convert this to a more recognisable date eg: 21/01/2007 dd/mm/yy?

 

Can you shed any light on this please?

 

Cheers.

[Wildbug]

That's why I suggested MIN(date), to get the smallest date on record in that week for your orders for that particular year/week.

[Wildbug]

Or you could use something like:

 

date - INTERVAL WEEKDAY(date) DAY AS firstday

# ...or...

date - INTERVAL(DAYOFWEEK(date)-1) DAY AS firstday

 

...using existing dates to find the first day of the week.