Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I track a user login time in php/mysql?

Posted on 2009-03-31
17
Medium Priority
?
1,856 Views
Last Modified: 2013-12-12
I've created a login system in php linked to a mysql database. I need to know what time the user logged in or out (the exact time is not important, I just need to know approximately when they were logged in), whichever is easier to achieve.

Is this done through php on the login page, or is this done through MySQL?
0
Comment
Question by:kentcommunications
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 5
17 Comments
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 24029063
You need to have another date/time field in mysql database table... and through php script ... get the date and time when ever they login and logout...

just store it and display it...


0
 

Author Comment

by:kentcommunications
ID: 24029120
I have an empty field in the users table to save the date/time in, I just need help setting up that piece of code that acquires and posts the information.
0
 
LVL 11

Expert Comment

by:spoxox
ID: 24029154
One problem with this architecture  is the abandoned session, which won't give you a log out time.

To improve your results, you can capture the session log in time, and for each page visit, update a  second value with the current time. This will fail to include time spent on the last page in an abandoned session, but includes everything else. (The total time spent is the difference between the two times.)

This can be done at the server (MySQL is good for this!), or perhaps using browser cookies. If you want to do it on the client, the same data would be stored in the cookie (login time, and time at each page access - or, track sessionTime ( = currentTime - loginTime) ). You can display for each user their time spent on your site by showing this value - but if you want to track it, you'll want it on the server.
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 24029187
something like this,

whenever user login + logout you have to update it

$login_date = date("Y-m-d h:i:s");
 
$sql = "UPDATE ".$tbl_name." SET lastLogin='".$login_date()"' WHERE userid=username";
 
$result = mysql_query($sql); 

Open in new window

0
 

Author Comment

by:kentcommunications
ID: 24029343
@logudotcom

Thank you for the code. I'm relatively new to php, so please bear with me.
When I try to insert the code on my login page, I receive this error

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in login.php on line 73

I'm changed the table/field names to properly reflect the way my tables are set up.
0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 24030128
Can you post your code please?
0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 24030143
it might be,

$sql = "UPDATE ".$tbl_name." SET lastLogin='".$login_date."' WHERE userid=username";

Open in new window

0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 24030159
also, replace user id?

$sql = "UPDATE ".$tbl_name." SET lastLogin='".$login_date()"' WHERE userid=username";

Open in new window

0
 

Author Comment

by:kentcommunications
ID: 24030237
Tried the new code, now all it does it time out trying to connect to localhost. As I said, I'm new to php/mysql, a lot of this is Dreamweaver produced code that I've edited.

Table name: adminusers
Primary key: user_id
Field to record login times: timers
<?php require_once('../Connections/kent.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
 
  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
 
  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
?>
<?php
// *** Validate request to login to this site.
if (!isset($_SESSION)) {
  session_start();
}
 
$loginFormAction = $_SERVER['PHP_SELF'];
if (isset($_GET['accesscheck'])) {
  $_SESSION['PrevUrl'] = $_GET['accesscheck'];
}
 
if (isset($_POST['username'])) {
  $loginUsername=$_POST['username'];
  $password=$_POST['password'];
  $MM_fldUserAuthorization = "admin";
  $MM_redirectLoginSuccess = "user.php";
  $MM_redirectLoginFailed = "loginfailed.php";
  $MM_redirecttoReferrer = false;
  mysql_select_db($database_kent, $kent);
  	
  $LoginRS__query=sprintf("SELECT username, password, admin FROM adminusers WHERE username=%s AND password=%s",
  GetSQLValueString($loginUsername, "text"), GetSQLValueString($password, "text")); 
   
  $LoginRS = mysql_query($LoginRS__query, $kent) or die(mysql_error());
  $loginFoundUser = mysql_num_rows($LoginRS);
  if ($loginFoundUser) {
    
    $loginStrGroup  = mysql_result($LoginRS,0,'admin');
    
    //declare two session variables and assign them
    $_SESSION['MM_Username'] = $loginUsername;
    $_SESSION['MM_UserGroup'] = $loginStrGroup;	      
 
    if (isset($_SESSION['PrevUrl']) && false) {
      $MM_redirectLoginSuccess = $_SESSION['PrevUrl'];	
    }
    header("Location: " . $MM_redirectLoginSuccess );
  }
  else {
    header("Location: ". $MM_redirectLoginFailed );
  }
}
$login_date = date("Y-m-d h:i:s"); 
$sql = "UPDATE ".$LoginRS_query." SET timers='".$login_date."' WHERE user_id=username"; 
$result = mysql_query($sql); 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
 
<body>
<form id="form1" name="form1" method="POST" action="<?php echo $loginFormAction; ?>"><table width="400" border="0" cellspacing="0" cellpadding="2">
  <tr>
    <td width="82"><label for="username">Username:</label></td>
    <td width="310"><input type="text" name="username" id="username" /></td>
  </tr>
  <tr>
    <td><label for="password">Password:</label></td>
    <td><input type="password" name="password" id="password" /></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td><label for="submit"></label>
      <input type="submit" name="submit" id="submit" value="Submit" />
      <input type="hidden" name="user_id" id="user_id" /></td>
  </tr>
</table>
 
</form>
 
</body>
</html>

Open in new window

0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 24030447
try now,

<?php require_once('../Connections/kent.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
 
  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
 
  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
?>
<?php
// *** Validate request to login to this site.
if (!isset($_SESSION)) {
  session_start();
}
 
$loginFormAction = $_SERVER['PHP_SELF'];
if (isset($_GET['accesscheck'])) {
  $_SESSION['PrevUrl'] = $_GET['accesscheck'];
}
 
if (isset($_POST['username'])) {
  $loginUsername=$_POST['username'];
  $password=$_POST['password'];
  $MM_fldUserAuthorization = "admin";
  $MM_redirectLoginSuccess = "user.php";
  $MM_redirectLoginFailed = "loginfailed.php";
  $MM_redirecttoReferrer = false;
  mysql_select_db($database_kent, $kent);
        
  $LoginRS__query=sprintf("SELECT username, password, admin FROM adminusers WHERE username=%s AND password=%s",
  GetSQLValueString($loginUsername, "text"), GetSQLValueString($password, "text")); 
   
  $LoginRS = mysql_query($LoginRS__query, $kent) or die(mysql_error());
  $loginFoundUser = mysql_num_rows($LoginRS);
  if ($loginFoundUser) {
    
    $loginStrGroup  = mysql_result($LoginRS,0,'admin');
    
    //declare two session variables and assign them
    $_SESSION['MM_Username'] = $loginUsername;
    $_SESSION['MM_UserGroup'] = $loginStrGroup;       
 
 	/* Update the login date and time*/	
	$login_date = date("Y-m-d h:i:s"); 
	$sql = "UPDATE `adminusers` SET `timers`='".$login_date."' WHERE username=%s"; 
	$result = mysql_query($sql) or die(mysql_error());
	
	
    if (isset($_SESSION['PrevUrl']) && false) {
      $MM_redirectLoginSuccess = $_SESSION['PrevUrl'];  
    }
    header("Location: " . $MM_redirectLoginSuccess );
  }
  else {
    header("Location: ". $MM_redirectLoginFailed );
  }
}
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
 
<body>
<form id="form1" name="form1" method="POST" action="<?php echo $loginFormAction; ?>"><table width="400" border="0" cellspacing="0" cellpadding="2">
  <tr>
    <td width="82"><label for="username">Username:</label></td>
    <td width="310"><input type="text" name="username" id="username" /></td>
  </tr>
  <tr>
    <td><label for="password">Password:</label></td>
    <td><input type="password" name="password" id="password" /></td>
  </tr>
  <tr>
    <td> </td>
    <td><label for="submit"></label>
      <input type="submit" name="submit" id="submit" value="Submit" />
      <input type="hidden" name="user_id" id="user_id" /></td>
  </tr>
</table>
 
</form>
 
</body>
</html>

Open in new window

0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 24030449
I have corrected like this,


/* Update the login date and time*/	
	$login_date = date("Y-m-d h:i:s"); 
	$sql = "UPDATE `adminusers` SET `timers`='".$login_date."' WHERE username=%s"; 
	$result = mysql_query($sql) or die(mysql_error());

Open in new window

0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 24030451
i hope you have "timers" as date time data type in "adminusers" table?

please check this ..
0
 

Author Comment

by:kentcommunications
ID: 24031074
The problem with it timing out was an issue with the connection, which I fixed.
The page is loading fine, but when I login I get this error message:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line 1"

The URL still says login.php so it's not an error on the next page. I tried logging in with multiple users, same results. I also checked the database and "timers" did not change from all zeros. Yes, it is set as "DATETIME"
0
 
LVL 36

Accepted Solution

by:
Loganathan Natarajan earned 2000 total points
ID: 24031133
OK, try now,


<?php require_once('../Connections/kent.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
 
  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
 
  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
?>
<?php
// *** Validate request to login to this site.
if (!isset($_SESSION)) {
  session_start();
}
 
$loginFormAction = $_SERVER['PHP_SELF'];
if (isset($_GET['accesscheck'])) {
  $_SESSION['PrevUrl'] = $_GET['accesscheck'];
}
 
if (isset($_POST['username'])) {
  $loginUsername=$_POST['username'];
  $password=$_POST['password'];
  $MM_fldUserAuthorization = "admin";
  $MM_redirectLoginSuccess = "user.php";
  $MM_redirectLoginFailed = "loginfailed.php";
  $MM_redirecttoReferrer = false;
  mysql_select_db($database_kent, $kent);
        
  $LoginRS__query=sprintf("SELECT username, password, admin FROM adminusers WHERE username=%s AND password=%s",
  GetSQLValueString($loginUsername, "text"), GetSQLValueString($password, "text")); 
   
  $LoginRS = mysql_query($LoginRS__query, $kent) or die(mysql_error());
  $loginFoundUser = mysql_num_rows($LoginRS);
  if ($loginFoundUser) {
    
    $loginStrGroup  = mysql_result($LoginRS,0,'admin');
    
    //declare two session variables and assign them
    $_SESSION['MM_Username'] = $loginUsername;
    $_SESSION['MM_UserGroup'] = $loginStrGroup;       
 
 	/* Update the login date and time*/	
	$login_date = date("Y-m-d h:i:s"); 
	//$sql = "UPDATE `adminusers` SET `timers`='".$login_date."' WHERE username=%s"; 
	$sql=sprintf("UPDATE adminusers  SET `timers`='".$login_date."' WHERE username=%s ",
  GetSQLValueString($loginUsername, "text")); 
  
	$result = mysql_query($sql) or die(mysql_error());
	
	
    if (isset($_SESSION['PrevUrl']) && false) {
      $MM_redirectLoginSuccess = $_SESSION['PrevUrl'];  
    }
    header("Location: " . $MM_redirectLoginSuccess );
  }
  else {
    header("Location: ". $MM_redirectLoginFailed );
  }
}
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
 
<body>
<form id="form1" name="form1" method="POST" action="<?php echo $loginFormAction; ?>"><table width="400" border="0" cellspacing="0" cellpadding="2">
  <tr>
    <td width="82"><label for="username">Username:</label></td>
    <td width="310"><input type="text" name="username" id="username" /></td>
  </tr>
  <tr>
    <td><label for="password">Password:</label></td>
    <td><input type="password" name="password" id="password" /></td>
  </tr>
  <tr>
    <td> </td>
    <td><label for="submit"></label>
      <input type="submit" name="submit" id="submit" value="Submit" />
      <input type="hidden" name="user_id" id="user_id" /></td>
  </tr>
</table>
 
</form>
 
</body>
</html>

Open in new window

0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 24031139
I made change here,


$sql=sprintf("UPDATE adminusers  SET `timers`='".$login_date."' WHERE username=%s ",
  GetSQLValueString($loginUsername, "text")); 

Open in new window

0
 

Author Closing Comment

by:kentcommunications
ID: 31564834
Works perfectly now, thank you for all your help!
Also, thank you for showing where you made changes. That makes it easier for me to learn and adapt in the future.
0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 24031539
glad to help!!! thanks for the point!
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question