Solved

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

Posted on 2009-03-31
17
1,414 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 36

Expert Comment

by:Loganathan Natarajan
Comment Utility
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
Comment Utility
@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
Comment Utility
Can you post your code please?
0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
Comment Utility
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
Comment Utility
also, replace user id?

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

Open in new window

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:kentcommunications
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
i hope you have "timers" as date time data type in "adminusers" table?

please check this ..
0
 

Author Comment

by:kentcommunications
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
glad to help!!! thanks for the point!
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
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 …

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now