I have Developed a Database using MySQL and PHP.
Now there are 60+ users who are using this DB.
I want to make a system which tells me about Active and inactive users.
Please guide me.
My memebers table is like

--
-- Table structure for table `members`
--

CREATE TABLE IF NOT EXISTS `members` (
  `MemId` int(5) NOT NULL AUTO_INCREMENT,
  `UserName` varchar(25) NOT NULL,
  `Password` varchar(20) NOT NULL,
  `Type` varchar(20) NOT NULL,
  PRIMARY KEY (`MemId`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=61 ;

My checklogin.php page

<?php
ob_start();
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="onm"; // Database name 
$tbl_name="members"; // Table name 

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// Define $myusername and $mypassword 
$myusername=$_POST['myusername']; 
$mypassword=$_POST['mypassword']; 

// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql);

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row

if($count==1){
// Register $myusername, $mypassword and redirect to file "login_success.php"
session_register("myusername");
session_register("mypassword"); 
header("location:login_success.php");
}
else {
header("location:sims_login_error.php");
}

ob_end_flush();
?>

and my loginsuccess.php page

<? 
session_start();
if(!session_is_registered(myusername)){
header("location:onm.php");
}
?>
<?
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="onm"; // Database name 
$tbl_name="members"; // Table name 

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");


$result = mysql_query("SELECT type FROM $tbl_name WHERE username='$_SESSION[myusername]'");

while($row = mysql_fetch_array($result))
  {
    if($row['type'] == 2)
 {
    echo $_SESSION[myusername];
  
  }
  else if($row['type'] == 'admin')
  {
  include "admin.php";
  }
   
 else 
   {
  //include "ll.php";
  }
  }
  


?>

table columns, last_login login_number
add to your php login script

update members set last_login=now() where user=$user
update members set login_number=login_number+1 where user=$user

code sample only, not checked, (laptop in public wifi, no notes)

once the timestamp is set then you can use php sql datetime functions to find any users who havent/haved in within any timespan

Or
create another table for visits keyed to the userid

Here is one idea of how you can get this done.
You can add a `LastRequest` field to your `members` table that will hold the timestamp of the last request made by the member. You can set it up as an integer field and store the current time in second (the return value of the PHP time() function). You would need to update this field for the currently logged in user on every request. As long as you know the `MemId` of the currently logged in user from the session, you can easily do it. At the same time you can invalidate the sessions of users whose sessions expired--that is, they made no requests to the server for, say, 20 minutes. Then, during any request, any member that has LastRequest set to a valid (non-null) value is logged in, others--logged out.

I would also suggest that you call session_start() in the checklogin.php page before you put any data into the session, and do not call it in your loginsuccess.php page.

<?php
// checklogin.php

if ($count == 1) {
    session_start();
    $row = mysql_fetch_row($result);
    $_SESSION['memberid'] = $row[0]; //MemId field
    header('Location: login_success.php');
}
else {
    header('Location: sims_login_error.php');
}
?>

The following code should run on every page that expects a logged in user (it could be a function in some include):

$curtime = time();
$oldtime = $curtime - (20 * 60); //20 minutes ago

// Invalidate old sessions.
// (If LastRequest was declared as NOT NULL, then it can be set to 0.)
$sql = "UPDATE `members` SET `LastRequest` = NULL WHERE `LastRequest` < $oldtime;";
mysql_query($sql);

// Update current session.
$memId = $_SESSION['memberid'];
if ($memId) {
    $sql = "UPDATE `members` SET `LastRequest` = $time WHERE `MemId` = $memId;";
    mysql_query($sql);
}

Using this setup, you can also check whether users session expired, and redirect them to a session expired page and ask them to re-login.

Hey,
I didn't really go through your codes.This will is just a logic .

1. Create an extra column named "Active/Status" in the user table.
2.When the user is successfully logged in update the column with "1" and with "0",when the user log outs.
3.Make a page to list all the users with an order by of the status column.You will get the active users first and inactive users last.
This will give you the basic settings.
4. In the next stage check whether the user session exists in all the pages and if not exists just do the update by making the status as "0".

3 solutions....
Now Which solution is better
I think status with 0 or 1 is best but it does not show login time ????

table columns, last_login login_number
add to your php login script

update members set last_login=now() where user=$user
update members set login_number=login_number+1 where user=$user

code sample only, not checked, (laptop in public wifi, no notes)

once the timestamp is set then you can use php sql datetime functions to find any users who havent/haved in within any timespan

Or
create another table for visits keyed to the userid

In which page I have to use this code
checklogin.php or loginsuccess.php
User session registered in checklogin.php

3 solutions....
Now Which solution is better
I think status with 0 or 1 is best but it does not show login time ????

Dont worry same way just add another column named "loggedInTime" and update the column whenever the user got logged in.

Keep in mind that not all users will hit the logout button, so the code that removes a user from the list of logged in users may not run for some user sessions.

the usual thing is a routine in the script that logs pages, keeps note of logged in users movements, that timesout other sessions if inactive for (preset time),

But where I have to put the code of Update either in checklogin or loginsuccess

Member Avatar for rajarajan2017

Logic:

1. Create a new field in your users table (fieldname: online)
2. Update the field with 1 if a user login and 0 if a user logged out
3. So you can come to know how many users are online and offline.
4. Check the process when a user login and logout.

I just give the suggestions.

Logic:

1. Create a new field in your users table (fieldname: online)
2. Update the field with 1 if a user login and 0 if a user logged out
3. So you can come to know how many users are online and offline.
4. Check the process when a user login and logout.

I just give the suggestions.

But on which page I will write Update code?

Is there a page which is called from all pages, some configuration or connection page. You may use that page to update user table. And I suggest to insert timestamp intead of flag.

the reply says it is not finished code,
it is a scrap of sql as a thought pattern, it would have to be properly setup in php mysql handling

the code that updates last_login login_count is_loggedin (and any other) should be in whichever file logs the user in, code for monitoring whether login is current has to be in EVERY page, so usually put it a menu include() file

Member Avatar for rajarajan2017

Try your code with login and logout buttons.

Is there a page which is called from all pages, some configuration or connection page. You may use that page to update user table. And I suggest to insert timestamp intead of flag.

yea there is a connection file config.php which is included in all pages. where I use Mysql Queries. which extra fields are required???

yea there is a connection file config.php which is included in all pages. where I use Mysql Queries. which extra fields are required???

But I am sooooooooooooo much confused ....
how I will get online user and what code I have to write on all pages??????

As almostbob suggested you may Use your menu file if you are having or any other file that is called in almost all pages to update user status.

I would prefer to do it in following way. I am not sure that it is the best way to do it or not. But it is just to start with.
1) create column in user master say last_accessed (datetime)
2) in you common file you may update user last_accessed column
suppose you store user name in session then you may write update query at the end of your file.

update usertable set last_accessed=current_timestamp() where userid='{$_SESSION['userid]}'

3) now you have time for each user in your usertable. now you may use this column for reporting, that depends on your requirements.

Are you getting?

As almostbob suggested you may Use your menu file if you are having or any other file that is called in almost all pages to update user status.

I would prefer to do it in following way. I am not sure that it is the best way to do it or not. But it is just to start with.
1) create column in user master say last_accessed (datetime)
2) in you common file you may update user last_accessed column
suppose you store user name in session then you may write update query at the end of your file.

update usertable set last_accessed=current_timestamp() where userid='{$_SESSION['userid]}'

3) now you have time for each user in your usertable. now you may use this column for reporting, that depends on your requirements.

Are you getting?

I got it . its great... but if time store like 03 - june - 2010 11:00 AM then how I get online users????

following query will give the username with minutes(page accessed by user before that much minutes). You may set one standard say 20 minutes. If minutes is less than 20 minutes then he/she is online if minutes is greater than 20 then user is offline.

select userid, TIMESTAMPDIFF(minute,last_accessed,current_timestamp) login_since_minutes
 from usertable

following query will give the username with minutes(page accessed by user before that much minutes). You may set one standard say 20 minutes. If minutes is less than 20 minutes then he/she is online if minutes is greater than 20 then user is offline.

select userid, TIMESTAMPDIFF(minute,last_accessed,current_timestamp) login_since_minutes
 from usertable

From where you write this

login_since_minutes

???

It is alias for timestampdiff(.....) column.
if your user table is like following

useriid, last_accesssed
urtrivedi, 2010-06-03 12:10:00
ayesha, 2010-06-03 11:40:00

now if you execute following query at say 12:30

select TIMESTAMPDIFF(second,last_accessed,current_timestamp) login_since_minutes from usertable

then execution will give

userid, login_since_minutes
urtrivedi, 20
ayesha, 50

means urtriedi visited some page before 20 minutes and ayesha visited some page before 50 minutes

It is alias for timestampdiff(.....) column.
if your user table is like following

useriid, last_accesssed
urtrivedi, 2010-06-03 12:10:00
ayesha, 2010-06-03 11:40:00

now if you execute following query at say 12:30

select TIMESTAMPDIFF(second,last_accessed,current_timestamp) login_since_minutes from usertable

then execution will give

userid, login_since_minutes
urtrivedi, 20
ayesha, 50

means urtriedi visited some page before 20 minutes and ayesha visited some page before 50 minutes

My this file is included in everypage where I use SQL

<?php

             $con = mysql_connect("localhost","root","");
				   if(!$con)
					 {
					 die('Could not connect:' . mysql_error());
					 }
				   mysql_select_db("onm", $con);


?>

and this below is used above almost every single page in my Software.

<? 
session_start();
if(!session_is_registered(myusername)){
header("location:index.php");
exit();
}
?>

now where it will serve best

you may write update code at the end of your fist database connection file.

do not Write code in every page.

you may write update code at the end of your fist database connection file.

do not Write code in every page.

I use this code and its working, I used NOW() Function...

<?php
session_start();
             $con = mysql_connect("localhost","root","");
				   if(!$con)
					 {
					 die('Could not connect:' . mysql_error());
					 }
				   mysql_select_db("onm", $con);
				   
		$qry=mysql_query("UPDATE members SET last_accessed=NOW() WHERE UserName='$_SESSION[myusername]'");				   
				 

?>

I use this code and its working, I used NOW() Function...

<?php
session_start();
             $con = mysql_connect("localhost","root","");
				   if(!$con)
					 {
					 die('Could not connect:' . mysql_error());
					 }
				   mysql_select_db("onm", $con);
				   
		$qry=mysql_query("UPDATE members SET last_accessed=NOW() WHERE UserName='$_SESSION[myusername]'");				   
				 

?>

But it gives following error on other pages

Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at C:\xampp\htdocs\lims_show_north.php:10) in C:\xampp\htdocs\config.php on line 2

But it gives following error on other pages

Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at C:\xampp\htdocs\lims_show_north.php:10) in C:\xampp\htdocs\config.php on line 2

I remove this session_start(); from config.php and its working :cool:

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.