[SOLVED] User's status

[deepson2]

Hello,

 

I know its already discussed many times. but i have not found any proper solution on it. so please bare me. because this thread is more of repetitive thread.

 

I want show users status, whether he is online or offline( and other members also whther they are online or offline.

 

what i have tried and got failed

 

1)added one status(0/1) column in to my earlier designed database. onces user logges in i updated my column form 0 to 1

 

limitation or where i got stucked - 1)what about logged out issue.because i was using session to link pages. so how can i updated my status column with logout 2) what if user just went off without doing log out?

 

2)calcualting time from once user logged in to now( 15 min consideration)

http://www.phpfreaks.com/forums/index.php/topic,253459.0.html

 

 

but it seems i am not able to figure it out that-

1)how can i show including all the users plus the user hows online(with each one's profile page to show they are online.

 

2)unable to find out the way to keep online user who are within only 20 min before now.

 

3) using IP address. i have searched for different tutorials. something like this

http://www.tutcity.com/view/whos-onl...ipt.16480.html

 

but it seems i just wont understand it properly.

 

can anyone help me in proper direction how can i achieve this?

thanks in advance.

[Masna]

In addition to the ENUM in your users table that stores online status, you'll need a field to store a UNIX timestamp of the time any given user last visited a page on your site: just update it, filling in time(), each time a user "goes somewhere" on your site, so to speak.

 

Then, you'll need a script that runs on every page that runs through all users, and checks to see if a user's last updated time is less than time() - (60*15); if it is, set their online status to 0.

 

Hope this helps: feel free to ask for clarifications on my above method.

[deepson2]

thanks a lot Masna!! 

 

I was trying this since last two days. but the small problem is there.

ok, in database i added two columns

 

last_access  status

meera            1

john              1

 

i firstly logged in as meera so last_access column got updated like this 2009-05-22 11:21:17(though today is 23 i dont know why its showing 22th ??) then i logged out as meera still my status didnt changed to 1 to 0??

 

then i logged in as john then again last_access is got updated like this 2009-05-22 06:54:40 and status is with 1.

 

now i am showing both the users online and others offline( at least. yestreday it was showing everyone's online)

 

so know could you please tell me what kind of changes i could do than whoever is left before 15 minute also get updated with status 0 that means offline?

 

here is my code:

 

$result1            = $op->runsql("SELECT a.last_access FROM ".tbl_author." a WHERE a.id = '$userid'");
  if(mysql_num_rows($result1) > 0){
while($row1 = $op->select($resul1t)){

$last  =$row1['last_access'];
if ($last < time() - (60*15))
{  // 15 minutes cosideration

$result = $op->runsql("UPDATE ".tbl_author." SET status = 1 WHERE id ='".$userid."'");
}else{
$result = $op->runsql("UPDATE ".tbl_author." SET status = 0 WHERE id ='".$userid."'");
}

} }
if($blogdata->status=="1"){
// here i am fetching data. using $blogdata    = $blog->personaldata(); using this function
echo "online";
}else{
echo "offline";
} 
?>

please help me.

[redarrow]

it updating the last entry because a user got to enter there info for the database to update.

 

if you want it to update automatically then use ajax or cron.

[deepson2]

if you want it to update automatically then use ajax or cron.

 

how to do that?

[redarrow]

cron linux

 

http://kevin.vanzonneveld.net/techblog/article/schedule_tasks_on_linux_using_crontab/

 

cron windows

 

http://cronw.sourceforge.net/

 

 

ajax.

http://forums.digitalpoint.com/showthread.php?t=490517

 

 

also read this ok. (full example via a php freaks expert.)

http://www.phpfreaks.com/forums/index.php/topic,223367.0.html

[BK87]

use mysql...

 

mysql_query("update online_counter set stime=CURRENT_TIMESTAMP where username='Joe'");

thats to update... this has to execute each time a page loads to update user status...

 

when displaying user status cross check with db

 

$query=mysql_query("select username from online_counter where username='Joe' and stime > CURRENT_TIMESTAMP - interval 15 minute");

$row=mysql_fetch_array($query);

if($row["username"]!=NULL){ echo "user online"; } else{ echo "user offline"; }

 

 

*** not tested technically should work

 

[deepson2]

redarrow, i am not sure i ll try your method. because i am new to ajax. and it will be bit complicated for me then.

 

BK87, thanks for your reply. I want to do something like this. i would like to tell you what i am doing actually.

 

on login. php, i am updating my last_access column like this

 

$result = $op->runsql("UPDATE ".tbl_author." SET last_access = '".date('Y-m-d H:i:s')."' WHERE username='$username' AND password='".md5($passwd)."'");

 

profile.php.( where i want to show user(self logged in/ other) are online/offline

$result1            = $op->runsql("SELECT a.last_access FROM ".tbl_author." a WHERE a.id = '$userid'");
  if(mysql_num_rows($result1) > 0){
while($row1 = $op->select($resul1t)){

$last  =$row1['last_access'];
if ($last < time() - (60*15))
{  // 15 minutes cosideration

$result = $op->runsql("UPDATE ".tbl_author." SET status = 1 WHERE id ='".$userid."'");
}else{
$result = $op->runsql("UPDATE ".tbl_author." SET status = 0 WHERE id ='".$userid."'");
}

} }

 

and on the same page showing my data like this

<?$result            = $op->runsql("SELECT * from ".tbl_author." where id='$blogdata->author' and last_access > CURRENT_TIMESTAMP - interval 15 minute");
if(mysql_num_rows($result)>0){
echo "online";
}else{
echo "offline";
} ?>

 

so could you please tell me where to actually change your query with mine?

 

[Masna]

Don't do this:

 

$result1            = $op->runsql("SELECT a.last_access FROM ".tbl_author." a WHERE a.id = '$userid'");
  if(mysql_num_rows($result1) > 0){
while($row1 = $op->select($resul1t)){

$last  =$row1['last_access'];
if ($last < time() - (60*15))
{  // 15 minutes cosideration

$result = $op->runsql("UPDATE ".tbl_author." SET status = 1 WHERE id ='".$userid."'");
}else{
$result = $op->runsql("UPDATE ".tbl_author." SET status = 0 WHERE id ='".$userid."'");
}

} }

 

I know I suggested it to you, but I'm tired and I wasn't thinking clearly. The above script will put a very serious strain on your server and MySQL Database(s). Instead, simply check the time when a user views another user on profile.php (or anywhere that online status is shown for a particular user).

 

[deepson2]

Masna, even i was thinking its bit comlicated to change the status (0/1) everytime. so i was concentrating only my last_access column and trying to solve this issue.

 

so trying to do it simple way.

on login.php updating last_access column. and then on profile.php

applying this query.

$result            = $op->runsql("SELECT * from ".tbl_author." where id='$blogdata->author' and last_access > NOW() - interval 15 minute");
if(mysql_num_rows($result)>0){
echo "online";
}else{
echo "offline";
} 

 

it showing me offline for all the time(anyone who is logged in or not)

 

could you please tell me query. and how can i apply that here?

 

 

[kickstart]

Hi

 

Firstly what format is last_access?

 

Secondly echo out the SQL you have executed and chck that $blogdata->author has been substituted in correctly.

 

All the best

 

Keith

[deepson2]

 

Hello keith, 

Firstly what format is last_access?

 

my type for last_access column is datatime.

 

Secondly echo out the SQL you have executed and chck that $blogdata->author has been substituted in correctly.

 

SELECT * from author where id='11' and last_access > NOW() - interval 15 minute

offline

 

this result i am getting. and yes $blogdata->author is getting substituted properly every time.

 

please suggest me somthing. i am finding difficult to solve this.

 

[kickstart]

Hi

 

Can't see anything obvious, unless you have some kind of difference between the time in php and MySQL.

 

In you update try:-

 

$result = $op->runsql("UPDATE ".tbl_author." SET last_access = NOW() WHERE username='$username' AND password='".md5($passwd)."'");

 

Also try running SELECT * from author where id='11' and last_access > NOW() - interval 15 minute

offline in phpMyAdmin

 

All the best

 

Keith

[deepson2]

 

$result = $op->runsql("UPDATE ".tbl_author." SET last_access = NOW() WHERE username='$username' AND password='".md5($passwd)."'");

 

i tried this. yes row is getting updated.got this value (2009-05-23 16:45:29)

 

Also try running SELECT * from author where id='11' and last_access > NOW() - interval 15 minute

offline in phpMyAdmin

 

using this got full row or you can say same row. ??? ??? that means my query is not understanding what i exactly want to do right?

 

[code<?]$result            = $op->runsql("SELECT * from ".tbl_author." where id='$blogdata->author' ");
if(mysql_num_rows($result)>0){
while($row1 = $op->select($result)){
$last = $row1['last_access'];
//$time = (NOW()- intervel 15 minute);
if ($last < time() - (60*15)){
echo "online";
}else{
echo "offline";
} }}?>[/code]

tried this now got everyone is online(who's logged in and who are not even looged in)

any help?

[deepson2]

$result            = $op->runsql("SELECT * from ".tbl_author." where id='$blogdata->author' ");
if(mysql_num_rows($result)>0){
while($row1 = $op->select($result)){
$last = $row1['last_access'];
//$time = (NOW()- intervel 15 minute);
if ($last < time() - (60*15)){
echo "online";
}else{
echo "offline";
} }}?>

 

tried this now getting everyone is online. any help??

[kickstart]

Hi

 

You are using a php Date() function without specifying a format and subtracting that a MySQL time field and expecting to multiply the results by a value to get seconds. Neither are really what you want. If you want to do it that way you probably want to extract the time from the table as a unix time stamp, and format date() in the same way.

 

All the best

 

Keith

[MadTechie]

why not just do this

UPDATE ".tbl_author." SET status = if(UNIX_TIMESTAMP(last_access)+(60*15) > NOW(),1,0) WHERE username='$username' AND password='".md5($passwd)."'

[deepson2]

Hello MadTechie,

 

My last_access column type is datatime. Do i need to change it with timestamp,if i need to compair with it to unix timestamp?

 

well, I have tried your query.getting this error.

 

MySQL said: Documentation

#1265 - Data truncated for column 'status' at row 1

 

please tell me how can i get proper time difference between last_access to now( we can say 15 min form logged in)?

[deepson2]

I have changed my last_access column type from datetime to timestamp

then i didn't use ENUM for status(using simple varchar- just testing). and when i use this query.

UPDATE author SET status = if(UNIX_TIMESTAMP(last_access)+(60*15) < NOW(),1,0) WHERE id=9

 

its changing status of that particular column to 1. then its not updating it after 15 minute.

 

could anyone describe this if condition? because i have never used if condition in query.

[deepson2]

I am sorry to putting lots of post here( couldn't help it modify is just goes away after few min. ) so please bare me.

 

ok, Now i am completely understanding that where the problem lies.

my query and php is not understanding the time difference. the value which is stored in the database is something like this

2009-05-25 14:56:29

 

so how php code ever will knows what is $time =time() -(60*15) ??

when i tried this $time =NOW() -(60*15) giving me error which says

Fatal error: Call to undefined function NOW() in

 

so my point is how can i checked my  15 minutes since lagged in with php??

here is what i trying.

$time = time() - (60*15);
echo NOW();

$result = $op->runsql("UPDATE ".tbl_author." SET last_access = NOW()  WHERE id='$userid'");
if(mysql_num_rows($result)>0){
while($row1 = $op->select($result)){

$last = $row1['last_access'];

if ($last > $time){
$result = $op->runsql("UPDATE ".tbl_author." SET status =0 WHERE id='$userid'");
}else{

$result = $op->runsql("UPDATE ".tbl_author." SET status =1 WHERE id='$userid'");

}
}}

please-please help me. i am tired now. but couldn't able to find out the solution. please someone help me here.

[kickstart]

Hi

 

This will set the status as you want.

 

UPDATE author SET status = if(UNIX_TIMESTAMP(last_access)+(60*15) < UNIX_TIMESTAMP(),1,0) WHERE id=9

 

However not sure it is a worthwhile step.

 

From your original post you just want to know who is still online from who has done something in the last 15 mins.

 

As such in a common routine put the following, so that every time anyone dones something the time is updated.

 

$sSqlSec = "Update ".tbl_author." Set last_access = NOW() where id='$userid' and TIMESTAMPDIFF(MINUTE, last_access,NOW()) <= 15";
$resultSec = mysql_query($sSqlSec,$conn) or die(mysql_error());
if (mysql_affected_rows() < 1)
{
header('Location:../Logout.php'); // Or do something else should they not have done anything in the last 15 mins
exit();
}

 

Then to check who is still logged in to give a list you would just have something like:-

 

$result = $op->runsql("SELECT * FROM ".tbl_author." WHERE TIMESTAMPDIFF(MINUTE, last_access,NOW()) <= 15";");
while($row1 = $op->select($result))
{
echo $row1['UserName']."<br />";
}

 

All the best

 

Keith

[deepson2]

Thanks a lot for your reply keith,

 

Its all going over to my head. I have discussed this with my senior. he told me. don't go into too complex method.

 

1) once user loggs in show him/her as online till they wont do log out.

 

2) In my logout.php i am destroying my session. so using that can we make it simple to show that user is offline.

 

I am sorry i have tried your code as well but still the problem is same. could you please tell me this module using sessions. without any extra column(last_access,status)?

 

please help me.

[kickstart]

Hi

 

Don't think others session variables are available to someone logged in. Hence you cannot check them to see who else is logged in.

 

As such you do need to use a column on a table. That column could be a BOOL set to 1 when they log in and 0 when they log out.

 

However if you do so you may as well use the code above and just add a datetime column rather than a status column.

 

To explain it the common section of code just updates a persons record when they do anything as long as it is not more than 15 minutes since they last did anything. Idea is that if they are idle for too long then you just redirect them elsewhere and force them to log in again.

 

The second part is just getting a list of all users who last did anything within the past 15 minutes.

 

All the best

 

Keith

[MadTechie]

Last_access should be

`last_access` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

then you should let SQL update it, don't update from PHP time,

when the record is created or updated the time will change the following code will change the status to 1 or 0

if(UNIX_TIMESTAMP(last_access)+(60*15) > NOW(),1,0)

 

if(contition,true,false)

so

if UNIX_TIMESTAMP(last_access)+(60*15) > NOW()

is true return 1

else return 0

 

 

why not just do this

UPDATE ".tbl_author." SET status = if(UNIX_TIMESTAMP(last_access)+(60*15) > NOW(),1,0) WHERE username='$username' AND password='".md5($passwd)."'

[deepson2]

Thanks MadTechie for your reply.

 

following is the query working for me.

SELECT id
     , visitor_id
     , last_access
     , CASE WHEN TIMESTAMPDIFF( MINUTE
                              , last_access
                              , CURRENT_TIMESTAMP ) <=15
            THEN 'online'
            ELSE 'offline' END AS status
  FROM author where id= '$userid'

so i can check this 15 minutes and than set status offline once its greater than 15 minutes from now.

 

but,(I thought this online/offline is the most ever simple thing to do. but its not )

 

well following problems are remaining. or they made me to thing which way(wrong/right) i am going.

) the second condition would be what if user left that page(probably logout ) before 15 minutes so how can i update my status column according to that?

 

2)what if user stayed for more than 15 minutes(lets say 2 hours) so did he need to do log in again and again?

 

3) As php freaks shows the logged in users till they themself wont do the log out/close the browser.(though choices are there)

 

the solved issue is done with query. so can anyone help me here for how to solve these 2 conditions?

 

thanks in advance.