Page 1 of 1
How to track login dates/times?
Posted: Thu Feb 12, 2009 4:27 pm
by sing2trees
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
Re: How to track login dates/times?
Posted: Thu Feb 12, 2009 5:07 pm
by requinix
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.
Re: How to track login dates/times?
Posted: Thu Feb 12, 2009 5:16 pm
by 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.
Code: Select all
UPDATE users SET last_login_date=NOW() WHERE uid=$uid
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.
Re: How to track login dates/times?
Posted: Thu Feb 12, 2009 6:55 pm
by TAViX
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.

Re: How to track login dates/times?
Posted: Fri Feb 13, 2009 3:40 am
by sing2trees
Fantastic - thanks ever so much to everyone for their help!!
I will give it a try later and report back!
Thanks again
Re: How to track login dates/times?
Posted: Fri Feb 13, 2009 3:46 am
by sing2trees
[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.
Code: Select all
UPDATE users SET last_login_date=NOW() WHERE uid=$uid
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!
Re: How to track login dates/times?
Posted: Fri Feb 13, 2009 1:27 pm
by sparrrow
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
}
Re: How to track login dates/times?
Posted: Fri Feb 13, 2009 5:12 pm
by sing2trees
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");
}
?>
Re: How to track login dates/times?
Posted: Fri Feb 13, 2009 5:34 pm
by watson516
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");
}
?>
You could put that code on line 35 of that code. Move the redirect down a line or two and put it before that.
Re: How to track login dates/times?
Posted: Sun Feb 15, 2009 2:19 am
by sing2trees
Still having some problems!
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");
}
?>
However once logging in, I get the following error message:
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
Finally, it isn't writing to the database.
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?
Posted: Sun Feb 15, 2009 12:47 pm
by sparrrow
- 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?
Posted: Wed Feb 18, 2009 3:33 pm
by sparrrow
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:
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
}
And the settings on the database:
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'm responding to the public post instead of PM so others may benefit from the full discussion.
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'");