How would I delete records after a certain time based on the value of a specific field?

I've got a table called users. Two of the fields in that table are "activation" and "regdate". Both are VARCHAR. The activation field contains the string "active" if the user has activated the account. If the user has not activated the account, the field contains a string which is a randomized alphanumerical activation key. The regdate field contains the date and time that the user registered the account, populated by date("m.d.y H:m:s");.

What I need to do is create a PHP script which will delete all records that do NOT equal "active" in the activation field and are older than an amount of time that I specify. I know how to locate records by matching criteria such as selecting records where activation equals "act开发者_开发知识库ive", but I would need it to do the opposite. I would need it to match all records where activation does not equal "active" (because the randomized string would be impossible to match, would it not?).

Of course, I would then have to also compare the selected records to the current time based on those records' "regdate" field.

So, in short, I've got a bunch of records and I want to delete all of the ones that are older than a certain amount of time and have a field that does NOT equal "active".

I apologize if I did not explain this well enough. I'm really tired, and this is also my first post on stackoverflow.

If you convert your regdatefield into a DATETIME type, the query can be as simple as:

DELETE FROM users WHERE activation <> 'active' AND regdate < '2011-01-01'

Otherwise, depending on the format of your regdate VARCHAR,you'llhave to CAST or CONVERT it to a DATETIMEbefore comparing it in your WHERE clause.

Is this enough infoforyou togetthisworking?

Change your field in the database to "datetime" using mysqladmin should be a breeze ( the data would look something like this "2011-09-13 02:08:20") then you could use standard mysql commands like:

mysql_query("DELETE   FROM `iplog` WHERE  DATE(datefield)  <= DATE_ADD(CURDATE(), INTERVAL -2 DAY) AND activation <> 'active'");

this is deleting anything older than 2 days in my 'iplog' table and not active.