Simple mysql hit counter not working

Daniel_Exe

<?php

// Quote variable to make safe
function quote_smart($value)
{
   // Stripslashes
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   // Quote if not a number or a numeric string
   if (!is_numeric($value)) {
       $value = "'" . mysql_real_escape_string($value) . "'";
   }
   return $value;
}

if(isset($_GET['id'])){
	mysql_select_db('**', mysql_pconnect('**','**','**')) or die (mysql_error());

$filtered_id = htmlentities($_GET[id]);
$id = quote_smart($filtered_id); 
$filtered_id = htmlentities($_GET[id]);
$id = quote_smart($filtered_id); 
if (ctype_digit($id)) { //id has been filtered
	$query_id="SELECT * FROM tutorials WHERE id=$id";
	$row_id=mysql_query($query_id);
	$result_id=mysql_fetch_array($row_id);
	$page_title="".$result_id['title']." - ".$result_id['category']." tutorial";
	$hits=$result_id['hits']++;
}
}

include("./header.php");

mysql_select_db('**', mysql_pconnect('**','**','**')) or die (mysql_error());

if(ctype_digit($id)){
		$query_hits="UPDATE tutorials SET hits=$hits WHERE id=$id";
		mysql_query("$query_hits") or die ("Could not update hits!");
		echo "<h1>".$result_id['title']."</h1>
		<b>By:&nbsp;".$result_id['author']."</b><br /><br />
		".$result_id['content']."<br />$hits";
}

if(!ctype_digit($id)){
		echo "Error: Tutorial doesn't exist!";
}

if(!isset($_GET['id'])){
	echo "You must select a tutorial!<br /><br />";
}

?>
<br /><br />
<?php include("./footer.php"); ?>

That is the code I'm using. It's suppose to update the hits row of the tutorials table according to the id whenever the page is viewed. Unfortunately, this is not working at all, and the value of $hits is always 1 (even after I try to increase it), and the number of hits in the database always stays 1. The row just won't update, and I don't understand why. I tried something else, and the value of $hits was updated, but the databse just wouldn't update. I've tried many other things, but I just can't get it to work. >_> It seems to me that this should be easy, but I'm lost now. Any and all help would be greatly appreciated.
Thanks,
Daniel

P.S. I know quote_smart isn't necessary, but I'm using it for something else, so I'm just gonna leave it there. Also, I have to select the database twice as header.php connects to a different one.

Rodney_H_

It is late so forgive me if I am just plain wrong, LOL...

But, I will take a stab at it:

1) You have some code that is entirely redundant:

Daniel Exe wrote:

<?php

// Look closely at these four lines:
$filtered_id = htmlentities($_GET[id]);
$id = quote_smart($filtered_id); 
$filtered_id = htmlentities($_GET[id]);
$id = quote_smart($filtered_id); 

// etc...
?>

Also, I think you can get away with a ONE-step query with something like this:

<?php
// the general idea:
if(isset($_GET['id'])  &&  is_numeric($_GET['id']))
{

$query = "UPDATE `tutorials` SET `hits` = (`hits` + 1) WHERE `id` = ". (int)$_GET['id'];

// etc...
}
?>

I don't know if my query is 100% accurrate in terms of MySQL syntax, but it shows you the general idea...

Daniel_Exe

<?php

mysql_select_db('***', mysql_pconnect('localhost','***','***')) or die (mysql_error());

if(isset($_GET['id'])  &&  ctype_digit($_GET['id'])){
	$query_id="SELECT * FROM tutorials WHERE id=$id";
	$row_id=mysql_query($query_id);
	$result_id=mysql_fetch_array($row_id);
	$page_title="".$result_id['title']." - ".$result_id['category']." tutorial";
}

include("./header.php");

mysql_select_db('***', mysql_pconnect('localhost','***','***')) or die (mysql_error());

$hits = $result_id['hits'];

if(isset($_GET['id'])  &&  ctype_digit($_GET['id'])){
		$query_hits="UPDATE `tutorials` SET `hits` = ($hits + 1) WHERE `id`=$id";
		mysql_query("$query_hits") or die ("Could not update hits!");
		echo "<h1>".$result_id['title']."</h1>
		<b>By:&nbsp;".$result_id['author']."</b><br /><br />
		".$result_id['content']."<br />$hits";
}

if(!isset($_GET['id'])  ||  !ctype_digit($_GET['id'])){
echo "You must select a tutorial!";
}

?>
<br /><br />
<?php include("./footer.php"); ?>

That's my updated code. I prefer to use ctype_digit than is_numeric as it doesn't allow signs, decimals, etc. I also removed all the other junk from the script as you had mentioned. I had noticed the sloppy job I had done right before posting the code, although I knew none of that filtering could have affected the end results. Anyway, with my code, the value of $hits is equal to the amount of hits in the table, but the number still doesn't increase in the SQL. I believe

$query_hits="UPDATE `tutorials` SET `hits` = ($hits + 1) WHERE `id`=$id";

is still the problem as that SQL simply will not increase the number. =\ Well, thanks again for the help and I hope someone could lend a hand with resolving this.
-Daniel

Rodney_H_

I think is more to the point...

<?php
if(!isset($_GET['id']) || !ctype_digit($_GET['id']))
{
	die('You must select a tutorial!');
}

include("./header.php");

$connection = mysql_pconnect('localhost', 'username', '****') or die(mysql_error());

$database = mysql_select_db('dbName', $connection) or die(mysql_error());

$query = "SELECT `title`, `category`, `hits` 
		  FROM `tutorials` 
		  WHERE `id` = " . $_GET['id'] . "
		  LIMIT 1";

$result = mysql_query($query) or die(mysql_error());

if(mysql_num_rows($result) == 0)
{
	die('That is an invalid category/tutorial!');
}

$row = mysql_fetch_array($result, MYSQL_ASSOC);

echo '<h1>' . $row['title'] . ' - ' . $row['category'] . ' tutorial</h1>';

$hits = $row['hits'] + 1;

$hits = $result_id['hits'];

$query = "UPDATE `tutorials` 
		  SET `hits` = " . $hits . "
		  WHERE `id` " . $_GET['id'] . "
		  LIMIT 1";

$result = mysql_query($query) or die(mysql_error());



if(mysql_affected_rows() == 1)
{
	echo '(hits updated)';
}


include("./footer.php"); 

?>

Daniel_Exe

I get the following error with that script:

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 'WHERE id 1 LIMIT 1' at line 3

I'm horrible with SQL so I'm not sure what the problem is. >_>
-Daniel

Rodney_H_

It means that I forgot an "=" eqal sign...

Try this one:

<?php
if(!isset($_GET['id']) || !ctype_digit($_GET['id']))
{
	die('You must select a tutorial!');
}

include("./header.php");

$connection = mysql_pconnect('localhost', 'username', '****') or die(mysql_error());

$database = mysql_select_db('dbName', $connection) or die(mysql_error());

$query = "SELECT `title`, `category`, `hits` 
		  FROM `tutorials` 
		  WHERE `id` = " . $_GET['id'] . "
		  LIMIT 1";

$result = mysql_query($query) or die(mysql_error());

if(mysql_num_rows($result) == 0)
{
	die('That is an invalid category/tutorial!');
}

$row = mysql_fetch_array($result, MYSQL_ASSOC);

echo '<h1>' . $row['title'] . ' - ' . $row['category'] . ' tutorial</h1>';

$hits = $row['hits'] + 1;

$hits = $result_id['hits'];

$query = "UPDATE `tutorials` 
		  SET `hits` = " . $hits . "
		  WHERE `id` = " . $_GET['id'] . "
		  LIMIT 1";

$result = mysql_query($query) or die(mysql_error());



if(mysql_affected_rows() == 1)
{
	echo '(hits updated)';
}


include("./footer.php"); 

?>