I store sessions in the DB using CDbSession. I need the "users online" widget. How can I create one?
Any ideas or maybe someone already implemented it?
I store sessions in the DB using CDbSession. I need the "users online" widget. How can I create one?
Any ideas or maybe someone already implemented it?
The "users online" widget can be developed easily by returning a count of the user sessions of the db session table.
If you also want to know the list of online user names, you will need to add one more column to the db session table to store user ID.
Then, you need to extend CWebUser by overriding its changeIdentity() method to update the db session table with the user ID.
check the extensions section.
I’m pretty sure I’ve seen an extension there that does that
I saw that, but I don’t need this. I need list of users that are online, their IDs.
Hi, allmighty Yii users 
I just tried to implement this feature.
First i’ve added a field “user_id” to session table.
Second i extended CWebUser, modifying changeIdentity method with following code
protected function changeIdentity($id,$name,$states) {
parent::changeIdentity($id, $name, $states);
$db = $this->getDbConnection();
$db->setActive(true);
$sql = "UPDATE sessions SET user_id = $id";
$db->createCommand($sql)->execute();
}
It works and after authorization i got user id in the field user_id. But when i quit with this user, session data clears, but user_id still got user id number, which should be better NULL or smth…
Please provide me with some ideas on this 
I found that changeIdentity calls only on login.
So i need to modify method which response for logout action.
I got something like this in my descendant class.
public function logout($destroySession=true) {
if (!$destroySession) {
$db = $this->getDbConnection();
$db->setActive(true);
$sql = "UPDATE sessions SET user_id = NULL";
$db->createCommand($sql)->execute();
}
}
I think Yii one of the masterpiece of PHP code and the great framework. Thanks Qiang and the team.
Previous posts contains bugs. Here is the new version ))
/**
* Stores an user id in session table
*
*/
class CWebUserId extends CWebUser {
private $_db;
public $connectionID;
protected function changeIdentity($id,$name,$states)
{
parent::changeIdentity($id,$name, $states);
$db = $this->getDbConnection();
$db->setActive(true);
$sessionId = md5(app()->session->sessionId);
$sql = "UPDATE sessions SET user_id = $id WHERE id = '$sessionId'";
$db->createCommand($sql)->execute();
}
protected function getDbConnection()
{
if($this->_db!==null)
return $this->_db;
else if(($id=$this->connectionID)!==null)
{
if(($this->_db=Yii::app()->getComponent($id)) instanceof CDbConnection)
return $this->_db;
else
throw new CException(Yii::t('yii','CDbHttpSession.connectionID "{id}" is invalid. Please make sure it refers to the ID of a CDbConnection application component.',
array('{id}'=>$id)));
}
else
{
$dbFile=Yii::app()->getRuntimePath().DIRECTORY_SEPARATOR.'session-'.Yii::getVersion().'.db';
return $this->_db=new CDbConnection('sqlite:'.$dbFile);
}
}
public function logout($destroySession=true)
{
parent::logout($destroySession);
if (!$destroySession) {
$db = $this->getDbConnection();
$db->setActive(true);
$sessionId = md5(app()->session->sessionId);
$sql = "UPDATE sessions SET user_id = NULL WHERE id = '$sessionId'";
$db->createCommand($sql)->execute();
}
}
Setting the user_id to NULL on logout is not a good idea, IMO. You’re destroying any useful information from the session table in terms of audit trails (who logged in when). Better to define a sessionEnded field, which you populate on logout. Then when selecting which users are online, check if sessionEnded is null or not. That way you still know who the session was for, when they logged out, and if they are logged in or out at present.
So I spent a bit of time playing with the session component, and discovered that it only stores current sessions, so your setting of the user_id to null on logout is correct. I’ve found I don’t need to do this explicitly though.
I’ve subclassed the CDbHttpSession class and rewritten the writeSession method like this:
class AuditHttpSession extends CDbHttpSession
{
public function getRemoteAddr()
{
if(getenv("HTTP_CLIENT_IP"))
$ip = getenv("HTTP_CLIENT_IP");
else if(getenv("HTTP_X_FORWARDED_FOR"))
$ip = getenv("HTTP_X_FORWARDED_FOR");
else if(getenv("REMOTE_ADDR"))
$ip = getenv("REMOTE_ADDR");
else
$ip = "UNKNOWN";
return $ip;
}
public function writeSession($id, $data)
{
parent::writeSession($id, $data);
$id = md5($id);
$db = $this->getDbConnection();
$vhost = SiteController::getVHost();
$vhost_id = empty($vhost) ? 'null' : $vhost->id;
$user_id = empty(user()->id) ? 'null' : user()->id;
$remote_addr = self::getRemoteAddr();
$sql = "UPDATE ".$this->sessionTableName." SET vhost_id = ".$vhost_id.", user_id = ".$user_id.", remote_addr = '".$_SERVER['REMOTE_ADDR']."', updated_at = now() WHERE id = '".$id."'";
$command = $db->createCommand($sql);
$command->execute();
return true;
}
}
I’ve got a multiple virtual host setup, so I capture the specific host as well (ignore that part 
).
When the user logs out, the identity carried by the CWebUser component becomes Guest and the getId() method returns NULL. Because the session has changed, it is written and this method is called which sets the proper value of NULL.
Next up is a session_log table, which will store state changes to this information. Eish, the days aren’t long enough. :->
Thanks for the contribution to the topic.
offtopic:
Can you please describe
SiteController::getVHost() method ?
Changed names but here it is:
public function getSite()
{
static $site;
if(isset($site)) {
return $site;
} else {
$session = Yii::app()->session;
if($session->contains('siteID')) {
$id = $session->itemAt('siteID');
$site = Site::model()->findByPk($id);
return $site;
} else {
$session = Yii::app()->session;
$uri = Yii::app()->request->hostInfo;
$site = Site::model()->findByAttributes(compact('uri'));
if(!empty($site)) {
$session->add('siteID', $site->id);
return $site;
}
throw new CHttpException(404, 'Unable to find a site hosted here with that hostname');
}
}
}
It looks up the site by the URI supplied by hostInfo (which does the hard work of resolving protocol, HTTP_HOST, etc).
The Site class is an AR model where the site information is stored.
What I did, since I’m only interested in knowing the list of currently logged in users, is this:
public function logout($destroySession=true)
{
parent::logout($destroySession);
if (!$destroySession) {
$sessionId = Yii::app()->session->sessionId;
$sessionTable = Yii::app()->session->sessionTableName;
$sql = "UPDATE {$sessionTable} SET `user_id` = NULL WHERE `id` = '{$sessionId}'";
Yii::app()->db->createCommand($sql)->execute();
}
}
protected function changeIdentity($id,$name,$states)
{
parent::changeIdentity($id,$name, $states);
$sessionId = Yii::app()->session->sessionId;
$sessionTable = Yii::app()->session->sessionTableName;
$sql = "UPDATE {$sessionTable} SET `user_id` = '$id' WHERE `id` = '{$sessionId}'";
Yii::app()->db->createCommand($sql)->execute();
}
Does not need getDbConnection as you can get that from Yii::app.
I then use this dirty query in my base controller:
class Controller extends CController
{
protected function beforeAction($action)
{
$users = Yii::app()->db->createCommand()
->select('u.id, username')
->from('{{usergroups_user}} u')
->join('{{session}} p', 'u.id=p.user_id')
->where('u.id=p.user_id AND p.data NOT LIKE ""')
->queryAll();
$this->users_online = $users;
return true;
}
Scratch the last bit - it’s useless!! 
I realized that a user will be regarded as online for about a month if they are using the ‘Remember me’ feature. 
So I ended up using a different strategy. 
Migration:
$this->createTable('{{visitor}}', array(
'id' => 'pk',
'user_id' => 'integer NOT NULL',
'last_activity' => 'timestamp',
), "ENGINE=InnoDB DEFAULT CHARSET=utf8");
$this->createIndex('fk_user_user_id', '{{visitor}}', 'user_id');
$this->addForeignKey('fk_user_user_id', '{{visitor}}', 'user_id', '{{user}}', 'id');
Creates a table named ‘visitor’ with id, user_id and last_activity timestamp field.
Base controller:
public $users_online = null;
public $visitorTableName = '{{visitor}}';
protected function beforeAction($action)
{
if(isset(Yii::app()->user->id)) {
$user_id = Yii::app()->user->id;
//TODO: Don't do this every time the app runs??
$sql = "SELECT user_id FROM {$this->visitorTableName} WHERE user_id=:user_id";
if (Yii::app()->db->createCommand($sql)->bindValue(':user_id', $user_id)->queryScalar() === false)
$sql = "INSERT INTO {$this->visitorTableName} (user_id, last_activity) VALUES (:user_id, :last_activity)";
else
$sql = "UPDATE {$this->visitorTableName} SET last_activity=:last_activity WHERE user_id=:user_id";
Yii::app()->db->createCommand($sql)->bindValue(':user_id', $user_id)->bindValue(':last_activity', date('Y-m-d H:i:s'))->execute();
}
$users = Yii::app()->db->createCommand()
->select('u.id, username, TIMESTAMPDIFF(MINUTE, last_activity, UTC_TIMESTAMP())')
->from('{{usergroups_user}} u')
->join("{$this->visitorTableName} v", 'u.id=v.user_id')
->where('TIMESTAMPDIFF(MINUTE, last_activity, UTC_TIMESTAMP()) < 5')
->queryAll();
$this->users_online = $users;
return true;
}
If Yii::app()->user->id is not null, update the last_activity timestamp for that user.
Then - regardless of the user being null or not, fetch the list of users which have been active for less than 5 minutes and assign it to $this->users_online.
In a clip/widget/view/controller:
<h2>Users Online</h2>
<?php
if (isset($this->users_online)) {
foreach ($this->users_online as $user)
if (isset($user)) {
echo '<b>' . ucfirst($user['username']) . '</b> <small>(';
echo 'Idle for ' . $user['TIMESTAMPDIFF(MINUTE, last_activity, UTC_TIMESTAMP())'] . ' minutes)</small><br/>';
}
} else {
echo 'none';
}
?>
It should echo something like this:
If the user has been idle for more than 5 minutes, he/she is removed from the list of active users.
Hi,
that’s very true
Here’s another idea.
You can create pinging mechanism: create with javascript invisible image tag and change its src every 30 seconds, therefore requesting url with GET method, change it a couple of times (eg. 30 times, that will give user 15 minutes to be on one page, maybe s/he is reading?) and after that you can safely mark user offline. So, in worst case, you end up making about 30 requests and 1 db query
Implementation:
<script>
$(document).ready(function(){
var repeat=30;
var timeout=1000;
function getUrl(r){
return 'http://example.com/user/ping?repeat=' + r;
}
function ping(){
if(repeat>=0){
$img.attr('src', getUrl(repeat));
repeat--;
setTimeout(ping, timeout);
}
}
var $img=$('<img/>')
.css({display: 'none', height: 0, width: 0})
.appendTo('body');
setTimeout(ping, timeout);
});
</script>
on server side:
// UserController.php
public function actionPing($repeat)
{
if($repeat==0)
Yii::app()->getUser()->setUserOffline();
}
// WebUser.php
public function setUserOffline()
{
$db=Yii::app()->getDb();
$db->createCommand()->update('user', array('status'=>'0'), 'id=?', array($this->getId()));
}
another idea is to use xmpp, but for that jabber server is required
Cheers
Why do you use md5() function to get the sessionId?
If I use it, the sessionId get incorrect. I couldn’t use md5()
Why do you use md5() function to get the $id?
If I use it, the sessionId get incorrect. I couldn’t use md5()
hi guys i am new to this yii…i am trying to develop a chat application ,In that i wan to show number of online users and their name.i have tried a lot but i did’t get that…Thanks to Mr jacmoe…he gave a clear explanation…Thank u guys!!! Now i have another query how to show desktop notification in my application whenever a new message comes.??Any one help me.