Having trouble allowing people to BUMP a unique entry in table...

Question

So, my table has a bunch of codes in it and I don't want more than one of the same code in the table so I have it as "UNIQUE." But, at the same time, I want them to be able to bump their code once every hour.

function some_more_custom_content() {

$output="<BR>";

ob_start();

if ($_REQUEST['code'] != "") {
    $code = $_REQUEST['code'];
    $query="INSERT INTO `fc` (`code`,`datetime`) values ('" . mysql_real_escape_string($code) . "', now())";
    $result=mysql_query($query) or die(mysql_error());
    $seconds = time() - strtotime($fetch_array["datetime"]);
  if($sql){
   echo("Intserted " . htmlentities($code) ." into the top.");
  }else{
   if ($seconds < 60*60) {
          echo ("The code " . htmlentities($code) ." was updated less than an hour ago.");
   } else {
    $query="DELETE FROM `fc` (`code`,`datetime`) values ('" . mysql_real_escape_string($code) . "', now())";
             echo ("Inserted " . htmlentities($code) ." into the top.");
        }

 }}

Now, I tried to get it so that when the code works it submits the code as normal, which I think works.

Now, if it gets a code that is already there I get the duplicate error "Duplicate entry 'Bob' for key 1"

But, I just want it to delete the old query that it found and to resubmit if it's been more than one hour since last submission.

Any ideas what I am doing wrong?

Answer 1

You can just update the date/time field in the database to reflect the time of the "bump".

if ($_REQUEST['code'] != "")
{
    $code = $_REQUEST['code'];

    $sql = "SELECT * FROM fc WHERE code = '" . mysql_real_escape_string($code) . "'";

    $result = mysql_query($sql);
    if (mysql_num_rows($result))
    {
        $row = mysql_fetch_array($result);
        $seconds = time() - strtotime($row["datetime"]);

        if ($seconds > 60*60)
        {
            $sql = "UPDATE fc SET datetime = NOW() WHERE code = '" . mysql_real_escape_string($code) . "'";

            mysql_query($sql);
            echo("Intserted " . htmlentities($code) ." into the top.");
        }
        else
        {
            echo ("The code " . htmlentities($code) ." was updated less than an hour ago.");
        }
    }
    else
    {
        $sql = "INSERT INTO fc (code, datetime) VALUES ('" . mysql_real_escape_string($code) . "', NOW())";

        mysql_query($sql);
        echo("Intserted " . htmlentities($code) ." into the top.");
    }
}

(I probably shouldn't have re-written your code for you, and there are a few more improvements that could be made, but that reflects the best changes I can make with the minimum of changes (does that make any sense?))

Answer 2

Frank, you do not need two different queries

$query="INSERT INTO `fc` (`code`,`datetime`) 
         values ('" . mysql_real_escape_string($code) . "', now())
         ON DUPLICATE KEY UPDATE datetime = case when now() - interval 1 hour > `datetime` 
                                      then NOW() 
                                      else `datetime` 
                                         end";