Updating a record on login with PHP

I have a simple login system that I am trying to develop further by adding the ability to store the last login time of a given user. It appears I can’t quite get my update query correct.

The code i am using in PDO is as follows;


    // This variable will be used to re-display the user's username to them in the 
    // login form if they fail to enter the correct password.  It is initialized here 
    // to an empty value, which will be shown if the user has not submitted the form. 
    $submitted_username = ''; 

    // This if statement checks to determine whether the login form has been submitted 
    // If it has, then the login code is run, otherwise the form is displayed 
        // This query retreives the user's information from the database using 
        // their username. 
        $query = " 
            FROM users 
                username = :username 
        // The parameter values 
        $query_params = array( 
            ':username' => $_POST['username'] 
            // Execute the query against the database 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        catch(PDOException $ex) 
            // Note: On a production website, you should not output $ex->getMessage(). 
            // It may provide an attacker with helpful information about your code.  
            die("Failed to run query: " . $ex->getMessage()); 
        // This variable tells us whether the user has successfully logged in or not. 
        // We initialize it to false, assuming they have not. 
        // If we determine that they have entered the right details, then we switch it to true. 
        $login_ok = false; 
        // Retrieve the user data from the database.  If $row is false, then the username 
        // they entered is not registered. 
        $row = $stmt->fetch(); 
            // Using the password submitted by the user and the salt stored in the database, 
            // we now check to see whether the passwords match by hashing the submitted password 
            // and comparing it to the hashed version already stored in the database. 
            $check_password = hash('sha256', $_POST['password'] . $row['salt']); 
            for($round = 0; $round < 65536; $round++) 
                $check_password = hash('sha256', $check_password . $row['salt']); 
            if($check_password === $row['password']) 
                // If they do, then we flip this to true 
                $login_ok = true; 
        // If the user logged in successfully, then we send them to the private members-only page 
        // Otherwise, we display a login failed message and show the login form again 
            // Here I am preparing to store the $row array into the $_SESSION by 
            // removing the salt and password values from it.  Although $_SESSION is 
            // stored on the server-side, there is no reason to store sensitive values 
            // in it unless you have to.  Thus, it is best practice to remove these 
            // sensitive values first. 
            // This stores the user's data into the session at the index 'user'. 
            // We will check this index on the private members-only page to determine whether 
            // or not the user is logged in.  We can also use it to retrieve 
            // the user's details. 
            $_SESSION['user'] = $row; 
            // Redirect the user to the private members-only page. 
            header("Location: home.php"); 
            die("Redirecting to: home.php"); 
            // Tell the user they failed 
            print("Login Failed."); 
            // Show them their username again so all they have to do is enter a new 
            // password.  The use of htmlentities prevents XSS attacks.  You should 
            // always use htmlentities on user submitted values before displaying them 
            // to any users (including the user that submitted them).  For more information: 
            // http://en.wikipedia.org/wiki/XSS_attack 
            $submitted_username = htmlentities($_POST['username'], ENT_QUOTES, 'UTF-8'); 

Ideally I’d like the user to be logged in and then just before they are directed to ‘members only’ page the current date/time logged as a timestamp in the MySQL DB.

The only success I have had is inserting a new record with no data, but not matching an existing an id and then updating the last login column.

The query I constructed that inserts a blank record is;

            $sql = "REPLACE INTO users(user_lastlogin) VALUES('$user_lastlogin')";
            $q = $db->prepare($sql);

And the following code is sucessfully updating the necessary record but is clearing all existing data and just writing the user_lastlogin value;

$lastlogintime = date("Y-m-d H:i:s");
            $id = $_SESSION['user']['id'];

            // query
            $sql = "REPLACE INTO users(id,username,password,salt,email,created,user_type,user_lastlogin) VALUES('$id','$username','$password','$salt','$email','$created','$user_type','$lastlogintime')";
            $q = $db->prepare($sql);

How can I update the record relating to their user ID with this data?

Given you need to update an existing record, what about using an UPDATE query?

I have tried an update query but couldn’t get it to write a thing. The user would be logged in as desired but the last login time in the user record in the users table would not contain anything.

Why not to

  1. “try” an UPDATE query according some tutorial or a working example?
  2. Show us what have you tried?

The example I tried was;

            $lastlogintime = date("Y-m-d H:i:s");
            $id = $_SESSION['user']['id'];

            // query
            $sql = "UPDATE `users` SET `user_lastlogin` = `$lastlogintime` WHERE `id` = ?";
            $q = $db->prepare($sql);

It was put together using a couple of results from Google search.

in your working example you are using a placeholder in the query (:username) AND $query_params variable to provide a value for it.
Whereas in the last example you are using a placeholder (? mark) in the query, but no variables at all.

Why putting together examples from google if you have a working example of your own right before your eyes?

Because I can’t write the query… I’ve tweaked what I had working to try and get it to function as intended. Obviously that failed so I’m now asking the question.

Your query is all right. All you need is to provide a value for the placeholder. Exactly the same way you did it in the SELECT query

So, more like this;

$lastlogintime = date("Y-m-d H:i:s");
            $id = $_SESSION['user']['id'];

            // query
            $sql = "REPLACE INTO users(id,user_lastlogin) VALUES('$id','$lastlogintime')";
            $q = $db->prepare($sql);

All that appears to be doing is deleting everything (therefore destroying the user account) and updating the lastlogin time.

Something llike:-

        // query
        $sql = "UPDATE `users` SET `user_lastlogin` = NOW() WHERE `id` = :id";
        $q = $db->prepare($sql);
        $q->execute([':id' => $id]);
1 Like

Perfect - thanks :thumbsup:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.