PHP: How to increment a value in the table row to

Question

I'm using the following to inset a number into 'mviews' every time some map is viewed.

Question 1. Where in the following code do i add 'ON DUPLICATE KEY UPDATE mviews = mviews+ 1' so it can increment on duplicate?

Question 2. How do i limit counts to one IP?

Question 3. How can i limit this IP to only increment the 'mviews' only within 24 hours; only the first view will be counted on every 24 hours, the rest of the views within 24 hours after the first view are not suppose to be counted.

<?php
require_once 'db_conx.php';
$result = mysql_query( "UPDATE profiles SET mviews = '1' WHERE pid = '2' ")  or die (mysql_error());
  /*ON DUPLICATE KEY UPDATE mviews = mviews+ 1 */               
if($result){
                echo "Views + 1";
                }
                else {
                     echo "Views inser error";
                }
mysql_close($con);
?> 

Answer 1

As promised for anyone who'd be in need of a WORKING version of this in PDO; (this is adapted from an answer that I found here on SO, but, can't remember at the second, so I'll look it up for an edit to give the answerer credit.)

track.php

<?php
    //Your database information
   include '../includes/db.php';

// Fetch id (article_id) from $_GET parameter
$article_id = '';
if (isset($_GET['id'])) $article_id = intval($_GET['id']);

if (!$article_id) {
  print "No article ID found!\n";
} else {

   //
   // Fetch Article ID
   //
  $sql = "SELECT * FROM `articles` WHERE article_id = :article_id";
  $sth = $pdo->prepare($sql);
  $sth->bindParam(":article_id", $article_id);
  $sth->execute();
}

if (!$sth) {
// No article!
  echo 'Invalid Article ID!\n';
 } else {
// Article found!
   //
   // Update Clicks Column
   //
   $sql = "UPDATE articles SET clicks=clicks+1 WHERE article_id = :article_id";
   $sth = $pdo->prepare($sql);
   $sth->bindParam(':article_id', $article_id);
   $sth->execute();
}
header('Location: view_article.php?id='.intval($_GET['id']));
?>  

As this code is working flawlessly, for MY purposes, here's a little caveat for those looking to just blindly cut-and-paste...it updates the clicks column EACH TIME IT'S CLICKED, soooooo if you need to limit it to a certain amount of clicks or some other type of functionality, I wish you well in your endeavors (lol!). I just wanted to publish this as a current, working model of the PDO kind. In fact, I may have to end up using this solution as a basis of another mind-bending jam I'm in right now. In fact, I'm almost willing to bet dollars to donuts I'll be completely right about this move.

HTHSO

P.S. ...PDO is sooooooo annoyingly difficult...but worth it.

Answer 2

DUPLICATE KEY UPDATE

is used with INSERT and not with UPDATE statement DOCS

count for one IP will be like this

SELECT COUNT(*) FROM profiles WHERE IP = "127.1.0.0";

[If you are not looking for what I have written above]

if IP address is your primary key then

INSERT INTO 
profiles (ip,views) 
VALUES ("127.1.0.0",1)
ON DUPLICATE KEY 
UPDATE views=views+1;

If you want your Code to work properly[dont use mysql_* also escape user input]

<?php
require_once 'db_conx.php';
$result = mysql_query( "SELECT * FROM profiles WHERE pid =2")  or die (mysql_error());
  /*ON DUPLICATE KEY UPDATE mviews = mviews+ 1 */               
if(mysql_num_rows($result) == 0){
     mysql_query( "INSERT INTO profiles (views) value(1) ")  or die (mysql_error());
    }else {
             mysql_query( "UPDATE profiles SET mviews = mviews +1 WHERE pid = '2' ")  or die (mysql_error());
      }
mysql_close($con);
?> 

Answer 3

To both Eustatia & Arun Killu - THANK YOU!!! Because of your posted/edited solution, I was helped out of a mind-bending jam. I have to create an addendum (as my currently-resolved situation may be of help to someone reading this).

My situation was how to establish a click-tracking process for a variable-based hyperlink (i.e.);

<a target="_blank" href="<?php echo $link;?>"><b><?php echo $title;?></b></a>

I kept getting the link to echo out the representative information, open the correct, id-tagged link request (and not baffle user expectation of conventional click-behavior), but the ability to MULTIPLY log link clicks was solved by this post. NOTE TO THE SUPRA-FINICKY - YES! PDO IS THE DEFACTO STANDARD.

I apologize PROFUSELY for spreading the rank taint of deprecation, but if I understand what I'm doing now, I should be able to assimilate rudimentary PDO mastery in a 3 - 6 week period.

<?php 
/** Connect to DB */
mysql_connect("localhost","database_user","pwd") or die(mysql_error());
mysql_select_db("database_name") or die(mysql_error()); 

$id = mysql_real_escape_string($_GET['id']);

/** retrieve URL */
$result = mysql_query("SELECT * FROM `articles` WHERE ID = '$id'") or die(mysql_error());

/*ON DUPLICATE KEY UPDATE clicks = clicks+ 1 */               
if(mysql_num_rows($result) == 0){
 mysql_query("INSERT INTO `articles`(`clicks`) value(1)")  or die (mysql_error());
}else {
         mysql_query("UPDATE `articles` SET clicks=clicks+1 WHERE `id` = '$id'")  or die (mysql_error());
  }
$row = mysql_fetch_array($result); 
mysql_close();

header("Location: " . $row['link']);
?>

I ended up changing the configuration of the link so the id passed the correct link to the target page;

<?php echo "<a href='pagetracking.php?id=" . $id . $link . "' target='_blank'> <font color='#0000CC' size='5'><b>" . $row['title']. "</b></font></a>"; ?>

Thank you SO MUCH for posting Eustatia & Arun Killu, I really appreciate the tip. If anyone else here happening across this sees anything that's EXCEPTIONALLY unforgivable (even in this decrepit state, lol) - PLEASE, do - not - hesitate - to scream on it robustly. I need all the help I can get.