1

I've table for hits that contains of 2 fields

id    time(TIMESTAMP)
1     2012-05-03 08:56:17
2     2012-04-26 03:22:20
3     2012-05-03 08:56:17

How can I count how many rows for each day for example for the past 10 days?

I mean what is the right query for it that produces:

2012-05-03 => 25 hits 2012-05-05 => 55 hits etc

Also, how can i arrange the returned array as jQuery flot array ?

any answers / articles regarding my questions would be great help

Thank you

Jonathan Eustace
  • 2,469
  • 12
  • 31
  • 54
Yusuf
  • 77
  • 2
  • 9
  • this what i've so far: "select count(id) as count, DATE(time) as hitday from hits group by hitday" and works fine, it shows day date and number of hits – Yusuf May 03 '12 at 17:26

2 Answers2

2

You want to use a Group By on the date field something like

select count(*), dateField from tablename 
group by dateField
jarchuleta
  • 1,231
  • 8
  • 10
2

Here is how I'd do it:

select count(id), DATE(time) as hitday from yourtable group by hitday

And regarding your jQuery thing, I am not very sure if I understand it or not. You want mysql to generate a jQuery parsable array for you? For that you definitely need to have a kinda middle man like php. Here is another thread which shows you how a select result is converted to json using php.

Community
  • 1
  • 1
Jermin Bazazian
  • 1,932
  • 2
  • 17
  • 20
  • Thanks for answer and no mysql won't generate the arrays for jquery flot, actually php does that by exporting special array from mysql returned array as json encode, but am not that good at it – Yusuf May 03 '12 at 16:44
  • your link is kinda weird :), please check it – Yusuf May 03 '12 at 17:01