Mysql – How to organize MySQL DB to allow an arbitrary counter for clicks, visits, etc in past 24 hours

MySQL

If you're familiar with it, I used to use count.io which has been permanently taken down. I basically want to create my own version for my own use.

Main question

How do I structure/organize my MySQL DB to allow to not only count page visits, but to also be able to tell how many of them are from the past 24 hours?

What I am trying to acheive

  1. The way it'd work is, every time a user hits some page X, a "hit" is recorded.

  2. The count.io API allowed me to record a hit with JavaScript, like this: 

    // Timestamp
var timestamp = Math.floor(Date.now());

// Grab product ID
var productNumber = $('#productDetails input[name=product_id]').attr('value');

// Record a hit to the DB under Product[ID]-group, as a timestamp
$.ajax({
    url:'http://count.io/vb/product' + productNumber + '/' + timestamp + '+',
    type: 'POST'
});

…I am not sure how the count.io DB was structured to allow this, but essentially, the count.io DB would receive/store my hits like this: 

    - http://count.io/vb/product37/1468038731  
    - http://count.io/vb/product37/1468048798  
    - http://count.io/vb/product37/1468058931  
    - http://count.io/vb/product37/1468069731  
    ... potentially hundreds or thousands of such entries for any 1 product

  1. When I'd retrieve it with JavaScript, I was then able to count how many timestamps there were within the week by doing something like this:

    // Retrieve current product array
$.post(
    // 24 hour old timestamp
    var dayAgo = timestamp - 86400000;
    httpPrefix + 'http://count.io/vb/product' + productNumber,
    function (data) {
        var timedViewCounter = 0;
        var myStringArray = data.counts;
        var arrayLength = myStringArray.length;
        // Check each "timestamped hit" to see if it's older than 24 hours
        for (var i = 0; i < arrayLength; i++) {
            var itemTimestamp = data.counts[i].item;
            // Count up all visits that are 24 hours old or newer
            if (itemTimestamp >= dayAgo) {
                timedViewCounter++;
            }
        }
        // Show total count in past 24 hours
        var hotItem = '<span class="view-count">' + timedViewCounter + '</span> people have viewed this product in the last 24 hours</span></div>';
});

Question

How would I organize a MySQL DB that would allow this kind of functionality? Would I create a new table for each page/product? (that sounds crazy as there are over 1,500 relevant items)

OR

Would I just record the "timestamp hits" into the same field, and simply separate the values with a comma?

OR

Is there some magical functionality of MySQL that I am not aware of?

What count.io did

Incrementing 

http://count.io/vb/fruit/apples+

Notice the + sign on the end of the URL.

Returns this JSON:

{
   "item":"apples",
   "count":1,
   "group":"fruit"
}

Use the "group" field to tie similar themed counts together. In this
case it's "fruit."

If you call the URL again, you'll notice the count is incremented by
1.

{
   "item":"apples",
   "count":2,
   "group":"fruit"
}

Get Count 

http://count.io/vb/fruit/apples/

Get Group Sum 

http://count.io/vb/fruit/

Returns this JSON::

{
   "group":"fruit",
   "sum":7,
   "counts":[
      {
         "item":"apples",
         "count":2
      },
      {
         "item":"oranges",
         "count":1
      },
      {
         "item":"bananas",
         "count":4
      }
   ]
}

The way I used it, is instead of "fruits", I'd have the group as the PorudctID and the items inside it were al unique timestamps with a count of 1. I'd then run through all the timestamp items and see which are from within past 24 hours and that way, Id have the count of visitors in past 24 hours.

I want to recreate this functionality on my own server but have no idea how to structure the queries. I'm leaning towards putting all timestamps into the same field but separated by commas… ?

Best Answer

3 suggestions...

Don't store all of "http://count.io/vb/product37/1468038731", it wastes space and time. Trim it down to just the timestamp.

Don't store the time, bump a counter for the day (or hour, if you prefer).

Don't store anything, parse the web server logs later.

Now let me approach it a different way... What is the minimal information you want? Exact times of all hits? Running counter of hits? Daily counts? Keep in mind what information can be derived from what other info. Hourly counts --> daily counts. Running counter --> daily counts (if you grab the count every midnight). Etc.

Related Question