Pageview and visitor counter stats SQL

Databases
1

Hello,

My friend has two websites and asked me to build a visitor counter for each site in one MySQL table. It had to count pageviews as well as unique visitors. The table has three columns; site, hit and type. The site column is just an ID (tinyint) for the website. The hit column is a datetime that holds the time the visitor has visited a page. The type column is a tinyint column that can have two values; 1 and 0. 1 meaning a new visitor, 0 meaning a pageview.

I want to make a statistics page (1 per website) that can show the number of unique visitors and pageviews in certain time intervals. Time intervals like “the past 60 minutes” and show it per minute or “the past 7 days” and show it per day. I thought this should be easily possible, but I’m having trouble building an SQL query for it.

I believe it can be done, but I’m stuck at this for two days now. Maybe I’m overlooking something? I’m not an SQL expert, but I always try to become one at least :wink: Still, I really can’t seem to solve this puzzle.

Thanks for the help in advance :slight_smile:

2

Please post the query you’ve got so far, and tell us what’s wrong with that query (what is it doing, and what should it be doing instead).

3

Hey guido2004, thanks for your reply.

This query is the closest I got to what I want, but without the time interval (I just select the last 7 days, but I also need the last 60 minutes for example) and I realised I need something else. I need the lowest number of visitors and the highest number of hits, it should be possible right?

SELECT * FROM (SELECT
COUNT(*) AS visits,
SUM(CASE WHEN type = 1 THEN 1 ELSE 0 END) AS visitors,
DATE(hit) AS hit
FROM hits
WHERE site = :site
GROUP BY DATE(hit)
ORDER BY hit DESC
LIMIT 7) AS result ORDER BY hit ASC

This returns the number of unique visitors, total amount of pageviews and their date. “:site” is ofcourse added with PDO bind param. As you can see I’ve also fixed the alignment of the dates, so today is at the end in the array that’s returned.

Thank you in advance for the help :slight_smile:

4

More info:

If I would change

SELECT * FROM

to

SELECT visits, visitors, hit, MIN(visitors) as minvisitors, MAX(visits) as maxvisits FROM

it will ofcourse return one row as MIN and MAX are aggregate functions.

I could also do a foreach loop with PHP ofcourse, making the query easier and maybe the script less heavy for the database? Is that a better idea? If so, then I would only need to know how to select the past 30 minutes/12 hours.

The final result would be presented in a (HTML) table like this;

2013-12-01     12 visitors     24 visits
2013-11-30     [COLOR="#FF0000"]11 visitors[/COLOR]     20 visits
2013-11-29     22 visitors     [COLOR="#00FF00"]34 visits[/COLOR]
2013-11-28     14 visitors     20 visits

I will also like to add two AVG functions so you have the average number of visitors/visits. This can also be done in PHP, but what do you think?

Thanks for any help :slight_smile: