Counter field in database table

Report · Dec 14, 2010

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.

Report · Dec 14, 2010

database has a table 'resorts' with columns for resort and hits. On resort list page have links to resorts go to your_website.com/hit_counter.php?resort=resort_name

on hit_hounter.php enter a query to update the databse with +1 where the URL parameter = the resort in the resort column of the database.


$the_resort = "-1";
if (isset($_GET['resort'])) {
$the_resort = $_GET['resort'];
$query_hits = sprintf("UPDATE resorts
SET hits = hits+1
WHERE resort = %s",
GetSQLValueString($the_resort, "text"));}

Then redirect the page to the resort name in the header location. Use the URL parameter of resort_name to redirect to the resort_name.com URL. You can setup a conditional array where if resort_name = this then go to this.com or if resort_name = that go to that.com etc.

Report · Dec 14, 2010

Duplicate post

Report · Dec 14, 2010

Thanks for that.

I've got a table - lodges, in the end - with fields LodgeID, Lodge and Hits.

And a page with a couple of links here :

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

(Am using LodgeID rather than Lodge as the identifier after the ? though).

And a hit_counter.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_hits = sprintf("UPDATE lodges

SET Hits = Hits+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">

<head>

<title>Untitled Document</title>

</head>

<body>

</body>

</html>

<?php

mysql_free_result($rsLodges);

?>

I changed the Hits update part to reflect the different fields, but its not doing anything yet.

Is this along the right lines though?

Report · Dec 14, 2010

You need to actually execute your query. Also, there's no header location redirect to redirect the visitor to the resort page.

$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($the_resort, $databaseConnection) or die(mysql_error());

There's no need for the query to SELECT * FROM lodges in hit_counter.php and you can remove all the html junk too. Try 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($the_resort, $databaseConnection) or die(mysql_error());
header("Location: http://google.com");
?>

Report · Dec 14, 2010

Thanks for your help with this - I've never quite gotten my head around PHP other than the basics.

That code is missing a } I think.... I counted five open brackets, but only four close brackets.

I tried popping one in in a couple of likely looking places, but was getting the error :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

Report · Dec 14, 2010

<?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($the_resort, $databaseConnection) or die(mysql_error());
}
header("Location: http://google.com");
?>

Report · Dec 14, 2010

Still getting that same error when I click on the links....

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

Report · Dec 14, 2010

EDIT: I see the problem. The query is executing the wrong variable. Try this:

$rshit_counter = mysql_query($query_hits, $databaseConnection) or die(mysql_error());

Report · Dec 14, 2010

Thank you!

That's the redirect and the hit field incrementing as it should.

So the last part of the thing is to get it to redirect to the correct URL. Would the best way to have a field LodgeURL in the table, and then replace

header("Location: http://google.com");

with

header("Location: LodgeURL");

(Although I'm guessing its not quite that straightforward, and the LodgeURL needs to be established in some code prior to that?

Report · Dec 14, 2010

Actually, something more like :

header("Location: http://www.goodsafariguide.com/hit_counter.php?LodgeID=<currentLodgeID>");

?

Report · Dec 14, 2010

The easiest (and dirtiest) method is to complie a string of if statements where if URL parameter = this go to this.com and if URL parameter = that go to that.com

example:

if ($_GET['LodgeID'] = "1") {
header ("Location: http://something.com");
}
if ($_GET['LodgeID'] = "2") {
header ("Location: http://something_else.com");
}
etc. etc.

Like I said: this is the dirtiest method, but functional none-the-less.

Report · Dec 14, 2010

OK - downside of that is that that would presumably need to be manually edited every time a lodge was added to the database?

Ideally this would all be taken care of at the point at which a new lodge was added to the database.

Report · Dec 14, 2010

Tried it with the code above, but both links are going to the same site with LodgeID=2....

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

Report · Dec 14, 2010

Iain71 wrote:
Tried it with the code above, but both links are going to the same site with LodgeID=2....
http://www.goodsafariguide.com/countertest.htm

Replace the seires of if statements with the original header code.

Replace this:

header("Location: http://google.com");

With this:

if ($_GET['LodgeID'] == "1") {
header ("Location: http://something.com");
}
if ($_GET['LodgeID'] == "2") {
header ("Location: http://something_else.com");
}

Without seeing your code it's impossible to see what the problem is. Just show me the end of the code after the query where the header is.

Report · Dec 14, 2010

Think that's what I did - the last part looks like this :

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());

}

if ($_GET['LodgeID'] = "1") {

header ("Location: http://www.elsaskopje.com");

}

if ($_GET['LodgeID'] = "2") {

header ("Location: http://www.behobeho.com");

}

?>

Report · Dec 14, 2010

Just as another note, I added a third record to see what that did, and it looks like each link is going to the most recently added record :

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

Report · Dec 15, 2010

OK - I think the issue there was it needed to be == rather than = - so that's working.

So now I'm trying to just pull the LodgeURL from the the table using the query :

mysql_select_db($database_databaseConnection, $databaseConnection);

$query_rsLodgeURL = "SELECT LodgeURL 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);

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?

Report · Dec 15, 2010

Change this:

mysql_select_db($database_databaseConnection, $databaseConnection);
$query_rsLodgeURL = "SELECT LodgeURL 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);

To this:

mysql_select_db($database_databaseConnection, $databaseConnection);
$query_rsLodgeURL = sprintf("SELECT LodgeURL FROM lodges WHERE LodgeID = %s",
GetSQLValueString($the_resort, "int"));
$rsLodgeURL = mysql_query($query_rsLodgeURL, $connSafari) or die(mysql_error());
$row_rsLodgeURL = mysql_fetch_assoc($rsLodgeURL);

echo $reLodgeURL['LodgeURL']; should output the entire lodge URL like http://www.singita.com or http://www.behobeho.com etc. depending on the URL parameter for LodgeID = 2 or 7 or whatever.

Then change this:

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");
}

To this:

if ($row_rsLodgeURL['LodgeURL'] != "") {
header("Location: $row_rsLodgeURL['LodgeURL']");
} else {
header ("Location: http://www.google.com");
}

Cheers!

Report · Dec 16, 2010

Thank you - that's all working great now.

Counter field in database table

1 Correct answer