I want users on my page to verify thier accounts (email) in one day. If a user isn't verified yet the datetime (date) is set to something like 0000-03-23 15:45:56 (the year is 0000 - I know that this causes problems in new years eve).
Event: (every hour)
DELETE FROM users WHERE SUBSTR(date, 0, 4)='0000' AND DAY(TIMEDIFF(NOW(), date)) >= 1
The event isn't working so i've created this test query:
$resultOne = $mysqli->query("SHOW COLUMNS FROM users WHERE SUBSTR(date, 0, 4)='0000'")or die($mysqli->error);
$resultTwo = $mysqli->query("SHOW COLUMNS FROM users WHERE DAY(TIMEDIFF(NOW(), date)) >= 1")or die($mysqli->error);
Now i'm getting this error message:
Unknown column 'date' in 'where clause'
Questions:
- Why isn't it working?
- Which better ways to verify users do you recomment?
Thank you! - Minding
Ok, there where two problems here:
First: test query
The "SHOW COLUMNS" didn't accept my where clause, but "SELECT *" did.
Second: SUBSTR()
I don't know exectly why, but the MySQLi SUBSTR has to be:
Not like in PHP:
One last problem:
New users are deleted nearly instantly (every hour -> test: every minute) and not after 1 day as they should.
EDIT:
I just want to share my final event code, in case somebody needs it:
Thank you all for your comments! - Minding