0

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... ?

  • Have you considered not using MySQL to basically store logs, and use something more appropriate, such as elasticsearch, or even pure text files? – jynus Jul 10 '16 at 8:08
1

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.

  • Bah! Can't even upvote. Answer much appreciated though. I am simply looking to show "the count of visitors in the past 24 hours." That way, if some pages/products popularity is boosted during someday due to viral marketing or something, customers will see "This item is selling like hotcakes -- 343 people have viewed it in the past hour!" And yeah, MySQL seems wasteful but I have no idea of how else to accomplish my seemingly simple goal. – Andre Bulatov Jul 13 '16 at 21:29
  • @AndreBulatov - Thanks for the virtual upvote. As the Asker, you can probably 'accept' the answer via the checkmark. – Rick James Jul 13 '16 at 21:54
  • I have not personally implemented any of the three suggestions, but I understand the principles. They do require a breadth of knowledge -- HTML, JSON, AJAX, tricks with <img>, database, PHP (or other client code), Perl (or other parsing tools for logs), etc. If you already had the database built, but were being bogged down by a million clicks per day, that gets more into my expertise. – Rick James Jul 13 '16 at 21:59
  • I got this "a breadth of knowledge -- HTML, JSON, AJAX, tricks with <img>, database, PHP" in spades, but still don't know how to accomplish this in an efficient manner. "My" method (very "manual") is easy to understand and implement but seems insanely bulky, as each of 1500+ products would have hundreds of timestamps to go through every time the products page gets a visit. I've looked through Google Analytics to see if something like this is implemented and the closest seems to be "Hits for some given date" but that's easy compared to 'hits in past 24 hours at any given point in time"... – Andre Bulatov Jul 14 '16 at 23:04
  • 2
    Would it be good enough to have 'hour' resolution? Maybe Google Analytics can be tweaked to give you "for a given hour"? Collect 24 of them? – Rick James Jul 15 '16 at 2:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.