Trying to ad a online table, what should it be set to?

[Breana]

Ok, i am adding a table to the users table called online.
So when they login it will set (online) to Y and when they logout it will be set to N.

But what should it be created as, it will be updated often and i need to beable to read it from my php in the Members online section?
varchar(50), int(10), or Text...

And how do i call the results of it not in text Y but in total.
Like lets say there is a total 50 users=Y so it will PRINT 50.

I have tried several times but i keep getting eather 9empty) or (Y) Not the total as 8

I created the table like this:
[PHP]ALTER TABLE users
ADD online varchar(1) default 'N';[/PHP]

And call it like this:
[PHP]$sql="SELECT online FROM users WHERE online = $Y";
$result = mysql_query($sql ,$db);[/PHP]

And call it:
[PHP]<?php printf($online); ?>[/PHP]

is there a special way to count the value of Y and skip N

[ak1dnar]

Ok, i am adding a table to the users table called online.
So when they login it will set (online) to Y and when they logout it will be set to N.

But what should it be created as, it will be updated often and i need to beable to read it from my php in the Members online section?
varchar(50), int(10), or Text...

Still I am struggling to understand your question Breana. could you please explain it little bit clearly. Thanks!

[Breana]

Ok, i am doing this.
ALTER TABLE users
ADD online (.........) ;

But what do i set it to?
Text, INT(10) , VARCHAR (50) or else?

I need to be able to read from it and update it easly.
Default has to be 'n'

So i tried this:
ALTER TABLE users
ADD online char(1) default 'n';

[ak1dnar]

And how do i call the results of it not in text Y but in total.
Like lets say there is a total 50 users=Y so it will PRINT 50.

I have tried several times but i keep getting Y,Y,Y,Y,Y,Y,Y,Y Not the total as 8

Now you have edited the original post with more details, but yet it doesn't describe the problem. Sorry...Could you please show us the NOT working code snippets. then it might be easier to get through it.

[ak1dnar]

Ok, i am doing this.
ALTER TABLE users
ADD online (.........) ;

But what do i set it to?
Text, INT(10) , VARCHAR (50) or else?

I need to be able to read from it and update it easly.
Default has to be 'n'

So i tried this:
ALTER TABLE users
ADD online char(1) default 'n';

Create a separate column (Say Status) on the users table where you have stored those login details. and set the default value to the column as 'N'.
Then when users logs in to the system UPDATE the records with Setting up the online status with 'Y'.

data type for the Status column should be varchar(1).

[Breana]

I did that, i updated my first post.
But i cant call it, it just shows y

[pbmods]

Heya, Breana.

Here's an idea which will let you see what Users are logged in and simultaneously enforce page timeout.

Instead of 'Y/N', use a timestamp:

ALTER TABLE
        `users`
    ADD
        `last_active`
            TIMESTAMP
            NOT NULL
            DEFAULT CURRENT_TIMESTAMP
            ON UPDATE CURRENT_TIMESTAMP,
    ADD
        KEY
            (`last_active`);
 

Then, set and enforce your default timeout, and if the User's session is still valid, 'touch' his `last_active` value:

// Check to see if User's session has timed out.
$_sql = "
SELECT
        `last_active`
    FROM
        `users`
    WHERE
        `user_id` = '{$user_id}'
    LIMIT 1";
$_res = mysql_query($_sql);
$_row = mysql_fetch_row($_res);  //  $_row[0] is `last_active`.
mysql_free_result($_res);
 
// Set timeout to 15 minutes
define('TIMEOUT', 900);
if( strtotime($_row[0]) < (time() - TIMEOUT) )
{
    // User is no longer logged in; his session has timed out
}
else
{
        // User is logged in; set his `last_active` to CURRENT_TIMESTAMP.
    $_sql = "
UPDATE
        `users`
    SET
        `last_active` = NOW()
    WHERE
        `user_id` = '{$user_id}'
    LIMIT 1";
    mysql_query($_sql);
}
 

Then, to find the number of Users that are online (active within the last 15 minutes):

SELECT
        COUNT(`user_id`)
    FROM
        `users`
    WHERE
        `last_active` > TIMESTAMP(NOW(), '00:15:00')
    GROUP BY
        `user_id`
 

[Breana]

Ok, seems complex but how do i set there "CURRENT_TIMESTAMP" in the login or is it auto done?

[pbmods]

Heya, Breana.

CURRENT_TIMESTAMP is an alias for NOW(). It's part of the 'magical' properties in MySQL timestamps.

It looks complex, but in reality, it's not that different than what you are doing now; instead of using 'y'/'n', you are using a number that represents the last time the User did anything.

Instead of checking to see if `online` is 'y', you are checking to see if `online` is within the last 15 minutes.

Your call. I had fun writing the timestamp solution at any rate*, so don't worry about my feelings if you want to go with the 'y'/'n' column anyway :)

*actually, you forced me to take a critical look at an odd 'feature' of my code. Right now, my session frameworks store the timeout data in the _SESSION instead of in the database. I really should be fixing this! Thanks for the inspiration!

[Breana]

So do i set the the table like this..

[PHP]$sql = "UPDATE last_active from users SET last_active >= ($now - $timeout)";
$result = $this->_query($sql);[/PHP]

Like that?

[pbmods]

Heya, Breana.

You've managed to create a UPDECT query, which is a cross between a SELECT and an UPDATE. It's not valid, but it is very creative.

The idea is that the `last_active` field stores the time of last activity; that is, the time that the User loaded the page. So you can simply set its value to the current datetime when updating it:

 $_sql = "
UPDATE
        `users`
    SET
        `last_active` = NOW()
    WHERE
        `user_id` = '{$user_id}'
    LIMIT 1";
 

[Breana]

I tried to but it dont change, allways 000-000-000
Iposted my code maybe i placed it wrong..

[PHP]<?php

$sql = "select * from users where login = '$login' and password = '$password'";
$result = mysql_query($sql ,$db);

if ($myrow = mysql_fetch_array($result)) {

do {

$uid = $myrow["userid"];
$uname = $myrow["login"];

} while ($myrow = mysql_fetch_array($result));

$loggedin = true;
$upwd = $password;
$msg = "<table width=\"500\" border=\"0\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\">
<tr>
<td><img src=\"images/top_c.gif\" width=\"500\" height=\"24\"></td>
</tr>
<tr>
<td align=\"center\" background=\"images/b_m.gif\">Welcome <font color=\"#FF0000\">$uname</font>, you are now logged in.</td>
</tr>
<tr>
<td><img src=\"images/bottom_c.gif\" width=\"500\" height=\"24\"></td>
</tr>
</table><br />
<br />
<a href=\"index.php\">CONTINUE >></a><br /><br /><p align=\"center\"><img src=\"images/Welcome_Back.gif\" width=\"300\" height=\"282\" /></p>";
$sql = "UPDATE `users` SET `last_active` = NOW() WHERE `user_id` = '{$user_id}' LIMIT 1";

} else {
$loggedin = false;
$upwd = "";
$uid = "";
$uname = "";
$msg = "<img src=\"images/invalid.gif\" width=\"402\" height=\"107\" /><br /><b><font color=\"#FF0000\">Sorry,</font></b> that login and password is not valid.<br /><br />If you have forgotten your password <a href=forgot.php>Reset Password</a>. <br />If you are a new user you will need to <a href=newuser.php>Create A New Account!</a>";


}

session_register("loggedin");
session_register("upwd");
session_register("uid");
session_register("uname");

?>[/PHP]

[pbmods]

Heya, Breana.

Make sure that the `last_active` field is a TIMESTAMP with DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.

Also note this line:

 $sql = "UPDATE `users` SET `last_active` = NOW() WHERE `user_id` = '{$user_id}' LIMIT 1";
 

You'll want to replace $user_id with $uid, since that's the variable that stores the User's ID. Also, in your Users table, the User ID field is userid, not user_id. I just put some generic names in there.

Your code should look more like this:

 $sql = "UPDATE `users` SET `last_active` = NOW() WHERE `userid` = '{$uid}' LIMIT 1";
 

[Breana]

Oh ok, i did it now error or blank page, but on the part where i posted the whos online is empty?

I used this to call it:
[PHP]Total online: <?
$_sql = "SELECT
COUNT(`uid`)
FROM
`users`
WHERE
`last_active` > TIMESTAMP(NOW(), '00:15:00')
GROUP BY
`uid`" ?>[/PHP]

[pbmods]

Heya, Breana.

You're setting the value of $_sql, but you never call mysql_query()....

[Breana]

Ok like this:
[PHP]<?
$sql = "SELECT COUNT(`uid`) FROM `users` WHERE `last_active` > TIMESTAMP(NOW(), '00:15:00')
GROUP BY `uid`";
$result = mysql_query($sql ,$db);?>[/PHP]

[pbmods]

Heya, Breana.

That looks pretty good. Then just call mysql_fetch_row($result), and echo index 0 of the resulting array.

[Breana]

Ok i have it like this:
[PHP]Total online: <?
$sql = "SELECT COUNT(`uid`) FROM `users` WHERE `last_active` > TIMESTAMP(NOW(), '00:15:00')
GROUP BY `uid`";
$result = mysql_query($sql ,$db);
mysql_fetch_row($result);?>[/PHP]

"echo index 0" what....
you mean i need to echo ("index 0"); or what?

[pbmods]

Heya, Breana.

This line:

mysql_fetch_row($result);
 

Returns an array. You'll want to assign that array to a variable and then echo its 0th index.

I.e.,

$row = mysql_fetch_row($result);
echo $row[0];
 

[Breana]

Ok now it works sort of, it don't show 1 or 5 but this 209.33.95.33?
What is that... my ip address. (0 ^ 0)

Do i need to change the (0) to uid? so it shows a number

[Breana]

I just tried again i cant seem to get the number to pop not the ip or what ever that is...

[Breana]

Anyone got an idea on how to fix this?