Safin Chowdhury Safin Chowdhury - 24 days ago 5
MySQL Question

Increment a counter in MySQL every time a product item is viewed

I have a product page in my website that works from a MySQL database, product items are created on the page as a thumbnail picture is clicked on.

Is there any way to increment a MySQL counter every time the thumbnail picture is clicked on?

If there is a way of doing this can someone please point me in the right direction with regards to coding this?

NOTE: this code is working but this update all products hits count. i need update only viewed product items row in table

my mysql table looks like

table name: images

id | product_name | image | price | hits

here is my php code:




<?php
$connection = mysql_connect("localhost", "root", ""); // Establishing Connection with Server
$db = mysql_select_db("user"); // Selecting Database

$result=mysql_query("SELECT id,image,SUBSTRING_INDEX(product_name,' ',4) as product_name FROM images");

mysql_query("update images set hits = hits+1 where product_name = product_name");

$count = 0;

while($res=mysql_fetch_array($result))
{
if($count==3) //three images per row
{
print "</tr>";
$count = 0;
if($count==0)
print "<tr>";
print "<td>";

?>
<?php echo"<div>";?>

<?php echo"<img style='heigth:170px; width:200px'src='image1.php?id=$res[id]'>"?>
<?php echo "<a href=\"getImage.php?id={$res['id']}\">{$res['product_name']}<h3>Read More</h3></a>";?>

<?php echo"</div>";?>

<?php
$count++;
print "
</td>";

}

if($count>0)
print "</tr>";

?>


here is getImage.php code`




<!DOCTYPE HTML>
<HTML>
<HEAD>

<style>
img.floatLeft
{
float: left;
margin-right: 10px;
margin-bottom: 1px;
padding-left: 2%;
}
p.padding {
padding-left: 2%;
padding-right: 2%;
}

h3 { color:#3300FF; font-family: Slab,Georgia,serif;font-size: 20px;
letter-spacing: .03em;text-transform: none}

div{background-color: white; border-style: ridge; height: 500px; width: 400px; margin-left:auto;
margin-right:auto; color: #000000; font: 12px arial, sans-serif; line-height: 18px;text-align: justify;}

</style>
</HEAD>
<body>

<?php
if (isset($_GET['id'])) {
$id = $_GET['id'];

$connection = mysql_connect("localhost", "root", ""); // Establishing Connection with Server
$db = mysql_select_db("user", $connection); // Selecting Database


$query1 = mysql_query("select * from images where id=$id", $connection);




echo"<div >";

while ($row1 = mysql_fetch_array($query1)) {
echo"<h3 align='center' color='red'>";
echo $row1['product_name'];
echo"</h3>";


echo "<img class='floatLeft'src=image1.php?id=".$row1['id']." width=150 height=140 />";


echo'<p class="padding">';
echo $row1['price'];
echo"</p>";
echo"</div>";
echo"</div>";



?>

<?php
}
}
?>

</body>
</html>

Answer

Okay so on the /product/product.php take out the update there because you don't want to update every view count, remove

mysql_query("update images set hits = hits+1 where product_name = product_name");

Then on the details.php page add in

if(!empty($_GET['id'])) {
    $id = (int)$_GET['id']; //the (int) forces this to be a number so you cant be injected
    mysql_query("update images set hits = hits+1 where id = $id");
}

You also may want to look at the modulus operator for the if ($count == 3). You also should switch over to using the mysqli or PDO driver.

Comments