How to limit user actions based on the last time they used a site feature PHP/Mysql Tutorial

I put together a gamified learning website for my kids to help them practice math and English concepts. As a bonus way to earn currency on the site, I added a feature that let them visit a ‘treasure parlor’ where they got to click a button and get a random amount of gold. In the interest of keeping the site balanced, I needed to make the grab bag (treasure parlor) available only once every 24 hours. This way, they couldn’t continuously build gold off of spamming that button.

To achieve this, I added a table in my SQL database that tracks their visits to the treasure parlor. For the purposes of this example, I instead am going to simply add a column to a users table. If you’re following along and would like to use this for your own site. You can either add the same column to your own users table using this sql (update accordingly if your users table is not labeled ‘users’):

ALTER TABLE users ADD lastvisit TIMESTAMP DEFAULT '0000-00-00 00:00:00';

Alternatively, you can add a new users table using this sql:

CREATE TABLE users (
    userid MEDIUMINT(30) NOT NULL AUTO_INCREMENT,
    username VARCHAR(30),
    lastvisit TIMESTAMP DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (userid)
    );

If you would like to populate the table with a user record to test the script out with, you can run the following sql:

INSERT INTO users (username) VALUES ('admin');

With the table set up and ready to go, let’s get into the php code that will make this feature work. Overall, the idea is to pull the date of the last visit and to see if more than 24 hours (in seconds) has passed. If not, the site pops up a graphic telling the kids it hasn’t been 24 hours since the last visit. If it has been 24 hours, then the grab bag button is made available. For the purposes of this tutorial, we’re just going to have the script output a message as to whether it’s been 24 hours since the last visit or not.

Here is the fully commented code that makes the whole thing work:

// Based on original Treasure Parlor code from Doodad Central script
// Coded by Kim Enders 1/8/18

// First, either include a call to the config file with the db details & connection code (if you have a pre-existing one)
// Or just keep the following code intact (with your own db details, of course)

   // Database Settings -- Edit these settings
   $hostname = "localhost";  // should work in most cases
   $dbuser = "your_user";  // username for your database
   $dbpass = "your_pass";        // the password for your database
   $db = "your_database_name";  // the database name

   // Connect to the database -- Do not edit
   $dbconn = mysql_connect($hostname, $dbuser, $dbpass) or die (mysql_error("Please correct your db settings and try again."));
   $tableconn = mysql_select_db($db) or die (mysql_error("Unable to connect to database. Have you created it yet?"));


// Now, for the real meat of the script

   // Pull the data about the user's last visit
   $pull_visitdata = "SELECT * FROM users WHERE userid = '1'";
   $result = mysql_query($pull_visitdata);

   if($result) {
   $row = mysql_fetch_array ($result,MYSQL_ASSOC);

      // assign the last visit data for our user to a variable
      $pulled_visit = '' . $row['lastvisit'] . '';

      // now format that same data into a form that will match with the following php
      $lastvisit = (strtotime($pulled_visit));

      // use php to capture the exact time currently and assign it to a variable we can work with
      $t = time();

         // now compare to see whether the current exact moment minus 24 hours (86400 seconds) is more or less than the exact time of the last visit
         // this tells the script whether it has been 24 hours since the last visit
         
         if ($lastvisit > $t - 86400) {
              // it hasn't been 24 hours since the last visit, respond accordingly!

              echo "It has not been 24 hours yet!";

              } else {

              // it has been 24 hours since the last visit, so unlock the functionality
              // in this case (for this example), just outputting a message to let the user know that 24 hours has elapsed

              echo "It has been 24 hours. Here is your button!";

              }

   // closing the initial data query

   } 

The code above will leave you with a very simple output that looks like this when you get an affirmative reply:

If you’d rather have javascript alert popup boxes, substitute the following for your outputs:

               echo "<head>
                    <script type=text/javascript>
                    alert(\"Sorry! It has not been 24 hours yet!\");           
                    </script>
                    </head>"; 

and

               echo "<head>
                    <script type=text/javascript>
                    alert(\"Woot! It has been 24 hours since your last attempt!\");           
                    </script>
                    </head>";

As a reminder, make sure to surround the main code in php tags if you’re copying and pasting into a new file. I don’t include the php tags in the php code I embed in my posts due to a limitation imposed by the plugin that allows me to embed php code in my blog posts.