[SOLVED] Auto expire a password. Is this possible?

[tqla]

I have a standard reg form that uses php/mysql. A user can sign and choose their own username and password. Is it possible to make the password expire or reset in 30 days?

[Caesar]

Yes. Just store the "registration date" in the user table. And check this upon each login. Be sure to save it as a timestamp. Don't be a knucklehead and store a formatted date in the db.

[ToonMariner]

why not?

 

mysql's date manipulation/calculation abilities are very very good.

 

allows you to use things like date_add(`datestored`, INTERVAL 30 DAY) which is exactly what will help this chap solve his 30 day expiry problem....

[tqla]

Well Caesar I guess I'm a knucklehead 'cause I stored it in a formatted state. Ha ha! I can change the format back though.  

So, I can check the date that they first logged in and if it's been 30 days reset their password. Sounds logical, I'm just not sure how to do it. Can you give me a bit of code to set me in the right direction?

 

ToonMariner, where can I learn more about  mysql's date manipulation/calculation capabilities?  It sounds like I can do it your way too. Can you give me a bit of code to set me in the right direction?

 

Sorry for asking for code but I'm a noob trying to learn php as fast as I can. I literally go to sleep with a headache every night. Thanks!

 

[ToonMariner]

There si nothing wrong with storing the date as you have done....

 

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

[tqla]

Thanks ToonMariner,

Do you (or anybody) have code to start me off in the right direction?

I need to check the date on login and reset password if it's been 30 days or more.

Humbly,

tqla

 

[Nhoj]

Could always store a timestamp in a special column. Everytime someone logs in you can check that column with something like...

 

 

$last_reset = mysql_result(mysql_query('SELECT `last_reset` FROM `users` WHERE `user_id` = '.$id), 0);

if ($last_reset < time() - (86400 * 30)) {
   echo 'This user\'s password is older than 30 days.';
} else {
   echo 'The password is not 30 days old.';
}

 

Everytime you reset the password just update the last_reset column for the user using something like:

 

mysql_query('UPDATE `users` SET `user_password` = insertapassword, `last_reset` = UNIX_TIMESTAMP() WHERE `user_id` = '.$id);

 

 

Edit: On the topic of formatted dates and timestamps, I personally love timestamps, they a) take up less space, and b) are very, very easy to manipulate and display without complicated queries. [int(10)]

[Caesar]

Edit: On the topic of formatted dates and timestamps, I personally love timestamps, they a) take up less space, and b) are very, very easy to manipulate and display without complicated queries. [int(10)]

 

And that was my point.

 

Sure, formatted dates work....but working with timestamps is such an effortless thing, and so much more flexible.

[ToonMariner]

OR if you already have the date stored in the YYYY-MM-DD HH-mm-ss format which mysql handles so well...

 

<?php
$qry = "SELECT * FROM `users` WHERE `password` = '" . $_POST['password'] . "' AND `username` = '" . mysql_real_escape_string($_POST['username']) . "' AND `date` > DATE_SUB(NOW(), INTERVAL 30 Day)";
$qry = mysql_query($qry);
If (@mysql_num_rows($qry) == 1)
{
echo 'GREAT - Your in!';
}
else
{
$qry = "SELECT * FROM `users` WHERE `password` = '" . $_POST['password'] . "' AND `username` = '" . mysql_real_escape_string($_POST['username']) . "'";
$qry = mysql_query($qry);
if (@mysql_num_rows($qry) == 1)
{
 echo 'Your password has expired';
 // put form in to chose new password...
}
else
{
 echo 'User not recognized';
}
}
?>

[tqla]

Hi again and thank you all for helping me. I decided to try Nhoj's method first and changed my database column to a Timestamp but I'm having a bit of trouble getting it to work.

 

The way that I am doing this is after the user logs in they are directed to a member page. The following code is at the top of that page:

 

<?php
require_once('Connections/Auth.php'); 
$connection = mysql_connect($hostname_Auth, $username_Auth, $password_Auth) 
or die ("Couldn't connect to server.");
$db = mysql_select_db($database_Auth, $connection)
or die ("Couldn't select database.");                                  
    
$last_reset = mysql_result(mysql_query('SELECT `createDate` FROM `Member` WHERE `user_id` = '.$id), 0);

if ($last_reset < time() - (86400 * 30)) {
   echo 'This user\'s password is NOT 30 days old.';
} else {
   echo 'The password is older than 30 days.';
}
?> 

 

The above code checks my DB table called Member and looks at a column called createDate which is a timestamp. I am testing this with a user that has a timestamp of "20050615000000" but the code is returning "This user's password is NOT 30 days old."

 

Can someone check my code and see if it's correct?

 

(I was wondering if the problem is here: WHERE `user_id` = '.$id

How does it know wht the $id is? Man I dont't know. )

 

Thank you.

 

 

 

 

[tqla]

*bump*

[redarrow]

use int for the timestamp field in the database ok.

[tqla]

Thanks redarrow.

 

Are you saying that the createDate column should be an INT and not a Timestamp?

 

If so, let's say I change createDate to an INT.

 

How do I insert a timestamp into it? What should $today be?

 

Here is my insert code:

 

$sql = "INSERT INTO Member (loginName,createDate,password,firstName,lastName,company,title,email,phone)
VALUES ('$newname','$today','$newpass','$firstName','$lastName','$company','$title','$email','$phone')";

 

 

 

[redarrow]

now() is the current timestamp ok but for php we use time() ok

 

lovly link keeping this one

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_now

<?php

$sql = "INSERT INTO Member (loginName,createDate,password,firstName,lastName,company,title,email,phone)
VALUES ('$newname',now(),'$newpass','$firstName','$lastName','$company','$title','$email','$phone')";
?>

[tqla]

Hi redarrow,

 

I used now() but for some reason only the year is entered.

 

In other words, after the code is executed only the year "2007" is in the column.

 

The column is INT. Should it be a Timestamp or a INT?

 

Thanks.

 

<?php

$sql = "INSERT INTO Member (loginName,createDate,password,firstName,lastName,company,title,email,phone)
VALUES ('$newname',now(),'$newpass','$firstName','$lastName','$company','$title','$email','$phone')";
?>

[redarrow]

<?php
$date=time();
$sql = "INSERT INTO Member (loginName,createDate,password,firstName,lastName,company,title,email,phone)
VALUES ('$newname','$date','$newpass','$firstName','$lastName','$company','$title','$email','$phone')";
?>

[tqla]

Thanks redarrow,

 

I'll try this.

[tqla]

Totally worked. Thanks redarrow, ToonMariner, Caesar, Nhoj for all your help!!!!!