What are the best practices for structuring database tables and queries in PHP applications to track user activity like logins?

To track user activity like logins in PHP applications, it is best to create a separate table in the database to store login activity records. This table can include fields such as user_id, login_time, and logout_time. When a user logs in, a new record is inserted into this table with the user's ID and the current timestamp. When the user logs out, the corresponding record can be updated with the logout timestamp. 

// Assuming you have a database connection established

// Function to insert a login record into the activity log table
function logUserLogin($userId) {
    $loginTime = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO user_activity_log (user_id, login_time) VALUES ('$userId', '$loginTime')";
    
    if ($conn->query($sql) === TRUE) {
        echo "Login activity logged successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

// Function to update the logout time in the activity log table
function logUserLogout($userId) {
    $logoutTime = date('Y-m-d H:i:s');
    
    $sql = "UPDATE user_activity_log SET logout_time = '$logoutTime' WHERE user_id = '$userId' AND logout_time IS NULL";
    
    if ($conn->query($sql) === TRUE) {
        echo "Logout activity logged successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

Keywords

database tables queries user activity logins PHP applications

Related Questions