[RESOLVED] trying to increment 'hits' field in mysql via click

apollyonus

Heya,
I'm working a links page that will pulls all my info from a table ('id', 'url', 'descrip', 'head', 'hits') My query pulls the data fine, and I can update / add new links as well.

What I would like to be able to do is track clicks for each link, and update the 'hits' field in the table.

here's my code:

<?php
	$dbhost = "localhost";
	$dbname = "test";
	$dbuser = "mike";
	$dbpass = "*********";	

	mysql_connect("$dbhost", "$dbuser", "$dbpass") or die(mysql_error());
	mysql_select_db("test") or die(mysql_error());


//	This is the general links table
$result = mysql_query("SELECT * FROM links WHERE head like 'general'") or die(mysql_error());
$row_count = mysql_num_rows($result) or die(mysql_error());

echo "<table class=\"footcollapse\">\n";
echo "<thead>\n<tr>\n<th colspan=\"3\" align=\"center\">" . General . "</th>\n</tr>\n</thead>\n";
echo "<tfoot>\n<tr>\n<td colspan=\"3\">expand / collapse</td>\n<tr>\n</tfoot>\n";
echo "<tr>\n <th>URL</th><th>Description</th><th>Hits</th>\n</tr>\n";

	//find out how many rows were returned	
	while($row = mysql_fetch_array($result)) {

if($row_count % 2) { //if the number of this of this row (not the id form the db) is even...
	$rowc = "#001155"; //...than make this the tr's bgcolor
} else { //If it's odd...
	$rowc = "#225588"; //...make tr use this bgcolor
}
		echo "<tr bgcolor=\"$rowc\">\n<td>"; //new table row, with alternating bgcolors
		echo "<a href=\"http://" . $row['url'] . "\"target=\"0\">" . $row['url'] . "</a>"; //grab the url twice: once for the href, and once for the clickey
		echo "</td><td>";
		echo $row['descrip']; //populate the description for the url
		echo "</td><td>";
		echo $row['hits']; //this is the field that I would like to increment via some kinda click tracking
		echo "</td>\n</tr>\n";
	$row_count++; //this is the way we count the rows, count the rows, count the rows...
	}

echo "</table>\n";
echo "<br />";
?>

I've searched through many forums & snippets, and haven't been able to wrap my brain around this one yet...

thanks in advance

Roger_Ramjet

<?php
	$dbhost = "localhost";
	$dbname = "test";
	$dbuser = "mike";
	$dbpass = "*********";	

	mysql_connect("$dbhost", "$dbuser", "$dbpass") or die(mysql_error());
	mysql_select_db("test") or die(mysql_error());


//	This is the general links table
$result = mysql_query("SELECT * FROM links WHERE head like 'general'") or die(mysql_error());
$row_count = mysql_num_rows($result) or die(mysql_error());

echo "<table class=\"footcollapse\">\n";
echo "<thead>\n<tr>\n<th colspan=\"3\" align=\"center\">" . General . "</th>\n</tr>\n</thead>\n";
echo "<tfoot>\n<tr>\n<td colspan=\"3\">expand / collapse</td>\n<tr>\n</tfoot>\n";
echo "<tr>\n <th>URL</th><th>Description</th><th>Hits</th>\n</tr>\n";

	//find out how many rows were returned	
	while($row = mysql_fetch_array($result)) {

if($row_count % 2) { //if the number of this of this row (not the id form the db) is even...
	$rowc = "#001155"; //...than make this the tr's bgcolor
} else { //If it's odd...
	$rowc = "#225588"; //...make tr use this bgcolor
}
		echo "<tr bgcolor=\"$rowc\">\n<td>"; //new table row, with alternating bgcolors
		echo "<a href=\"http://" . $row['url'] . "\"target=\"0\">" . $row['url'] . "</a>"; //grab the url twice: once for the href, and once for the clickey
		echo "</td><td>";
		echo $row['descrip']; //populate the description for the url
		echo "</td><td>";
		echo $row['hits']; //this is the field that I would like to increment via some kinda click tracking
		echo "</td>\n</tr>\n";
	$row_count++; //this is the way we count the rows, count the rows, count the rows...
	}

echo "</table>\n";
echo "<br />";
?>

Roger_Ramjet

If you want to count the clicks then you are going to have to have the click route via a script so that you can count and redirect to the real url

links page

<?php
	$dbhost = 'localhost';
	$dbname = 'test';
	$dbuser = 'mike';
	$dbpass = '*********';	

	mysql_connect('$dbhost', '$dbuser', '$dbpass') or die(mysql_error());
	mysql_select_db('test') or die(mysql_error());


//	This is the general links table
$result = mysql_query("SELECT * FROM links WHERE head like 'general'") or die(mysql_error());
$row_count = mysql_num_rows($result) or die(mysql_error());

echo '<table class="footcollapse">';
echo '<thead><tr><th colspan="3" align="center">' . General . '</th></tr></thead>';
echo '<tfoot><tr><td colspan="3">expand / collapse</td><tr></tfoot>';
echo '<tr> <th>URL</th><th>Description</th><th>Hits</th></tr>';

	//find out how many rows were returned	
	while($row = mysql_fetch_array($result)) {

if($row_count % 2) { //if the number of this of this row (not the id form the db) is even...
	$rowc = '#001155'; //...than make this the tr's bgcolor
} else { //If it's odd...
	$rowc = '#225588'; //...make tr use this bgcolor
}
		echo '<tr bgcolor="$rowc"><td>'; //new table row, with alternating bgcolors
		$go = 'redir.php?go=http://' . $row['url'];
                    echo '<a href="' . rawurlencode($go) . '"target="0">' . $row['url'] . '</a>'; //grab the url twice: once for the href, and once for the clickey
		echo '</td><td>';
		echo $row['descrip']; //populate the description for the url
		echo '</td><td>';
		echo $row['hits']; //this is the field that I would like to increment via some kinda click tracking
		echo '</td></tr>';
	$row_count++; //this is the way we count the rows, count the rows, count the rows...
	}

echo '</table>';
echo '<br />';
?>

Now we have the url pionting to a script called redir.php with the target url as a get var

redir.php

<?php
if (isset($_GET['go'])) {
  // redirect browser to clicked url
  $go = rawurldecode($_GET['go']);
  echo 'header("Location: ' . $go . '")';
  // meanwhile count the click
  $sql = "UPDATE links SET hits=hits+1 WHERE url='" . $go . "'";
  $result = mysql_query($sql);
} else {
   echo 'header("Location: http://www.yourdomain.com/links.php")';
}
?>

apollyonus

it seems to be expecting the domain to be on the local server:

The requested URL /redir.php?go=http://www.my-outside-link.net was not found on this server.

Also, Thank you very much for your reply 🙂

Roger_Ramjet

Aw shucks, I was hoping the urlencode would cover that. OK, I forget the syntax for passing a url in the url, not something I'm inclined to do myself. Best ask someone else for that part; probably best to start a new thread for just that question.

bradgrafelman

Roger Ramjet wrote:
Aw shucks, I was hoping the urlencode would cover that. OK, I forget the syntax for passing a url in the url, not something I'm inclined to do myself. Best ask someone else for that part; probably best to start a new thread for just that question.

Teehee, you must be tired. You didn't ENcode the URL... you tried to DEcode it... 😛

Change this:

  $go = rawurldecode($_GET['go']);

to this:

  $go = rawurlencode($_GET['go']);

apollyonus

nope, still hosed. The error message is:
The requested URL /redir.php?go=http://www.outside.net was not found on this server.

The url in the location bar is:

http://localhost:85/redir.php%3Fgo%3Dhttp%3A%2F%2Fwww.outside.net

Thanks for trying guys, it's appreciated

bradgrafelman

Erm, I'm confused... repost the entire contents of the script again, and show us an example query string (i.e. what address did you use to call the script in your browser).

apollyonus

Sorry about that. I've actually got the redir.php working, but my hits aren't incrementing.

the code for my links page:

<?php require "includes/html.inc"; ?>

<?php
    $dbhost = "localhost";
    $dbname = "test";
    $dbuser = 'mike';
    $dbpass = 'password';    

    mysql_connect("$dbhost", "$dbuser", "$dbpass") or die(mysql_error());
    mysql_select_db('test') or die(mysql_error());

//    This is the general links table
$result = mysql_query("SELECT * FROM links WHERE head like 'general'") or die(mysql_error());
$row_count = mysql_num_rows($result) or die(mysql_error());

echo "<table class=\"footcollapse\">";
echo "<thead>\n<tr>\n<th colspan=\"3\" align=\"center\">" . General . "</th>\n</tr>\n</thead>\n";
echo "<tfoot>\n<tr>\n<td colspan=\"3\">expand / collapse</td>\n<tr>\n</tfoot>";
echo "<tr>\n<th>URL</th><th>Description</th><th>Hits</th>\n</tr>\n";

    //find out how many rows were returned    
    while($row = mysql_fetch_array($result)) {

if($row_count % 2) { //if the number of this of this row (not the id form the db) is even...
    $rowc = "#001155"; //...than make this the tr's bgcolor
} else { //If it's odd...
    $rowc = "#225588"; //...make tr use this bgcolor
}
        echo "<tr bgcolor=\"$rowc\"><td>"; //new table row, with alternating bgcolors
        $go = 'redir.php?go=http://' . $row['url'];
                    echo '<a href="' . rawurldecode($go) . '" target="0">' . $row['url'] . '</a>'; //grab the url twice: once for the href, and once for the clickey
        echo '</td><td>';
        echo $row['descrip']; //populate the description for the url
        echo '</td><td>';
        echo $row['hits']; //this is the field that I would like to increment via some kinda click tracking
        echo '</td></tr>';
    $row_count++; //this is the way we count the rows, count the rows, count the rows...
    }
echo "</table>\n";
echo "<br />\n";
?>

And the code for redir.php

<?php
if (isset($_GET['go'])) {
  // redirect browser to clicked url
  $go = rawurldecode($_GET['go']);
  header("Location: " . $go);
  // meanwhile count the click
    $dbhost = "localhost";
    $dbname = "test";
    $dbuser = "mike";
    $dbpass = "password";   

    mysql_connect("$dbhost", "$dbuser", "$dbpass");
    mysql_select_db("test");
	mysql_query("UPDATE links SET hits=hits+1 WHERE url=".$_GET['url']);

} else {
   echo 'header("Location: http://localhost:85/links.php")';
}
?>

bradgrafelman

Whatever happened to the quotes around the URL, such as in here:

$sql = "UPDATE links SET hits=hits+1 WHERE url='" . $go . "'";

apollyonus

At this point, it is moot, as the value for $go is "http://www.outside-domain.com" whereas what's in the field is "www.outside-domain.com"

Roger_Ramjet

OK lets make things easier for ourselves. If that is what is in the db then that is what we put in the link.

// take out the line $go = 'redir.php?go=http://' . $row['url'];
// and just do this
echo '<a href="redir.php?go=' . $row['url'] . '" target="0">' . $row['url'] . '</a>';

Now you are just passing the url without http:// so it display right, posts right and queries the db right.

Now you add the protocol in redir.php

if (isset($_GET['go'])) {
  // redirect browser to clicked url
  header("Location:http:// " . $_GET['go']);
  // meanwhile count the click
    $dbhost = "localhost";
    $dbname = "test";
    $dbuser = "mike";
    $dbpass = "password";   

    mysql_connect("$dbhost", "$dbuser", "$dbpass");
    mysql_select_db("test");
    mysql_query("UPDATE links SET hits=hits+1 WHERE url=".$_GET['go']);

} else {
   echo 'header("Location: http://localhost:85/links.php")'; 
}

My fault really, but heck I do expect people who have got this far to be able to work some things out.

apollyonus

After spending the required amount of time with family and such, I went right back to this little project I've started on. I found something called str_replace and played around with that for a while.

So here it is:

<?php
    $dbhost = "localhost";
    $dbname = "test";
    $dbuser = 'mike';
    $dbpass = '******';    

    mysql_connect("$dbhost", "$dbuser", "$dbpass") or die(mysql_error());
    mysql_select_db('test') or die(mysql_error());

//    This is the general links table
$result = mysql_query("SELECT * FROM links WHERE head like 'general'") or die(mysql_error());
$row_count = mysql_num_rows($result) or die(mysql_error());

echo "<table class=\"footcollapse\">";
echo "<thead>\n<tr>\n<th colspan=\"3\" align=\"center\">" . General . "</th>\n</tr>\n</thead>\n";
echo "<tfoot>\n<tr>\n<td colspan=\"3\">expand / collapse</td>\n<tr>\n</tfoot>";
echo "<tr>\n<th>URL</th><th>Description</th><th>Hits</th>\n</tr>\n";

    //find out how many rows were returned    
    while($row = mysql_fetch_array($result)) {

if($row_count % 2) { //if the number of this of this row (not the id form the db) is even...
    $rowc = "#001155"; //...than make this the tr's bgcolor
} else { //If it's odd...
    $rowc = "#225588"; //...make tr use this bgcolor
}
$base = $row['url'];
        echo "<tr bgcolor=\"$rowc\"><td>"; //new table row, with alternating bgcolors
        $go = 'redir.php?go=http://' . $row['url'];
        echo '<a href="' . rawurldecode($go) . '" target="0">' . $base . '</a>'; //grab the url twice: once for the href, and once for the clickey
        echo '</td><td>';
        echo $row['descrip']; //populate the description for the url
        echo '</td><td>';
        echo $row['hits']; //this is the field that I would like to increment via some kinda click tracking
        echo '</td></tr>';
    $row_count++; //this is the way we count the rows, count the rows, count the rows...
    }
echo "</table>\n";
echo "<br />\n";
?>

As for the script redir.php, (which seemed to be the fun part)

<?php
if (isset($_GET['go'])) {
  // redirect browser to clicked url
	$go = rawurldecode($_GET['go']);
	$url = str_replace('http://','',$_GET['go']); //here's where we strip off the "http://"
  header("Location: " . $go);

  // meanwhile count the click
    $dbhost = "localhost";
    $dbname = "test";
    $dbuser = "mike";
    $dbpass = "*******";   

        mysql_connect("$dbhost", "$dbuser", "$dbpass");
        mysql_select_db("test");

$sql = "UPDATE links SET hits=hits+1 WHERE url='".$url."'";
mysql_query($sql);

} else {
  header("Location: http://localhost:85/links.php");
}
?>

So here I am with a completely customized hit counting table of links... and no static route 🙁

Thanks so much guys for your help with this!