Sorting data from database, to use in chart. PHP

I am using highcharts for showing charts on my website. I have a database table, named "advertisement_clicks". I want t开发者_JAVA技巧he data from that table, to go into a highchart. but, I want the data to seperate when a new day come.

A picture of my database: http://awesomescreenshot.com/0cfia2md0

The way to put the # of clicks (# of rows found) in the highchart is this way:

    series: [{
                name: 'Clicks',
                data: [1, 2, 3]
            }],

The way to set the day in highcharts is in this way:

 xAxis: {
                    categories: ['Today', '09-06', '10-06']
                },

I want to limit the data, by X number of days.

I was thinking of doing it with Arrays, but I am just not sure how to. Please help! :)

---

You can limit the number of days by filtering on date column, ie WHERE 'date' BETWEEN x AND y and to get data for days use GROUP BY 'date'. So your query would be something like

SELECT
  count(id) AS Clicks,
  date AS categories
FROM advertisement_clicks
WHERE date BETWEEN x AND y
GROUP BY date
ORDER BY date DESC

Replace x and y with the date range you want to create chart for.

Or if you really want always query for x last days then use (assuming mySQL)

date >= CURRENT_DATE - INTERVAL x DAY

in the WHERE instead of date BETWEEN x AND y.

In case the date column is infact timestamp you must extract the date part for grouping, ie assuming mySQL GROUP BY DATE(date)

Format the date in the client side (for "categories" label), it would be easier than to do it in SQL (althought most engines support date formating functions).