Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    13

    Unanswered: Link tracking - I cant work this out and neither can my coder

    Hi,

    I am literally going insane, I have been asking coders I know how to do this and all the suggestios seem poor and rely on CRON jobs.

    For each link on my site, I want to record for each day, how many clicks I get. Since I do not need any other information such as IP, browser etc, it seems much better from a performance point of view to have the view count on that days date incremented each time. So, regardless of the number of hits, there is only 1 record per day, not one new record per hit.

    Date | Hits
    20-12-12 | 102
    20-12-13 | 23
    20-12-14 | 35
    20-12-15 | 47

    If today is the 15th and I visit the site, it will increment "47" to "48". So, it seems easy enough. However, the issue is, what happens on the 16th December using the example above. There is no record for that.

    I dont want to be running a Cron job each day to add in the next days date so how can I do it. Is this no function to just increment todays date and create the line if it doesnt already exist.

    I would REALLY appreciate some help on this, ive spent all day on it.

    James

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    could be done either as

    as trigger or stored procedure
    MySQL :: MySQL 5.1 Reference Manual :: 18.2 Using Stored Routines (Procedures and Functions)
    ..however that may depend oj the version of MySQL your website is running. stored procedures came in with version 5.1

    or
    write a function within whatever language which is your website is written in.

    in both cases the psuedo code will be the same
    check if there is a value for today
    if not then value is one and write that to the db
    otherwise add to that value and update the existing row.

    in all honesty I don't see why you'd need to do this as its all in the logfile, and unless you actually need a count of users right up todate, then I'd just scan through the logfile (using a cron job) early the following day.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Would something like this work?
    Code:
    CREATE TABLE your_table (
       link     varchar(256) NOT NULL
     , the_date datetime     NOT NULL
     , hits     int          NOT NULL DEFAULT 1
     , PRIMARY KEY (link, the_date)
    );
    
    INSERT INTO your_table (link, the_date)
      VALUES ('www.dbforums.com', '20120311')
        ON DUPLICATE KEY
          UPDATE hits = hits + 1;
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2011
    Posts
    13
    Thank you both for the suggestions. I will come back to the coder today with these and hopefully we can move forward.

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Have a look at look at MySQL EVENTS which allow you to schedule SQL queries to run inside your database. This is like a CRON scheduler but inside the MySQL database. However, looking at your code Triggers is definitely the best way to proceed. Here is an example of a trigger

    MySQL – Triggers IT Integrated Business Solutions
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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