-
Pertti
Senior Member
-
Posts: 365
Threads: 5
Joined: May 2015
Reputation:
32
You could put 'expiry' date/time set +1 hour in future in DB and when checking code check against it.
Because it's such a limited use feature and because there's time limit on after which you don't have to worry about extra codes being stored in DB, you could run clean-up script once a day or once a month even.
It's usually done via CRON job on server side, that calls php /pahttoyourproject/index.php cron/cleanup from command line.
Just make sure you check that such controllers are only called from command line, think the helper function for that in CI was is_cli().
-
skunkbad
Senior Citizen
-
Posts: 1,300
Threads: 63
Joined: Oct 2014
Reputation:
86
When you create the code and store it in the DB, also create a timestamp:
Code: insert into whatever_table
(user_id, code, created_at)
values( ?, ?, NOW() );
Then when you want to check for a valid code:
Code: select * from whatever_table
where user_id = ?
and code = ?
and created_at > ( NOW() - interval 1 hour );
-
richb201
Posting Freak
-
Posts: 1,004
Threads: 300
Joined: Sep 2017
Reputation:
0
07-13-2018, 11:04 AM
(This post was last modified: 07-13-2018, 11:06 AM by richb201.)
Thanks Brian. I have figured out that "my" code writes a time stamp into the database when it creates the "specialcode". So I when I check for the specialcode in the database, I only want to consider records that have been created in the last 60 minutes. I wrote this
$query = $this->db->get_where('users', array('forgot_pass_identity' => $fp_code),TIMESTAMPDIFF(MINUTE, NOW(), 'modified')=> '<60');//this is the user since the code matches using qb
The field in the database is called modified. I am not sure what the TIMESTAMPDIFF returns? The => is not working. I want to find records where the minutes is "less than 60". Not sure if this is possible?
proof that an old dog can learn new tricks
-
Pertti
Senior Member
-
Posts: 365
Threads: 5
Joined: May 2015
Reputation:
32
(07-13-2018, 03:47 PM)skunkbad Wrote: PHP Code: AND modified > ( NOW() - INTERVAL 1 HOUR )
Ah interesting, I've always used DATE_SUB("2017-06-15", INTERVAL 10 DAY) function, so didn't think the simple minus or plus wouldn't work.
For some reason I've always preferred setting exact expiry time, not current timestamp then trying to work backwards from there, I guess both ways have some shortcomgins and some advantages.
-
richb201
Posting Freak
-
Posts: 1,004
Threads: 300
Joined: Sep 2017
Reputation:
0
07-14-2018, 07:58 AM
(This post was last modified: 07-14-2018, 08:02 AM by richb201.)
Guys, I am a little confused. Is "INTERVAL 1 HOUR" and "INTERVAL 10 DAY" some kind of constants or are they pseudo code? I think the problem I am having is based on the "TIMESTAMPDIFF(MINUTE, NOW(), 'modified')" which is part of MariaDb. I can certainly subtract instead. But how do I get the Interval 1 Hour. Or even more precisely, how do I write the SQL that will only return the record that is both for the right fp_code and is less than 1 hour old? what I currently have is
$sql="SELECT * FROM users WHERE TIMESTAMPDIFF(MINUTE, NOW(), modified) <= 10 AND forgot_pass_identity = ?";
$query=$this->db->query($sql,$fp_code);
$q=$query->result();
$iCount=$query->num_rows();
This is returning zero records found ($iCount=0). How do I calculate the INTERVAL 1 HOUR? ie what format can be subtracted from NOW()? I found the date_sub() and can try to fool with that, but is it searchable in an SQL statement?
proof that an old dog can learn new tricks
|