0

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!

Abude
  • 1,930
  • 7
  • 30
  • 55

3 Answers3

2

As stated in the introduction to the PHP manual chapter on the mysql_* functions:

This extension is not recommended for writing new code. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

Using PDO:

$dbh = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
$qry = $dbh->prepare('
  UPDATE user_status SET last_activity_time = NOW() WHERE user_id = ?
');
$qry->bindValue(1, $_SESSION['userid']);
$qry->execute();
eggyal
  • 113,121
  • 18
  • 188
  • 221
2

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.

Community
  • 1
  • 1
Matt
  • 6,745
  • 4
  • 24
  • 49
  • it's great, but actually i have from session the username and i have to make a connection between the `user_id` in the table `users` and the `user_status` table .... – Abude Aug 02 '12 at 14:17
  • it's like this : `$username = $_COOKIE['username'];` how can i make it? can you update the answer please ? thanks ! – Abude Aug 02 '12 at 14:23
  • Updated. My query may not be perfectly optimized, but it will work. – Matt Aug 02 '12 at 14:27
  • it look good, but it's not working, nothing is updating, it still empty the table, there's no need for the `$_SESSION['userid'] = $userid;` right ? – Abude Aug 02 '12 at 15:14
  • Correct. Just use the code below, and verify that **a)** the query being sent is the one you think it is, and **b)** the subquery returns what you expect it to return. – Matt Aug 02 '12 at 15:15
  • the subqurey is working returning an id, and the full query return ok but no modification made on the table, the table is empty, so maybe is there a solution if the user_id is not in the table, make `insert` and if there's make `update` ? thanks~ – Abude Aug 02 '12 at 15:37
  • do you have a solution Matt? Thanks in advance! – Abude Aug 02 '12 at 16:10
  • If `user_id` is a foreign key, then yes, it does have to exist in order for the update to be made. Do the update first. This *should* return the number of rows effected. If that number is 0, then do the insert. – Matt Aug 02 '12 at 16:11
1

Do you want something like:

<?php
    $date = date("Y-m-d H:i:s");

    if(isset($_SESSION['userid']) 
    // or whatever you are using to see if the user is active?
    {
        $query = mysql_query("update user_status SET userID=".$_SESSION['userID'], last_activity_time=".$date.";");
    }

?>
Fluffeh
  • 31,925
  • 16
  • 62
  • 77
  • -1 for using `mysql_*`. This is being deprecated and should no longer be used. [PDO](http://php.net/manual/en/book.pdo.php) or [mysqli](http://php.net/manual/en/book.mysqli.php) are recommended instead. – Matt Aug 02 '12 at 14:03