0

I have a mysql database that I store visitors in.

id        ip             date
--     -----------    ------------------   
1      192.168.0.1    2013-02-08 12:12:04
2      192.168.0.1    2013-02-08 14:30:00
3      192.168.0.1    2013-02-09 16:43:46
4      192.168.0.1    2013-02-10 08:35:02

This a simplified version of that i am using but the concept is the same. I want to be able to get the number of visitors and display them in a chart by time interval. I use the following array design to populate the chart, where 0,1,2,3 is the x axis and 24, 74, 26, 51 is the y axis.

 [[0, 24], [1, 74], [2, 26], [3, 52]]

I have tried to use the queries from stack question like this for instance but i cant get it to work with the Group by and that. Any help will be appreciated.

 $query = "SELECT COUNT(id) FROM  `unique_visitors_user` WHERE DATE(date) BETWEEN '2013-02-08' AND '2013-02-10' ";
Community
  • 1
  • 1
Ukjent
  • 823
  • 2
  • 9
  • 26

2 Answers2

1

Try this one:

SELECT 
    DATE_FORMAT(date, '%Y-%m-%d') Date1, COUNT(*) AS Visitors
FROM
    unique_visitors_user
WHERE
    date BETWEEN STR_TO_DATE('%Y-%m-%d', '2013-02-08') AND STR_TO_DATE('%Y-%m-%d', '2013-02-10')
GROUP BY Date1;
जलजनक
  • 3,072
  • 2
  • 24
  • 30
1

This one should count all visits by day

SELECT COUNT(id)
FROM `unique_visitors_user`
WHERE date >= "2013-01-01" 
AND date < DATE_ADD("2013-01-01", INTERVAL 1 DAY)
GROUP BY DAY(date)
ikkez
  • 2,052
  • 11
  • 20
  • Thx, How can i count visits for several days. Like for example between two dates ? – Ukjent Feb 10 '13 at 21:49
  • you can use 'INTERVAL 2 DAYS' (or DAY?!) to count visits within 48 hours... You can also interval on hours, weeks, months, or years... I havn't tested the query here on my iPad :D but i used a similar query,.. So read about DATE_ADD and DATE_SUB in the manual... i guess you're very close to the right solution ;) – ikkez Feb 10 '13 at 21:57