Counter field

bonsai99 · December 14, 2010, 4:19pm

Am just thinking about how to solve a little problem which is new to me in PHP, but I’m hoping not to complex.

Basically, I have a database of holiday resorts, and my client would like to keep track of how many people click through to the resorts own website from our website.

So I was thinking if I have a field ClickCounter, initially set to 0, would there be some way to have a link that, once clicked on, would increment the ClickCounter field by 1.

If anyone could help me get started with this, that would be much appreciated.

Thanks.

DarthGuido · December 14, 2010, 4:23pm

http://www.google.com/search?hl=en&q=php+click+counter

Then, if you have troubles understanding or implementing the solutions you find googling, please ask help here.

lorenw · December 14, 2010, 4:28pm

I would do it like facebook or google.

your links would look something like,
yoursite.com/clicks.php?id=1

Then in clicks.php check if id == 1 increment counter and then do a header location to the resort assigned to id 1.

HTH

bonsai99 · December 14, 2010, 5:17pm

OK, so using that method I’d have a table of Resorts, with fields including :

ResortID
Resort
ResortURL
ClickCounter

And then, instead of having a link to :

www.resort1.com

Have a link to :

www.mydomain.com/clicks.php?id=1

?

bonsai99 · December 14, 2010, 6:35pm

OK - i’ve changed it to a table called lodges, with fields LodgeID, Lodge and ClickCounter

Have a page with a couple of links :


<a href="http://www.goodsafariguide.com/clicks.php?LodgeID=1">Elsa's Kopje</a>
<a href="http://www.goodsafariguide.com/clicks.php?LodgeID=2">Beho Beho</a>

And a clicks.php page which looks like this :


<?php require_once('Connections/databaseConnection.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_databaseConnection, $databaseConnection);
$query_rsLodges = "SELECT * FROM lodges";
$rsLodges = mysql_query($query_rsLodges, $databaseConnection) or die(mysql_error());
$row_rsLodges = mysql_fetch_assoc($rsLodges);
$totalRows_rsLodges = mysql_num_rows($rsLodges);

$the_resort = "-1";
if (isset($_GET['LodgeID'])) {
$the_resort = $_GET['LodgeID'];
$query_ClickCounter = sprintf("UPDATE lodges
SET ClickCounter = ClickCounter+1
WHERE LodgeID = %s",
GetSQLValueString($the_resort, "text"));}
?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>
</body>
</html>
<?php
mysql_free_result($rsLodges);
?>

Not doing anything yet - but is this along the right sort of lines?

DarthGuido · December 14, 2010, 6:39pm

hit_counter.php
clicks.php

You’ll have to decide how to call that page

bonsai99 · December 14, 2010, 6:49pm

I’ll go for clicks.php…

bonsai99 · December 14, 2010, 10:44pm

OK - I’ve made some progress…

I have a page here with some links…

http://www.goodsafariguide.com/countertest.htm

The links are of the form :

<p><a href=“http://www.goodsafariguide.com/hit_counter.php?LodgeID=1”>Elsa’s Kopje</a></p>

etc

And the hit_counter.php page looks like this :


<?php require_once('Connections/databaseConnection.php');
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }
  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break; 
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
mysql_select_db($database_databaseConnection, $databaseConnection);
$the_resort = "-1";
if (isset($_GET['LodgeID'])) {
$the_resort = $_GET['LodgeID'];
$query_hits = sprintf("UPDATE lodges
SET Hits = Hits+1
WHERE LodgeID = %s",
GetSQLValueString($the_resort, "text"));
$rshit_counter = mysql_query($query_hits, $databaseConnection) or die(mysql_error());
}
if ($_GET['LodgeID'] = "1") {
header ("Location: http://www.elsaskopje.com");
}
if ($_GET['LodgeID'] = "2") {
header ("Location: http://www.behobeho.com");
}
if ($_GET['LodgeID'] = "7") {
header ("Location: http://www.singita.com");
}
?>

The Hits field is incrementing as it should, so that’s fine.

However, each link is going to the most recently added record.

Although it would be good if there was a way to make each link go to the correct site without having to manually add the if… part for each link…

LSC_Rob · December 15, 2010, 2:27am

If you’ve already got the URL in the database, why not just query it?

DarthGuido · December 15, 2010, 7:13am

if ($_GET['LodgeID'] [B][COLOR="Red"]==[/COLOR][/B] "1")

Rob is right, get the url from the database, don’t hard code them.

bonsai99 · December 15, 2010, 8:56am

OK - I added a query :


mysql_select_db($database_databaseConnection, $databaseConnection);
$query_rsLodgeURL = "SELECT LodgeURL FROM lodges WHERE LodgeID = '$the_resort'";
$rsLodgeURL = mysql_query($query_rsLodgeURL, $databaseConnectioni) or die(mysql_error());
$row_rsLodgeURL = mysql_fetch_assoc($rsLodgeURL);
$totalRows_rsLodgeURL = mysql_num_rows($rsLodgeURL);

Should that be LodgeID, or id? (LodgeID is the unique ID in the table)

But I’m not sure what the syntax needs to be for the actual link in the header part at the bottom…

So the lines that were :


if ($_GET['LodgeID'] == "1") {
header ("Location: http://www.elsaskopje.com");
}
if ($_GET['LodgeID'] == "2") {
header ("Location: http://www.behobeho.com");
}
if ($_GET['LodgeID'] == "7") {
header ("Location: http://www.singita.com");
}

What should that be changed to?

DarthGuido · December 15, 2010, 9:26am


mysql_select_db($database_databaseConnection, $databaseConnection);
$the_resort = "-1";
if (isset($_GET['LodgeID'])) {
  $the_resort = $_GET['LodgeID'];

  // update the hit counter
  $query_hits = sprintf("
    UPDATE lodges
    SET Hits = Hits+1
    WHERE LodgeID = %s",
    GetSQLValueString($the_resort, "text")
  );
  $rshit_counter = mysql_query($query_hits, $databaseConnection) or die(mysql_error());

  // get the url
  $query_rsLodgeURL = "
    SELECT LodgeURL 
    FROM lodges 
    WHERE LodgeID = '" . GetSQLValueString($the_resort) . "'
  ";
  $rsLodgeURL = mysql_query($query_rsLodgeURL, $databaseConnectioni) or die(mysql_error());

  // if the url exists, get it and redirect to it
  if (mysql_num_rows($rsLodgeURL) > 0) {
    $row_rsLodgeURL = mysql_fetch_assoc($rsLodgeURL);
    header ("Location: " . $row_rsLodgeURL['LodgeURL']);
    exit();
  } 
}
// if for some reason you didn't redirect, do something else
    ....

bonsai99 · December 15, 2010, 10:03am

Thanks for that - I had managed to get it working using :


mysql_select_db($database_connSafari, $connSafari);
$the_resort = "-1";
if (isset($_GET['LodgeID'])) {
$the_resort = $_GET['LodgeID'];
$query_hits = sprintf("UPDATE lodges
SET Hits = Hits+1
WHERE LodgeID = %s",
GetSQLValueString($the_resort, "text"));
$rshit_counter = mysql_query($query_hits, $connSafari) or die(mysql_error());
}
mysql_select_db($database_connSafari, $connSafari);
$query_rsLodgeURL = "SELECT * FROM lodges WHERE LodgeID = '$the_resort'";
$rsLodgeURL = mysql_query($query_rsLodgeURL, $connSafari) or die(mysql_error());
$row_rsLodgeURL = mysql_fetch_assoc($rsLodgeURL);
$totalRows_rsLodgeURL = mysql_num_rows($rsLodgeURL);
$url = $row_rsLodgeURL['LodgeURL']; 
header("Location: $url");

So the very last thing I’m trying to do is apply those links to my little array that groups Lodges by Alphabet…

I have got my little array nearly working here :

http://www.goodsafariguide.com/awardssite/nominees/

Which looks like :


<table cellpadding="0" cellspacing="0" width="100%" border="0">
       <?php 
     $groups = array(); 
while ($row = mysql_fetch_assoc($rs2011nominees)) { 
    $groups[$row['Alphabet']][] = $row; 
} 
foreach ($groups as $alphabet_letter => $rows) { 
    echo "<tr><td><br><h2>$alphabet_letter</h2><br></td></tr>"; 
    foreach ($rows as $row) { 
        echo "<tr><td>".$row['Lodge']."</td></tr>"; 
    } 
} 
?>
</table>

So now I’m just unsure of the syntax to put in the link from the static page :

<a href="http://www.goodsafariguide.com/hit_counter2.php?LodgeID=1">Elsa's Kopje</a>

Something along the lines of :

echo "<tr><td><a href="http://www.goodsafariguide.com/hit_counter2.php?LodgeID=.$row['LodgeID'].">".$row['Lodge']."</a></td></tr>";

Well, sort of…?

bonsai99 · December 15, 2010, 10:28am

OK, should have been :


echo "<tr><td><a href=\\"http://www.goodsafariguide.com/hit_counter2.php?LodgeID=" . $row['LodgeID'] . "\\">" . $row['Lodge'] . "</a></td></tr>";

Thanks again to everyone who helped out with this - its always very much appreciated.