How to track login dates/times?
Moderator: General Moderators
-
sing2trees
- Forum Newbie
- Posts: 10
- Joined: Thu Feb 12, 2009 4:25 pm
How to track login dates/times?
Evening everyone,
I am slowly learning PHP and login scripts. On my site, I have a registration and login page/script using MySQL. All very simple!
However what would be really helpful is finding out when users last logged in. Plus if I could have on the index.php (once logged in) 'Welcome user name'.
I am not sure what to search on google tho in order to find scripts or tutorials to do this!
Could anyone give me some pointers?
If you need more info, let me know!
Thanks in advance!
Ben
I am slowly learning PHP and login scripts. On my site, I have a registration and login page/script using MySQL. All very simple!
However what would be really helpful is finding out when users last logged in. Plus if I could have on the index.php (once logged in) 'Welcome user name'.
I am not sure what to search on google tho in order to find scripts or tutorials to do this!
Could anyone give me some pointers?
If you need more info, let me know!
Thanks in advance!
Ben
Re: How to track login dates/times?
You need a place to store the last log-in time. That will be in the database table where all the other user information will be stored. Then, when the user logs in, you store the current date/time in that field.
For the welcome message you need the username. Then you simply echo or print it where you want the message to appear.
For the welcome message you need the username. Then you simply echo or print it where you want the message to appear.
Re: How to track login dates/times?
If you want to find out when the user last logged in, just add a "last_login_date" column to the user table, and when they log in.
If you want to log ALL access attempts, you'll need to create a new table for user log with columns for user id and date, then INSERT to it when the user logs in in a similar method to the UPDATE statement above.
As for displaying any of their account information, I assume you are doing a SELECT db statement to check the username and password. You just need to pull the columns you wish to display when you do this. If results are returned, that not only means that the credentials were valid, but you also have any information from their profile that you need.
Code: Select all
UPDATE users SET last_login_date=NOW() WHERE uid=$uidAs for displaying any of their account information, I assume you are doing a SELECT db statement to check the username and password. You just need to pull the columns you wish to display when you do this. If results are returned, that not only means that the credentials were valid, but you also have any information from their profile that you need.
Re: How to track login dates/times?
Create a new entry in your MySql database called Submitted, for example, and as Datatype use TIMESTAMP, default value CURRENT_TIMESTAMP, and NOT NULL and UNSIGNED. That's it. 
-
sing2trees
- Forum Newbie
- Posts: 10
- Joined: Thu Feb 12, 2009 4:25 pm
Re: How to track login dates/times?
Fantastic - thanks ever so much to everyone for their help!!
I will give it a try later and report back!
Thanks again
I will give it a try later and report back!
Thanks again
-
sing2trees
- Forum Newbie
- Posts: 10
- Joined: Thu Feb 12, 2009 4:25 pm
Re: How to track login dates/times?
[quote="sparrrow"]If you want to find out when the user last logged in, just add a "last_login_date" column to the user table, and when they log in.
If you want to log ALL access attempts, you'll need to create a new table for user log with columns for user id and date, then INSERT to it when the user logs in in a similar method to the UPDATE statement above.
Hi Sparrrow,
So with the code above, where do I insert it? Within the login script? I think I will just need a 'last login date'.
Thanks in advance!
Code: Select all
UPDATE users SET last_login_date=NOW() WHERE uid=$uidHi Sparrrow,
So with the code above, where do I insert it? Within the login script? I think I will just need a 'last login date'.
Thanks in advance!
Re: How to track login dates/times?
You should place the code in with your user validation script. After you have checked that they are a valid user and can be granted access, just update their last login date.
Code: Select all
$result=mysql_query("SELECT * FROM users WHERE uid='$uid' AND password='$password'");//validate user credentials (Hopefully you are using MD5() or some sort of password encryption)
if (mysql_num_rows($result)) {//If credentials matched a user in the db...
$row = mysql_fetch_assoc($result);//...Get user profile information...
echo "Welcome {$row['first_name']}!";//...Display custom success message...
mysql_query("UPDATE users SET last_login_date=NOW() WHERE uid=$uid");//...And update last login date
} else {//Login failed
echo "Invalid username or password.";//Display failure message
}-
sing2trees
- Forum Newbie
- Posts: 10
- Joined: Thu Feb 12, 2009 4:25 pm
Re: How to track login dates/times?
Sorry Sparrrow, I really appreciate your help and patience.
I have tried putting the code into my 'checklogin' script, but am not having any success. The script runs, and shows the welcome message. However it isn't writing the login date to the database. I have copied my checklogin script (without the code you added) below to see if you can see where I am going wrong?
Once again I really appreciate your help and patience with this. Am trying to learn PHP and scripts like this - is just happening very slowly!
I have tried putting the code into my 'checklogin' script, but am not having any success. The script runs, and shows the welcome message. However it isn't writing the login date to the database. I have copied my checklogin script (without the code you added) below to see if you can see where I am going wrong?
Once again I really appreciate your help and patience with this. Am trying to learn PHP and scripts like this - is just happening very slowly!
Code: Select all
<?php
$host="localhost"; // Host name
$username="user"; // Mysql username
$password="password"; // Mysql password
$db_name="db"; // Database name
$tbl_name="tbl"; // 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");
// username and password sent from form
$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' and auth='1'";
$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:login_fail.php");
}
?>
Re: How to track login dates/times?
You could put that code on line 35 of that code. Move the redirect down a line or two and put it before that.sing2trees wrote:Sorry Sparrrow, I really appreciate your help and patience.
I have tried putting the code into my 'checklogin' script, but am not having any success. The script runs, and shows the welcome message. However it isn't writing the login date to the database. I have copied my checklogin script (without the code you added) below to see if you can see where I am going wrong?
Once again I really appreciate your help and patience with this. Am trying to learn PHP and scripts like this - is just happening very slowly!
Code: Select all
<?php $host="localhost"; // Host name $username="user"; // Mysql username $password="password"; // Mysql password $db_name="db"; // Database name $tbl_name="tbl"; // 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"); // username and password sent from form $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' and auth='1'"; $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:login_fail.php"); } ?>
-
sing2trees
- Forum Newbie
- Posts: 10
- Joined: Thu Feb 12, 2009 4:25 pm
Re: How to track login dates/times?
Still having some problems!
Have added the script as you suggested, so the full script is as follows:
checklogin.php:
However once logging in, I get the following error message:
I have the database set up for the login bit as follows:
field: lastlogin
type: timestamp
null: not null
default: current_timestamp (ticked)
Any ideas on this? Thanks again,
Ben

Have added the script as you suggested, so the full script is as follows:
checklogin.php:
Code: Select all
<?php
$host="localhost"; // Host name
$username="user"; // Mysql username
$password="password"; // Mysql password
$db_name="db"; // Database name
$tbl_name="tb"; // 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");
// username and password sent from form
$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' and auth='1'";
$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");
$result=mysql_query("SELECT * FROM membersnew WHERE username='$myusername' AND password='$mypassword'");//validate user
credentials (Hopefully you are using MD5() or some sort of password encryption)
if (mysql_num_rows($result)) {//If credentials matched a user in the db...
$row = mysql_fetch_assoc($result);//...Get user profile information...
echo "Welcome {$row['firstname']}!";//...Display custom success message...
mysql_query("UPDATE membersnew SET lastlogin=NOW() WHERE username=$myusername");//...And update last login date
} else {//Login failed
echo "Invalid username or password.";//Display failure message
}
header("location:login_success.php");
}
else {
header("location:login_fail.php");
}
?>
Finally, it isn't writing to the database.Welcome Ben!
Warning: Cannot modify header information - headers already sent by (output started at /home/sapphire/public_html/sapphire/checklogin.php:38) in /home/sapphire/public_html/sapphire/checklogin.php on line 45
Warning: Unknown: Your script possibly relies on a session side-effect which existed until PHP 4.2.3. Please be advised that the session extension does not consider global variables as a source of data, unless register_globals is enabled. You can disable this functionality and this warning by setting session.bug_compat_42 or session.bug_compat_warn to off, respectively. in Unknown on line 0
I have the database set up for the login bit as follows:
field: lastlogin
type: timestamp
null: not null
default: current_timestamp (ticked)
Any ideas on this? Thanks again,
Ben
Re: How to track login dates/times?
- If you want to send header information after text has been echoed to the client, you must add ob_start(); to the first line of the code
- To register session variables, you need to add session_start(); to the top of the code. Also, you should register variables by setting the $_SESSION array instead of using the session_register function (deprecated). Check out the examples in the manual
- To set the timestamp in the db, you should either set the column default to current timestamp and set it to a blank string in the update statement, OR set it using the mysql NOW() function....but not both. So if you already set "on_update_current_timestamp", then just write your statement like lastupdate = ''
Re: How to track login dates/times?
I'm responding to the public post instead of PM so others may benefit from the full discussion.sing2trees wrote: Everything is working now, with the headers and session start (I read through the manual link you sent through), however the last_login_date column still isn't updating.
The script I have on checklogin.php is:
And the settings on the database:Code: Select all
$result=mysql_query("SELECT * FROM $tbl_name WHERE username='$myusername' AND password='$mypassword'");//validate user credentials (Hopefully you are using MD5() or some sort of password encryption) if (mysql_num_rows($result)) {//If credentials matched a user in the db... $row = mysql_fetch_assoc($result);//...Get user profile information... echo "Welcome {$row['firstname']}!";//...Display custom success message... mysql_query("UPDATE $tbl_name SET last_login_date = '' WHERE username=$username");//...And update last login date } else {//Login failed echo "Invalid username or password.";//Display failure message }
Field: last_login_date
Type: Timestamp
Attributes: ON UPDATE CURRENT_TIMESTAMP
Null: not null
Default: CURRENT_TIMESTAMP (ticked)
Whenever I login, everything works fine, however the last login date field remains as: 0000-00-00 00:00:00
Do you know what I am doing wrong?
I made a mistake. Updating with empty string sets the value to "nothing" (zeroes). I believe setting it to NOW() should have worked, but I don't know why it didn't. Also, if your username field is a string, you need to add tic marks around the value (If it is just numbers, then omit the tic marks). Try it like this:
Code: Select all
mysql_query("UPDATE $tbl_name SET last_login_date = NULL WHERE username='$username'");