MySQLi event delete row after certain time

Question

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

Answer 1

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:

SUBSTR(date, 1, 4)='0000'

Not like in PHP:

substr($user['date'], 0, 4) === '0000'

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:

DELETE FROM users WHERE SUBSTR(date, 1, 4)='0000' AND HOUR(TIMEDIFF(NOW(), CONCAT(SUBSTR(NOW(), 1, 5), SUBSTR(date, 5)))) >= 24

Thank you all for your comments! - Minding