Unique visitor vote

[runsome]

I have this simple script which when executed increases the number by 1 in the DB.

I am trying to fugure out the best way to make it so that only a unique visitor can register a "vote". In other words once you have executed the script once you cannot execute it again for at least a day.

so if someone reloaded this script over and over on;y the first visit would add +1 to the count in the DB

I was reading about cookies for this, but im some what new to php and still learning, so Im not quite sure how to apply it to the script.

Any help or an example on how I could do this would be greatly appreciated.

<?php
include("mysql.php");
$totalviewed = $_POST['support'];

$cover = $_GET['id'];
if($cover!=""){
$query = ("UPDATE stats SET support=(support+1) WHERE username='$cover'");
$result = mysql_query($query);
mysql_close($mysql_link);
}

?>

[bpat1434]

I'd suggest adding a different table called "voters". In that table, just store the IP address (or for even more uniqueness, use their hostname 😉 It just adds some more info to keep it more unique. an example hostname is: c-217-100-255-230.hsd1.md.comcast.net)

The IP address is stored in $SERVER['REMOTE_ADDR'] and the hostname is either stored in $SERVER['REMOTE_HOST'] or you have to manually get it via PHP with [man]gethostbyaddr/man;

Now, in that table you'd have listings of hostnames or IPs (or both) and timestamps. An example table would be:
voters
- Hostname varchar(255) NOT NULL PRIMARY KEY
- Timestamp DATETIME NOT NULL

Now, when a visitor visits, you quickly look for their hostname:

<?php
$hostname = gethostbyaddr($_SERVER['REMOTE_ADDR']);
$query = "SELECT 1 FROM voters WHERE `Hostname` = '" . mysql_real_escape_string($hostname) . "' LIMIT 0,1";
$rslt = mysql_query($query);

At this point, we will know (by [man]mysql_num_rows/man) if our user is "new". If they are, you can show the voting stuff, otherwise hide it. To add the functionality of whether or not they're a day old, you'd just have to add a simple "WHERE" clause to it:

$query = "SELECT 1 FROM `voters` WHERE `Hostname` = '" . mysql_real_escape_string($hostname) . "' AND `Timestamp` > (NOW()-(3600*24)) LIMIT 0,1";

Now, if there were rows returned they are not new and can not vote. If rows weren't returned, then they either are new OR haven't visited with in the last 24 hours (3600 seconds * 24 hours).

So, something like this function would return whether or not they can vote:

function canVote() {
  $hostname = ((isset($_SERVER['REMOTE_HOST']) && !empty($_SERVER['REMOTE_HOST'])) ? $_SERVER['REMOTE_HOST'] : gethostbyaddr($_SERVER['REMOTE_ADDR']));

  $query = sprintf("
      SELECT 1
      FROM `voters`
      WHERE `Hostname` = '%s'
        AND `Timestamp` > NOW()-(3600*24)
      LIMIT 0,1", mysql_real_escape_string($hostname));
  $rslt = mysql_query($query);

  // Something went wrong with the query, return -1 to show such
  if(!$rslt)
    return -1;

  // Found someone with same hostname within last 24 hours, they can't vote
  if(mysql_num_rows($rslt) == 1)
    return false;

  // Well, if nothing else caught them, they must be allowed to vote
  return true;
}

Now, when they vote, you'd need to add their hostname and a date and timestamp to the table to record it. A simple function to do that is this:

function touchVoters() {
  $hostname = ((isset($_SERVER['REMOTE_HOST']) && !empty($_SERVER['REMOTE_HOST'])) ? $_SERVER['REMOTE_HOST'] : gethostbyaddr($_SERVER['REMOTE_ADDR']));

  $query  = sprintf("
      INSERT INTO `voters` (`Hostname`, `Timestamp`) 
      VALUES ('%s', NOW())
      ON DUPLICATE KEY UPDATE `Timestamp` = NOW()", mysql_real_escape_string($hostname));

  $rslt = mysql_query($query);

  // No result means something went wrong...
  if(!$rslt)
    return -1;

  // Everything went fine.  Either a row was inserted for that user, or it
  // was updated.
  if(mysql_affected_rows($rslt) == 1)
    return true;

  // Hmm.... something went wrong if nothing was inserted or updated.
  return false;
}

And to trim the fat of the table, you could run a cron-job every day at say 12:00 am or whenever you want to execute a function like this:

function pruneOldVoters() {
  $query = "
      DELETE FROM `voters`
      WHERE `Timestamp` < NOW()-(3600*24*7*4)";

  $rslt = mysql_query($query);

  // Something went wrong...
  if(!$rslt)
    return -1;

  // We deleted some rows....
  if(mysql_affected_rows($rslt) > 0)
    return true;

  // No rows to delete... everything went fine...
  return false;
}

Hopefully that gets you on to some sort of track as to where to go.

[runsome]

You rock! thanks for the speedy/detailed explanation, i'm going to get working on it and see what I come up with, thanks again.

[Piranha]

Just one correction of Bpat's code, if I'm not totally wrong it should be Timestamp < (NOW()-(3600*24)) like this:

$query = "SELECT 1 FROM `voters` WHERE `Hostname` = '" . mysql_real_escape_string($hostname) . "' AND `Timestamp` < (NOW()-(3600*24)) LIMIT 0,1";

[bpat1434]

No piranha, my code is right. I wanted to select those that have voted within the last 24 hours. So if my timestamp is larger (greater than) it has to be more recent (as seconds only count up) and if it's less than, it means that it's older.

If I was looking for anyone that voted outside that 24 hour period, I would use your code. That's not what I'm looking for. But either will work, as long as the code is updated to use it properly.

[Piranha]

Ops, I should have read the code more carefully. Of course you are right, and you are using it the best possible way.

[bpat1434]

That's okay. You made me go back and re-read the code to make sure it was okay. You would be right, if I wanted the opposite of what I have... but that could possibly put too much load on the server.