50 users online (0 members and 50 guests)  


  Results 1 to 2 of 2

Related

  1. Replies: 1
  2. Mod with mysql    Forum: Website Scripts Forum
    Replies: 0
  3. Mysql help please    Forum: PHP Forum
    Replies: 1
  1. #1
    badboymav's Avatar
    New User

    Status
    Offline
    Join Date
    Jan 2007
    Posts
    1

    Need Pro MYSQL HELP

    Hi, I'm hoping a mysql pro can help me out here

    I've taken over developing a statistics page for a client.
    The statistics are recorded in a table named 'visitors'.
    This table is about 35,000+ records at the moment.
    I need to be able to display total site hits unique hits
    and new visitor hits for periods such as today, this week, month and year.

    The problem is getting new visitor hits, which would be remote_addr which have not been entered into the database yet. perhaps i need to create a new field called new, and when a unique remote_addr is added for the first time, that record gets a yes in the new field?

    It creates a new record on every page thats visited on the website.
    The table has 7 fields:
    -----------------------------
    ID
    REMOTE_ADDR - logs visitors ip address
    HTTP_USER_AGENT - logs browser
    HTTP_REFERER - logs referal if exists
    SESSION_ID - logs visitors session id
    PAGE - logs page visited
    DATE_ADDED - date record created - timestamp
    -----------------------------

    Currently the ID fields are used to test if the current remote_addr has a previous record. This is very resource intensive to test each 35,000 rows against each other. Can anyone suggest a better system or a solution?

    Thanks in advanced!

    I've added an example of the code the previous developer was working on.
    The sql below is meant to split up the day by hour showing total hits and new visitors for a specific day.
    I've tested the SQL and it times out on the new visitor 'if function'.


    SELECT
    CONCAT(
    DATE_FORMAT(date_added, '%k'),
    ':00 - ',
    DATE_FORMAT(date_added, '%k'),
    ':59'
    ) as `day`,

    DATE_FORMAT(date_added, '%W %e %M %Y') AS act_day,

    COUNT( DISTINCT REMOTE_ADDR ) AS un_total,
    COUNT( DISTINCT session_id) AS site_hits,

    COUNT(IF(
    v1.REMOTE_ADDR IN (
    SELECT v2.REMOTE_ADDR
    FROM visitors AS v2
    WHERE v2.id < v1.id
    GROUP BY REMOTE_ADDR
    ),
    NULL,
    1
    ) ) AS un_new,

    FROM visitors AS v1
    WHERE DATE(date_added) = DATE('".$_GET['day']."')
    AND page != 'REFERAL'
    GROUP BY HOUR(date_added)
    ORDER BY date_added DESC;

  2. #2
    DeadMeatGF's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    381

    Re: Need Pro MYSQL HELP

    Distinct



Tags for this Thread