[RESOLVED] Counter field

turtleton09

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.

dagon

count.php would count the hits then forward to the url

turtleton09

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"));
$r****_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.....

dagon

why not use the db for getting the url as well as storing the count.
Your error is due to using = not ==

turtleton09

What would the syntax need to be to get the URL from the database?

Would I need to store the URL in the database, or could it just look it up using the method above?

dagon

select url from abc where id=$the_resort

turtleton09

Is that where URL is the field storing the actual URL, and abc is the table name?

Too late to play around anymore tonight, but I'm still not sure how that will know which URL to link to if there are several records / links on the page?

dagon

duklaprague;10970485 wrote:
Is that where URL is the field storing the actual URL, and abc is the table name?

yes.

duklaprague;10970485 wrote:
Too late to play around anymore tonight, but I'm still not sure how that will know which URL to link to if there are several records / links on the page?

where id= will limit it to the correct record.

turtleton09

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, $databaseConnection) 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?

JVassie

duklaprague;10970497 wrote:
Should that be LodgeID, or id? (LodgeID is the unique ID in the table)

You should always use the name of the field, so in this case LodgeID.

When using mysql_fetch_assoc, I normally tend to select everything from the row, in this case it would mean changing it to:

$query_rsLodgeURL = "SELECT * FROM lodges WHERE LodgeID = '$the_resort'";

That way should you need to pickup anything further from the table, you dont need a 2nd query, you can just reference each field:

$row_rsLodgeURL['fieldname']

Edit: Forgot the 2nd question

duklaprague;10970497 wrote:

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?

$url = $row_rsLodgeURL['LodgeURL'];
header("Location: '$url'");

Should work fine, however if you plan, or currently uses PHP sessions, you can't modify the headers as they'll already have been sent, I tend to use a meta refresh redirect.

<meta http-equiv="REFRESH" content="0; url=<?php echo $url; ?>">

Hope that helps a bit
James

turtleton09

Thanks James.

I did just have to move the query down to after $the_resort had been declared.

And

header("Location: '$url'");

didn't need the single quotes, so needed to be :

header("Location: $url");

But thank you very much guys for helping out with this - now I just need to recreate it using a little loop to display the Lodges as they are added to the table.

turtleton09

OK - 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.....?

JVassie

duklaprague;10970502 wrote:
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.....?

Edit: (I promise I'll wake up and start reading properly soon!)

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

Need to escape the quotation marks within the HTML otheriwse it'll throw a wobblie.

turtleton09

Thank you! That all works great now. 🙂

JVassie

duklaprague;10970505 wrote:
Thank you! That all works great now. 🙂

Glad to hear it 🙂