每当页面加载时更新sql表

Question

I want to make the online/offline status, i made a table user_status and i want to add in the header sql query so every time the user loads a page the table will be updated with that time;

user_status table is : user_id | last_activity_time ; user_id is from the table users;

i added this in the header:

//check update last_time_active
$date = date("Y-m-d H:i:s");

$query = mysql_query("update user_status SET last_activity_time=$date ");
?>

how can i add also the user? if he's logged in, check his user_id and update the table with the user_id and the last_activity_time ? is it correct?

after that i will want to add in specific pages a text next to the username (online/offline).

Thanks all!

Answer 1

Store the userid in the user's $_SESSION when they log in. Then pull it and insert into your query.

<?php
//confirmed login, set $userid to the user's ID
session_start();
$_SESSION['userid'] = $userid;

Then your query would look like this (UPDATED):

// Since, according to your comment, $username = $_COOKIE['username']

"UPDATE user_status SET last_activity_time = NOW() where user_id = (SELECT userid FROM users WHERE username='" . $_COOKIE['username'] . "')";

However DO NOT use mysql_* functions anymore. They are being deprecated. Instead use PDO or mysqli. If you're not sure which one to use, read this article from SO.