在 php 会话超时时更新数据库表

Question

我有一个如下所示的 php 代码，其中会话超时在没有活动的 60 分钟后发生。以下代码位于文件/mno.php中。我的登录和注销代码也在同一个文件/mno.php中。

/mno.php

if (isset($_SESSION['LAST_ACTIVITY']) && (time() - $_SESSION['LAST_ACTIVITY'] > 3600)) {

    session_destroy();   // destroy session data in storage

    !isset($_SESSION['pageadmin']);

    /* Update Table (START) */

    $open="false";

    $stmt= $connect->prepare("UPDATE trace_users SET open=? WHERE user_name=?");

    $stmt->bind_param('ss', $open, $_SESSION['user_name']);

    $stmt->execute();

    /* Update Table (END) */

    header('location: /mmo.php');

    exit();

}

$_SESSION['LAST_ACTIVITY'] = time(); // update last activity time stamp 

代码中的表trace_users跟踪所有登录的用户。在该表中，有两列user_name和open。当任何用户登录/注销时，该值设置为true/false。

我已经包含了 sql 查询，其中我试图在没有活动时更新表，但不幸的是，当 60 分钟没有活动发生时，该特定用户的列值未设置为 false 。

这是我试过的：

在做了一些研究之后，我想我必须运行一个计时器（js/ajax）。在下面显示的 javascript 中，我计算了Last Activity和Current time之间的差异。如果超过 60 分钟，那么它将更新数据库表。这是我尝试过的，但我相信需要做更多的工作才能更新数据库中的表。

<script>

let x = setInterval(function() {

    let lastActivity = <?php echo ($_SESSION['LAST_ACTIVITY']); ?>

    let now = <?php echo time() ?>;

    let difference = now - lastActivity;

    if (difference > 3600) {

        clearInterval(x);

    }

}, 1000

);

</script>   

问题陈述：

我想知道我应该在上面的 js（或 php）代码中进行哪些更改，以便在 60 分钟内没有任何活动时，它应该将该特定用户的open列更新为false（在表trace_users中）。

编辑 1：

我的登录代码和会话历史代码在同一个文件/mno.php中。我已将所有内容放在同一个文件/mno.php中。

Answer 1

我认为 Vineys 和 jo0gbe4bstjbs 的回答是错误的，因为当用户关闭浏览器直到 5 秒时，它也无法在 60 分钟和会话后更新表格。会话在 php.ini 配置文件中设置的时间后立即删除。你介意每 5 秒请求一次是解决这个问题的好方法吗？这对性能来说是最糟糕的。如果你想专业地解决这个问题，你应该从表中添加“last_request”列并删除“open”列，并且在每个请求之后你应该将 last_requests 值更新为当前的 unix 时间戳。在获取用户的地方你应该写：$time = time() - 3600;"SELECT * FROM `users` WHERE last_request > $time" //active users"SELECT * FROM `users` WHERE last_request <= $time" //inactive users而不是每 5 秒发送一次 ajax 请求，你应该编写 setTimeout，延迟时间为 3600 秒，运行 window.location.href= '/mmo.php'; 代码。如果您想要最佳性能并在 60 分钟注销后获得准确结果，它的方式很好

Answer 2

我想你意识到这段代码if (isset($_SESSION['LAST_ACTIVITY']) && (time() - $_SESSION['LAST_ACTIVITY'] > 3600)) {&nbsp; &nbsp; //...}$_SESSION['LAST_ACTIVITY'] = time(); // update last activity time stamp&nbsp;在每个请求上运行，并且仅在请求到达时运行想象一下，我访问了您的网站，然后在保持浏览器打开的情况下出去购物。你认为会发生什么？NOTHING - 因为不会有新的请求发送给你（假设你没有实现任何周期性的 ajax 轮询/Websocket 机制）所以服务器不会打扰我，直到我购物回来并刷新页面，然后服务器才会意识到“嗯..这家伙的 LAST_ACTIVITY 已经超过一个小时让我更新我的trace_users表并false为他设置打开”来到您提出的解决方案，它看起来不错并且避免了 websockets/周期性 ajax 请求的复杂性只需要一些小的更正，请按照此处进行基本演示<script>&nbsp; &nbsp; var lastActivity = <?php echo ($_SESSION['LAST_ACTIVITY']); ?>; //the timestamp of latest page refresh or navigation&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //This will remain constant as long as page stays put&nbsp; &nbsp; var now = <?php echo time() ?>; //This takes inital value (technically same as LAST_ACTIVITY) from server&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // but later on it will be incremented by javascript to act as counter&nbsp; &nbsp; var logoutAfter = 5; //I set 5 sec for demo purposes&nbsp; &nbsp; var timer = setInterval(function() {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; now++;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; let delta = now - lastActivity;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if ( delta > logoutAfter) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; alert('you are logged out');&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; clearInterval(timer);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //DO AJAX REQUEST TO close.php&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }, 1000);</script>&nbsp;这里lastActivity将保存页面由服务器发送到浏览器时的时间戳，它永远不会被浏览器上的脚本更改， now是您的计数器，您将使用它来跟踪自页面加载到浏览器以来经过了多少时间，您将每秒增加它并检查是否超过了给定的时间如果 true 执行 ajax 请求（或简单地重定向到 logout.php），您将在其中销毁会话并更新trace_users表以将用户标记为已关闭更新所以ajax会像$.ajax({&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; url: "/close.php",&nbsp;&nbsp; &nbsp; type: 'POST', // GET also fine&nbsp; &nbsp; data: { },&nbsp; &nbsp; success: function(data) {&nbsp; &nbsp; &nbsp; &nbsp; window.location.href= '/mmo.php';&nbsp; &nbsp; },&nbsp; &nbsp; error: function(jqXHR, textStatus, errorThrown) {&nbsp; &nbsp; &nbsp; &nbsp; alert(textStatus);&nbsp; &nbsp; }});&nbsp;和关闭.php<?phpsession_start();$logoutAfter = 5; //5 sec timeout for testing purposes// I'm not sure whether the below if condition check is required here or not&nbsp;// because we have already checked (whether to timeout or not ) in our javascript&nbsp;// and we call close.php only when it's affirmative// I encourage you to test and find out :)if (isset($_SESSION['LAST_ACTIVITY']) && (time() - $_SESSION['LAST_ACTIVITY'] > $logoutAfter)) {&nbsp; &nbsp; session_destroy();&nbsp; &nbsp;// destroy session data in storage&nbsp; &nbsp; !isset($_SESSION['pageadmin']);&nbsp; &nbsp; /* Update Table (START) */&nbsp; &nbsp; $open="false";&nbsp; &nbsp; $stmt= $connect->prepare("UPDATE trace_users SET open=? WHERE user_name=?");&nbsp; &nbsp; $stmt->bind_param('ss', $open, $_SESSION['user_name']);&nbsp; &nbsp; $stmt->execute();&nbsp; &nbsp; /* Update Table (END) */&nbsp; &nbsp; //header('location: /mmo.php'); //<-- no need of it when url hit by ajax&nbsp; &nbsp; exit();}else&nbsp; //<-- note the else$_SESSION['LAST_ACTIVITY'] = time(); // update last activity time stamp&nbsp;

Answer 3

页.php<!-- CODE TO INCLUDE IN HEADER.PHP --><?php&nbsp;&nbsp;session_start();$_SESSION['LAST_ACTIVITY'] = time(); // update last activity time stamp&nbsp;&nbsp;?><!-- CLOSE --><html><head><script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script></head><body></body><script>let lastActivity = <?php echo ($_SESSION['LAST_ACTIVITY']); ?>; //the timestamp of latest page refresh or navigation//This will remain constant as long as page stays putlet now = <?php echo time() ?>; //This takes inital value (technically same as LAST_ACTIVITY) from server+&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // but later on it will be incremented by javascript to act as counterlet logoutAfter = 5; //I set 5 secs for demo purposeslet timer = setInterval(function() {&nbsp; &nbsp; now++;&nbsp; &nbsp; let delta = now - lastActivity;&nbsp; &nbsp; if ( delta > logoutAfter) {&nbsp; &nbsp; &nbsp; &nbsp; alert('you are logged out');&nbsp; &nbsp; &nbsp; &nbsp; clearInterval(timer);&nbsp; &nbsp; &nbsp; &nbsp; //DO AJAX REQUEST TO close.php&nbsp; &nbsp; &nbsp; &nbsp; $.ajax({&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; url: "/mmo.php",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; type: 'POST', // GET also fine&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; data: { },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; success: function(data) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; error: function(jqXHR, textStatus, errorThrown) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log("I am inside error");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; alert(textStatus);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; }}, 1000); //<-- you can increse it( till <= logoutAfter ) for better performance as suggested by @"Space Coding"</script></html>网游.php<?php$servername = "localhost";$username&nbsp; &nbsp;= "username";$password&nbsp; &nbsp;= "password";$dbname&nbsp; &nbsp; &nbsp;= "myDB";$connect = new mysqli($servername, $username, $password, $dbname);if ($connect->connect_error) {&nbsp; &nbsp; die("Connection failed: " . $connect->connect_error);}session_start();$logoutAfter = 5; //5 sec timeout for testing purposesif (isset($_SESSION['LAST_ACTIVITY']) && (time() - $_SESSION['LAST_ACTIVITY'] > $logoutAfter)) {&nbsp; &nbsp; session_destroy();&nbsp; &nbsp;// destroy session data in storage&nbsp; &nbsp; !isset($_SESSION['pageadmin']);&nbsp; &nbsp; /* Update Table (START) */&nbsp; &nbsp; $open="false";&nbsp; &nbsp; $stmt= $connect->prepare("UPDATE trace_users SET open=? WHERE user_name=?");&nbsp; &nbsp; $usname = !empty($_SESSION['user_name'])?$_SESSION['user_name']:'';&nbsp; &nbsp; $stmt->bind_param('ss', $open, $usname );&nbsp; &nbsp; $stmt->execute();&nbsp; &nbsp; /* Update Table (END) */&nbsp; &nbsp; //header('location: /mmo.php'); //<-- no need of it when url hit by ajax&nbsp; &nbsp; exit();}else&nbsp; //<-- note the else$_SESSION['LAST_ACTIVITY'] = time(); // update last activity time stamp&nbsp;&nbsp;?>

Answer 4

这是一个简单的网页时间验证：$modified_on = isset($SERVER['HTTP_IF_MODIFIED_SINCE']) ? $SERVER['HTTP_IF_MODIFIED_SINCE'] : null;$current_time = time();if (!is_null($modified_on) && ($current_time - strtotime($modified_on)) > 3600) {&nbsp; &nbsp; session_destroy();&nbsp; &nbsp; ...}header('Last-Modified: '.gmdate('D, d M Y H:i:s', $current_time).' GMT');...