Solved

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

Posted on 2009-03-31
17
1,562 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Complex MySQL Query 2 33
Currency in SQL? 2 31
Help With Simple Database Design 7 50
ability to create nested list on the UI using PHP, HTML, Javascript 7 45
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

830 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