• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
how to clear a field in database in x mins?

#11
(07-14-2018, 07:58 AM)richb201 Wrote: 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?

"INTERVAL" is also part of MySQL / MariaDB:

http://www.mysqltutorial.org/mysql-interval/
Reply

#12
Thanks Brian. I am not sure why but the Interval 1 Hour worked.
proof that an old dog can learn new tricks
Reply

#13
(07-15-2018, 06:31 AM)richb201 Wrote: Thanks Brian. I am not sure why but the Interval 1 Hour worked.

At least for me, SQL (MySQL and MariaDB) is a lot harder to master than PHP. Its error messages are often unclear, and its documentation is harder to understand. I've been using it for 12+ years and still don't feel like I'm great with it. It definitely takes a lot of learning and experience to fully understand.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.