How Can I Count Clicks to See Which Ones are Most Popular?

[Tehuti]

Say I had twenty links on a page. How can I count which ones have been clicked on the most?

I have been told that I have to first direct the visitor to a page which will log the click and add it to the database, before redirecting the visitor to the original destination. But what I don't understand is whether or not I have to create an individual "log and redirect" page for each link. Also, I have no idea as to the type of code that I will need to log the clicks. Can anyone help, please?

[omoutop]

click -> redirect to log page -> redirect to final destination
this tactic is bad for SEO

better to use $_SERVER['HTTP_REFERER'] on the target page for logging your clicks

PS: parse_url() may come handy

[cameraman]

Make a db table, say linklog, with fields:
id url count
If they're links to pages on your own site you can just add lines to each script to increment the counter:
$self = mysql_real_escape_string($_SERVER['PHP_SELF']);
"update linklog set count=count+1 where url=$self"

If they're links to external pages, make the links look something like
<a href="mysite.com/redirect.php?id=1">shopping site</a>
<a href="mysite.com/redirect.php?id=2">dictionary</a>

then in redirect.php
$id = intval($_GET['id']);
"update linklog set count=count+1 where id=$id";
$href = "select url from linklog where id=$id";
header("Location:$href\n");
exit;

[Tehuti]

Thanks for the help, guys!

Cameraman, excellent explanation! However, that's not the exact script that I should use, is it? Shouldn't it be something more like this (try not to laugh!):

<?php

$con = mysql_connect("localhost","root");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("ctyi", $con);

$id = intval($_GET['id']);

mysql_query("update linklog set count=count+1 where id=$id");

$href = "select url from linklog where id=$id";

mysql_query($href);

header("Location:$href\n");

mysql_close($con);

?>

Will that work?

[cameraman]

That was pseudo-code.
You've almost got it - the first part is fine but you need to fetch the href value from the table:
$href = "select url from linklog where id=$id";

$qry = mysql_query($href);
list($href)=mysql_fetch_row($qry); // list() because an array is returned

header("Location:$href\n");

[Tehuti]

Great stuff, Cameraman! I've learnt loads from you in the past two days. Thanks a lot, Sir!

One thing though . . . It didn't work properly. The redirect happened, but when I checked the database, I saw that no data had been added to the Count column. It just said "NULL". Here's exactly what I did:

MySQL:

create table linklog (
id smallint unsigned not null auto_increment primary key,
url varchar(200),
count smallint unsigned
);

insert into linklog (url) values ("http://www.amazon.co.uk");

Link Page:

<a href="redirect.php?id=1">Amazon</a>

redirect.php

<?php

$con = mysql_connect("localhost","root");

if (!$con)

{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("ctyi", $con);

$id = intval($_GET['id']);

mysql_query("update linklog set count=count+1 where id=$id");

$href = "select url from linklog where id=$id";

$qry = mysql_query($href);

list($href)=mysql_fetch_row($qry); // list() because an array is returned

header("Location:$href\n");

mysql_close($con);

?>

MySQL Result

select id, url, count from linklog;

id: 1
url: http:www.amazon.co.uk
count: NULL

[cameraman]

One time, easiest with phpMyAdmin, start them all off at zero. You might want to set a default value of zero for that column, too.
The problem most likely is that NULL + 1 = NULL

[Tehuti]

Yes!

Cameraman, it worked! Just as you said, all I had to do was assign a default value of '0' to the Count column, and start it off with an insert of '0'.

I appreciate all the help, man. Thanks!