Save last login via mysql and show who is online

Question

I’m developing a project, which is actually already working. But there’s something I’ve tried a thousand ways but still can’t implement. I need to record each user’s login and logout dates/times in an existing table in the database. And then display whether this user is still online or not...

Below put the table of my BD and login and logout pages:

Table:

CREATE TABLE `tab_clientes` (
  `id` int(11) NOT NULL,
  `nome` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sobrenome` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` varchar(20) COLLATE utf8_unicode_ci DEFAULT 'Inativo',
  `foto` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `username` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `mast_id` int(11) NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `gender` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `data_cadastro` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `data_alteracao` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `city` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `idade` int(3) NOT NULL,
  `ip` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `last_login` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Login page:

<?php
// Initialize the session
session_start();

$message = '';
 
// Check if the user is already logged in, if yes then redirect him to welcome page
if(isset($_SESSION["loggedin"]) && $_SESSION["loggedin"] === true){
  header("location: welcome.php");
  exit;
}
 
// Include config file
require_once "config.php";
include('chat/database_connection.php');
 
// Define variables and initialize with empty values
$username = $password = "";
$username_err = $password_err = "";
 
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){
 
    // Check if username is empty
    if(empty(trim($_POST["username"]))){
        $username_err = "Please enter username.";
    } else{
        $username = trim($_POST["username"]);
    }
    
    // Check if password is empty
    if(empty(trim($_POST["password"]))){
        $password_err = "Please enter your password.";
    } else {
        $password = trim($_POST["password"]);
    }
    
    // Validate credentials
    if(empty($username_err) && empty($password_err)){
        // Prepare a select statement
        $sql = "SELECT id, username, password FROM tab_clientes WHERE username = ?";
        
        if($stmt = mysqli_prepare($link, $sql)){
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "s", $param_username);
                     
            // Set parameters
            $param_username = $username;
            
            // Attempt to execute the prepared statement
            if(mysqli_stmt_execute($stmt)){
                // Store result
                mysqli_stmt_store_result($stmt);
                
                // Check if username exists, if yes then verify password
                if(mysqli_stmt_num_rows($stmt) == 1){                    
                    // Bind result variables
                    mysqli_stmt_bind_result($stmt, $id, $username, $hashed_password);
                    if(mysqli_stmt_fetch($stmt)){
                        if(password_verify($password, $hashed_password)){
                            // Password is correct, so start a new session
                            session_start();
                            
                            // Store data in session variables
                            $_SESSION["loggedin"] = true;
                            $_SESSION["id"] = $id;
                            $_SESSION["username"] = $username;                            
                            
                            // Redirect user to welcome page
                            header("location: welcome.php");
                        } else{
                            // Display an error message if password is not valid
                            $password_err = "The password you entered was not valid.";
                        }
                    }
                } else{
                    // Display an error message if username doesn't exist
                    $username_err = "No account found with that username.";
                }
            } else{
                echo "Oops! Something went wrong. Please try again later.";
            }

            // Close statement
            mysqli_stmt_close($stmt);
        }
    }
    
    // Close connection
    mysqli_close($link);
}
?>
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>Tutto L'amore | Login</title>
        <link rel="shortcut icon" href="images/icons/icon.png" />
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
    </head>
    
    <body>
        <div class="topnav" id="myTopnav">
      <a href="/" class="active">Home</a>
      <!--<a href="/Membros/index.php">Membros</a>-->
      <a href="/contatos">Contato</a>
      <a href="register.php">Cadastrar</a>
      <a href="#home" style="width: 10%"><img src="images/logo.png" style="width: 80px; padding-top: 1px; padding-bottom: -3px"></a>
      <a href="javascript:void(0);" class="icon" onclick="myFunction()">
        <i class="fa fa-bars"></i>
      </a>
    </div>
        
        <div class="underLine"></div>
        
    <script>
    function myFunction() {
      var x = document.getElementById("myTopnav");
      if (x.className === "topnav") {
        x.className += " responsive";
      } else {
        x.className = "topnav";
      }
    }
    </script>    
        
        <div class="container">
        <div class="wrapper" style="width: 400px; margin: auto">
            <h2>Entrar</h2>
            <p><h5>Por favor para logar preencha com seus dados.</h5></p>
            <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
                <div class="form-group <?php echo (!empty($username_err)) ? 'has-error' : ''; ?>">
                    <label><h6 style="font-weight: bold;">Nome de usuário</6></label>
                    <input type="text" name="username" class="form-control" value="<?php echo $username; ?>">
                    <span class="help-block"><?php echo $username_err; ?></span>
                </div>    
                <div class="form-group <?php echo (!empty($password_err)) ? 'has-error' : ''; ?>">
                    <label><h6 style="font-weight: bold;">Senha</h6></label>
                    <input type="password" name="password" class="form-control">
                    <span class="help-block"><?php echo $password_err; ?></span>
                </div>
                <div class="form-group">
                    <input type="submit" class="btn btn-primary" value="Entrar">
                </div>
                <p><h5>Não tem uma conta? <a href="register.php">Cadastre-se agora</a>.</5></p>
            </form>
        </div>    
     </div>    
    <?php
    
    include 'includes/footer.php';
    ?>
    </body>
    </html>

Logout page:

<?php
// Initialize the session
session_start();
    
// Unset all of the session variables
$_SESSION = array();
    
// Destroy the session.
session_destroy();
    
// Redirect to login page
header("location: login.php");
exit;
?>

Answer 1

You’ll have to do it with cookies. Set when it logs in and at the same time the Insert in the database and when it exits.

Like this you arrow: setcookie("Cookieteste", $value);

So you clean up:

Function clearcookie( $inKey ) { clearpieces( $inKey , 1 ); setcookie( $inKey , '' , time()-3600 , '/' , '' , 0 ); unset( $_COOKIE[$inKey] ); }