I am making a chart that will show views, unique visitors and returning visitors of the website for each day. On my main page I am logging every visitors IP and date when it was recorded. For the chart to work I need 3 MySQL queries, each one showing one value for each day.
The first query would be something like this:
SELECT DATE_FORMAT(date,'%y-%m-%d'),COUNT(ip) FROM visits GROUP BY DATE_FORMAT(date,'%y-%m-%d')
This query will simply output nuber of ip adresses logged per day.
The two following queries will be harder. For the second one I need to count each IP adress only once per day
For the third one I need to count only IP adresses that are already in the database.
I found that the third one might be done using something like this
SELECT date, visits.ip FROM visits INNER JOIN (SELECT ip FROM visits GROUP BY ip HAVING COUNT(id) > 1) dup ON visits.ip = dup.ip
However I couldn’t figure out how to implement counting the records into this one.
How can I make the second and the third query?
I am also open to PHP solutions
Advertisement
Answer
I would rewrite your first query to use the date()
function to remove the time portion of the date:
select date(v.date) date_day, count(v.ip) visits from visits v group by date_day
Your second query is just count(distinct ...)
:
select date(v.date) date_day, count(distinct v.ip) distinct_visits from visits v group by date_day
The third query could make use of exists
:
select date(v.date) date_day, count(distinct v.ip) returning_visits from visits v where exists (select 1 from visits v1 where v1.ip = v.ip and v1.date < date(v.date)) group by date_day