Link click tracking

Question

bashaash 0 Light Poster

16 Years Ago

Hello everybody i am new with php and i would to help with link click counter i have a database and a table which i store some links and the i have these links
play.php?id=1
play.php?id=2

i wanna to know how many times has been clicked any links for example
play.php?id=1 - hits 150
play.php?id=2 - hits 320

can someone help me to make this thanks?
thanks all php masters

mysql php

3 Contributors
11 Replies
4K Views
1 Day Discussion Span
Latest Post 16 Years Ago Latest Post by bashaash

Dani AI

Generated 1 Year Ago

What you have from rajarajan2017 works, but two things can bite you later: doing a SELECT and then an UPDATE can miss clicks under load, and counting only in JavaScript will miss middle-clicks, keyboard open, or users with JS off. A small redirect script solves both: every click goes through one PHP endpoint that atomically increments the counter and then sends the user to the MP3 or player.

Example go.php using PDO (mysql_* was deprecated and later removed, so prefer PDO/MySQLi):

<?php
$pdo = new PDO(
    'mysql:host=localhost;dbname=DATABASE;charset=utf8mb4',
    'username',
    'pass',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
if (!$id) { http_response_code(400); exit('Bad id'); }

// Atomic increment avoids race conditions
$pdo->prepare('UPDATE mp3s SET hitcounter = hitcounter + 1 WHERE id = ?')->execute([$id]);

// Get destination and redirect
$stmt = $pdo->prepare('SELECT filepath FROM mp3s WHERE id = ?');
$stmt->execute([$id]);
$filepath = $stmt->fetchColumn();
if (!$filepath) { http_response_code(404); exit('Not found'); }

header('Location: ' . $filepath);
exit;

Use it for normal links or popups:

<a href="go.php?id=4" target="_blank" rel="noopener">listen music</a>
<!-- or -->
<a href="go.php?id=4" onclick="window.open(this.href,'linkname','height=260,width=414,resizable=0,scrollbars=0'); return false;">listen music</a>

Tips based on the thread:

As rajarajan2017 suggested, keep a hitcounter column (INT UNSIGNED NOT NULL DEFAULT 0) in mp3s. An index on id is essential.
To display counts, select them directly: SELECT id, title, hitcounter FROM mp3s ORDER BY hitcounter DESC.
If you want to reduce repeated counts from reloads, add a simple throttle: store a row per (mp3_id, ip_hash, yyyymmdd) in a mp3_hits table with a unique key and only increment when an insert succeeds. Otherwise, keep the simple total counter.

All 11 Replies

Member #334542

16 Years Ago

Anyway you have to store your links in the database right? then add one more field hitCounter in your table.

And write the code to just increment the value of the counter by 1 when you click.
Now you have the count that you have last stored.

One more thing you has to do in your link file. while loading the link page I mean before click which one shows the link (understand). Connect the database on the page and check what was the counter and if you click again now increase the counter by 1 and again update the database.

Member #334542

16 Years Ago

Yes intialize default hitcounter to 0 while creating the table. Yes within play.php make a database connection anyway, you already have that to open your song.mp3, While loading your song.mp3 also fetch the field of hitCounter and get the value whatever it be there and increment by 1 stored in a local variable. Now again use the update query.

UPDATE mp3s set hitcounter='$incremented' where id='$id'

where $incremented is the value that was incremented by 1 from your hitcounter field.

Hope you clear!

Member #334542

16 Years Ago

<?php
   

      $connection = mysql_connect('localhost','username','pass');

      if (!$connection)

      {

      die ('error connecting to database server'.mysql_error());

      }

       

      mysql_select_db('DATABASE', $connection);

      
      $result = mysql_query("SELECT * FROM mp3s WHERE id = '{$_GET['id']}'") or die(mysql_error());

      if(mysql_num_rows($result))

      {

      while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

      $mp3s = $row['filepath'];
	  $title = $row['title'];
	  $count = $row['hitCounter'];
      //echo $mp3s;

      }

      }
	  $count = $count + 1;
	  echo $count;
	  
	  $result=mysql_query("Update mp3 set hitCounter='$count' where id='{$_GET['id']}'") or die(mysql_error());
	    if (mysql_num_rows($result > 0))
		{
		  echo "records updated";
		}

      ?>

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

keval_hack -3 Junior Poster in Training Banned · Answer 1 · 2010-07-02T00:21:12+00:00

Now you need AJAX to perform your link counter operation.

I created simple example for u.

First Create Database:link_counter

Now Create Table:link

Execute following query in query window to create table with data.

-- phpMyAdmin SQL Dump
-- version 3.1.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 01, 2010 at 06:17 PM
-- Server version: 5.1.30
-- PHP Version: 5.2.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `link_counter`
--

-- --------------------------------------------------------

--
-- Table structure for table `link`
--

CREATE TABLE IF NOT EXISTS `link` (
  `id` int(11) NOT NULL,
  `Link` varchar(50) NOT NULL,
  `Hits` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `link`
--

INSERT INTO `link` (`id`, `Link`, `Hits`) VALUES
(1, 'www.google.com', 10),
(2, 'www.daniweb.com', 5);

Now create file named index.php and copy following code.

<html>
<head>
<script src="Ajax.js" language="javascript"></script>
</head>
<body>

<?php

//For mysql connection
$con=mysql_connect("localhost","root","");
$db=mysql_select_db("link_counter",$con);

//Query for displaying list of link and hits
$sql="select * from link";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result,MYSQL_NUM))
{

		echo  "<a href=''  name='$row[0]' onClick=\"update(this.name)\">".$row[1]."</a>     ".$row[2]."<br>";
}
?>
</body>
</html>

now create file named and copy below code in it.

// JavaScript Document
//JavaScript And Ajax Script

var xmlhttp;

//this function is call  from counter.php for incrementing counter
function update(str)
{
		alert(str);
		xmlhttp=getxml();
		
		if(xmlhttp==null)
		{
			
				alert("Browser Does Not Support");
				return;
		}
		
		var url="counter.php";
		
		url=url+"?id="+str;
		url=url+"&s="+Math.random();
		
				
		xmlhttp.open("GET",url,true);
		
		xmlhttp.send(null);
		
}


function getxml()
{
	
		if(window.XMLHttpRequest)
		{
				return new XMLHttpRequest();
				
		}
		
		if(window.ActiveXObject)
		{
				return new ActiveXObject("Microsoft.XMLHTTP");
		}
		
		return null;
}

Now create last file counter.php and copy below code in it.

<?php

$id=$_GET['id'];
$con=mysql_connect("localhost","root","");

$db=mysql_select_db("link_counter",$con);


//Query for geting previous counter value
$sql="select * from link where id='$id'";
$result=mysql_query($sql);
$row=mysql_fetch_array($result,MYSQL_NUM);

$hits=$row[2]+1;
 

//Query for Incrementin Hit Counter
$sql="update link  set hits=$hits where id='$id'";
$result=mysql_query($sql) or die(mysql_error());

?>

Now run index.php on localhost and click on any link u see the counter is incremented on each click.

I hope this may help u.

-keval

bashaash 0 Light Poster · Answer 2 · 2010-07-02T03:20:31+00:00

Hi Keval first i thank for your help and your code is not working like the one i want but i am saying you again thanks so i did the database and stored some links how to count the links and visible the counter on my website i have these links

www.mysite.com/path/web.php?id=4
and 
<a href="javascript:void(1)"onclick="window.open('play.php?id=4','linkname','height=260, width=414, resizable=0, scrollbars=0')">listen music</a>

how to counter the javascript pop up link thanks

keval_hack -3 Junior Poster in Training Banned · Answer 3 · 2010-07-02T11:06:16+00:00

First u tell me that
1) each time you want to call same link each time with different id such as

play.php?id=1
play.php?id=5
play.php?id=8

2) or u Want call different link each time.

Based on one of this condition you have to write code.

bashaash 0 Light Poster · Answer 4 · 2010-07-02T17:11:32+00:00

Hi best regards i understand what u say but i have a table can i add hitcounter field to it i.e

mp3s table

======================================================
|      id       |     title    |    artist   |  filepath   |
------------------------------------------------------------
|      1        |  Song Name   |  song artist|  song.mp3   |
------------------------------------------------------------

==========================================================================
|      id       |     title    |    artist   |  filepath   |  hitcounter | 
--------------------------------------------------------------------------
|      1        |  Song Name   |  song artist|  song.mp3   |       124   |
--------------------------------------------------------------------------

can i add counter code to the previous play.php code thanks

bashaash 0 Light Poster · Answer 5 · 2010-07-02T18:08:44+00:00

bashaash 0 Light Poster

16 Years Ago

hi i did this

Edited 16 Years Ago by bashaash because: n/a

bashaash 0 Light Poster · Answer 6 · 2010-07-02T18:20:27+00:00

ok Mrs Raja here is the play.php code where can i put the counter code

<?php
   

      $connection = mysql_connect('localhost','username','pass');

      if (!$connection)

      {

      die ('error connecting to database server'.mysql_error());

      }

       

      mysql_select_db('DATABASE', $connection);

      
      $result = mysql_query("SELECT * FROM mp3s WHERE id = '{$_GET['id']}'") or die(mysql_error());

      if(mysql_num_rows($result))

      {

      while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

      $mp3s = $row['filepath'];
	  $title = $row['title'];

      //echo $mp3s;

      }

      }

      ?>

and the how can i display each link on my site how many hits it thanks again my teacher

bashaash 0 Light Poster · Answer 7 · 2010-07-02T19:19:29+00:00

yes Mrs Raja it's working perfectly but it increaces 1 time if i reload the page or clicks the link another time not added more value it counts one
thanks for your help

bashaash 0 Light Poster · Answer 8 · 2010-07-02T22:39:21+00:00

Thank you very much mr Raja code is working fine thanks for your help.

Link click tracking

Dani AI

Recommended Answers Collapse Answers

All 11 Replies

Recommended Answers