SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Pageview and visitor counter stats SQL

    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 Still, I really can't seem to solve this puzzle.

    Thanks for the help in advance

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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. #3
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

    Code:
    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

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    More info:

    If I would change
    Code:
    SELECT * FROM
    to
    Code:
    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;

    Code:
    2013-12-01     12 visitors     24 visits
    2013-11-30     11 visitors     20 visits
    2013-11-29     22 visitors     34 visits
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •