Click counter php mysql?

Question

Hey, I have been looking for a simple way to track clicks on a link and store how many times the link has been clicked in a mysql database. Anyone have a solid approach on how to do this?

Thanks.

Answer 1

Yeah. You have the link go to a redirector script on your site, which inserts a record into a tracking table and redirects the user to the final link location.

Answer 2

One of the biggest things you'll need to consider is scale - depending upon how many users you have, you can do something simple like:

Initial page creation (admin)

INSERT INTO visit_track_table (id, count, url) VALUES (1, 0, 'page.php');

Page update (user)

UPDATE visit_track_table SET count=count+1 WHERE url='page.php';

The problem here is that this row has to be locked for every single visitor to the page.

A better approach is to define a scalable segmentation that is recalculated periodically to give an overall total. The easiest way to do this is to provide a random suffix so that fewer concurrent users lock the same database row.

Scalable

UPDATE visit_track_table SET count=count+1 WHERE url='page.php-N'

In this query the -N suffix would be generated by a tuned parameter that would range from '-0' to '-100' if you wanted to allocate 100 rows per page. The optimal value of N would depend upon your user base.

Tallying (done periodically by a cron job perhaps)

SELECT SUM(count) AS M FROM visit_track_table WHERE url LIKE 'page.php-%'

UPDATE visit_track_table SET count=M WHERE url='page.php'

EDIT: Yes you need to have an intermediate page which records the URL that you are heading to.

Answer 3

You can use ajax or a bounce page:

Bounce: The link forwards to a counter script like, http://www.your-domain.com?click_count.php?forward=http://another-domain.com/destination.html

using sql like INSERT INTO clicks ('http://another-domain.com/destination.html',1) ON DUPLICATE KEY UPDATE clicks=clicks+1

Ajax: you could add an onclick javascript event to send an ajax call to, maybe the same click counter script and then return true to follow the link.

Ajax method requires javascript obviously which might not be robust enough for you in which case a bounce script will do fine and it'll be unnoticeable really