MirageSF
asked on
Store Last Login Details In DB
Hi,
I have a Database with a field called Last_login, how do I store the date, time and IP of the clients PC within this cell, it will need to go into the correct Last_login cell that belongs to dms_id (so I store it in the correct persons field)
I wish the details to be stored as below for example...
Thursday, 18 February 2004 - 18:26 - IP: 69.66.42.22
How do I put that data into the field for the correct client, and also read it back when needed ?
Thankyou
I have a Database with a field called Last_login, how do I store the date, time and IP of the clients PC within this cell, it will need to go into the correct Last_login cell that belongs to dms_id (so I store it in the correct persons field)
I wish the details to be stored as below for example...
Thursday, 18 February 2004 - 18:26 - IP: 69.66.42.22
How do I put that data into the field for the correct client, and also read it back when needed ?
Thankyou
ASKER
Hi Zyloch,
I have the two tables, one called clients contains DMS_ID, Name, Email and DMS Password.
The other called domains contains DMS_ID, Domain, Username, Password, Plan, Server, Created, Status, Invoice_reference, invoice_amount, invoice_freq and last_login.
Basically I need some code that would generate a string of "Thursday, 18 February 2004 - 18:26 - IP: 69.66.42.22" for example, and I could then insert this into the correct field. Reading it back would be easy as its already stored as one string.
I used to program in Pascal, Basic, C++ etc years and years ago, but my grey matter in these areas has gone somewhat fuzzy, from little snippets of code and ideas plus part memory I have managed to do what I need to do so far, and with the help of you guys on EE my knowledge is improving.
Thankyou
I have the two tables, one called clients contains DMS_ID, Name, Email and DMS Password.
The other called domains contains DMS_ID, Domain, Username, Password, Plan, Server, Created, Status, Invoice_reference, invoice_amount, invoice_freq and last_login.
Basically I need some code that would generate a string of "Thursday, 18 February 2004 - 18:26 - IP: 69.66.42.22" for example, and I could then insert this into the correct field. Reading it back would be easy as its already stored as one string.
I used to program in Pascal, Basic, C++ etc years and years ago, but my grey matter in these areas has gone somewhat fuzzy, from little snippets of code and ideas plus part memory I have managed to do what I need to do so far, and with the help of you guys on EE my knowledge is improving.
Thankyou
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thankyou, works great dont understand how I miss these simple things :(
This works fine for server time of last login, but any easy way to get the client machines time ?
BTW - Just found out the hard way that MD5 Hash encryption is one way lol
This works fine for server time of last login, but any easy way to get the client machines time ?
BTW - Just found out the hard way that MD5 Hash encryption is one way lol
Heh, it's what makes it more secure, md5 I mean.
Client machine time... hmm... I haven't heard of a way to get this with PHP. Only with client side Javascript...
Client machine time... hmm... I haven't heard of a way to get this with PHP. Only with client side Javascript...
You could possibly store the clients timezone as a property of their accounts. Then you'd just save the servertime and adjust for the timedifference.
regards
-r-
regards
-r-
Owh, one thing.. You could use the DATETIME column format instead of the VARCHAR/STRING column. This would be more spaceefficient and should automatically always make sure your dates are correct because the db does the checking.
you'd input data in a date('Y-m-d H:i:s') format--> ('2004-09-21 10:15:12') in my timezone at the moment.
regards
-r-
you'd input data in a date('Y-m-d H:i:s') format--> ('2004-09-21 10:15:12') in my timezone at the moment.
regards
-r-
I don't think you want to store the client's machine time. that could be set to anything. store all your times in the same timezone (based on the server time) in the db to make life easy, and then do what Roonan suggested (store the client timezone somewhere) if you need to see the time in the client's local time.
You should try to never trust users or users PC since you never know if they have correctly configured their PCs. You should use the server time and if the website is going to be seen from other countries then you should ask the user for their timezone and calculate the current time for them.
I would do it something like this:
Mysql database:
#
# Table structure for table 'user'
#
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`user_id` varchar(32) NOT NULL default '',
`username` varchar(32) binary NOT NULL default '',
`password` varchar(32) binary NOT NULL default '',
PRIMARY KEY (`user_id`),
UNIQUE KEY `k_username` (`username`)
) TYPE=MyISAM;
#
# Dumping data for table 'user'
#
INSERT INTO `user` VALUES("cd5afb82baeb22502d 9be171039e 5c20", "user1", "pwd1");
INSERT INTO `user` VALUES("992bc880c621d1dd46 55f2409407 0548", "user2", "pwd2");
#
# Table structure for table 'log'
#
DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
`user_id` varchar(32) NOT NULL default '',
`date` date default NULL,
`time` time default NULL,
`ip` varchar(15) NOT NULL default ''
) TYPE=MyISAM;
#
# Dumping data for table 'log'
#
INSERT INTO `log` VALUES("cd5afb82baeb22502d 9be171039e 5c20", "2004-09-01", "15:05:25","200.61.161.120 ");
INSERT INTO `log` VALUES("cd5afb82baeb22502d 9be171039e 5c20", "2004-09-01", "16:06:26","200.61.161.120 ");
INSERT INTO `log` VALUES("992bc880c621d1dd46 55f2409407 0548", "2004-09-22", "11:05:06","216.21.11.10") ;
PHP code:
//INSERT LOG
//you can use this variables or CURRENT_TIMESTAMP()
$now_value = date("Ymd H:i:s");
$user_ip = getenv("REMOTE_ADDR");
//insert connection to db and select database
mysql_query("INSERT INTO log values ('$uid',CURRENT_TIMESTAMP( ),'$user_i p')") or die(mysql_error());
//SELECT LOG
//depends on what you want to do you could use
//MAX(DATE_FORMAT(date,'%W , %d %M %Y %H:%i')) AS date
$sql = "SELECT DATE_FORMAT(date,'%W, %d %M %Y %H:%i') AS date,ip FROM log WHERE user_id='$uid';
$query_log = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows ($query_log) > 0) {
$date_txt = mysql_result ($query_log,0,"date");
$ip = mysql_result ($query_log,0,"ip");
} else {
$date_txt = "Never logged on";
$ip = "";
}
if ($ip!="") echo($date_txt." - ".$ip) else echo $date_txt;
I would do it something like this:
Mysql database:
#
# Table structure for table 'user'
#
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`user_id` varchar(32) NOT NULL default '',
`username` varchar(32) binary NOT NULL default '',
`password` varchar(32) binary NOT NULL default '',
PRIMARY KEY (`user_id`),
UNIQUE KEY `k_username` (`username`)
) TYPE=MyISAM;
#
# Dumping data for table 'user'
#
INSERT INTO `user` VALUES("cd5afb82baeb22502d
INSERT INTO `user` VALUES("992bc880c621d1dd46
#
# Table structure for table 'log'
#
DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
`user_id` varchar(32) NOT NULL default '',
`date` date default NULL,
`time` time default NULL,
`ip` varchar(15) NOT NULL default ''
) TYPE=MyISAM;
#
# Dumping data for table 'log'
#
INSERT INTO `log` VALUES("cd5afb82baeb22502d
INSERT INTO `log` VALUES("cd5afb82baeb22502d
INSERT INTO `log` VALUES("992bc880c621d1dd46
PHP code:
//INSERT LOG
//you can use this variables or CURRENT_TIMESTAMP()
$now_value = date("Ymd H:i:s");
$user_ip = getenv("REMOTE_ADDR");
//insert connection to db and select database
mysql_query("INSERT INTO log values ('$uid',CURRENT_TIMESTAMP(
//SELECT LOG
//depends on what you want to do you could use
//MAX(DATE_FORMAT(date,'%W
$sql = "SELECT DATE_FORMAT(date,'%W, %d %M %Y %H:%i') AS date,ip FROM log WHERE user_id='$uid';
$query_log = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows ($query_log) > 0) {
$date_txt = mysql_result ($query_log,0,"date");
$ip = mysql_result ($query_log,0,"ip");
} else {
$date_txt = "Never logged on";
$ip = "";
}
if ($ip!="") echo($date_txt." - ".$ip) else echo $date_txt;
You of course would need to have a cross-referencing field. For example, your table would have to have a userid field. That way, you can tell which user is which row.
Then, you'd use standard mysql_query to insert stuff into a mysql database (other ways for other databases).
How advanced are you in PHP, mySQL, etc. or programming in general?
Regards,
Zyloch