[ic] Customer Housekeeping & IC 4.8.7 - Resend

[Peter]

On 03/02/05 07:32, Mark Bryant wrote:
> At 13:10 02/03/2005, you wrote:
>> SELECT userdb.username from userdb LEFT JOIN transactions ON 
>> userdb.username=transactions.username WHERE transactions.code IS NULL
>>
>> It would be risky to delete all records found by that statement since 
>> it does not take into account the time since the creation of the 
>> account. So if someone created an account 1 minute before that 
>> statement was run, it would find that account, and delete it, if a 
>> delete statement was used.
>>
>> So probably filtering by mod_time in the userdb would be the way to 
>> find all userdb records that have been created more than X time ago 
>> and have not placed an order.
> 
> All I need to figure out now is how to do it from a link on the 
> customer.html admin page and I'm ready to go, although with the list 
> I've got atm (it's not terribly large at 260 usernames) I could delete 
> them manually and then try to stay on top of it on a monthly basis. A 
> clickable link in the Admin UI would be more desirable long term of course.

Actually I recommend doing it from a cron job.

> If it's easy enough to exclude accounts that are less than a month old 
> then I'm interested in knowing how to do that as it's a better solution 
> than the one I currently have above.

Untested, requires mysql 4.1.1 or higher:

SELECT userdb.username from userdb LEFT JOIN transactions ON 
userdb.username=transactions.username WHERE transactions.code IS NULL 
AND FROM_UNIXTIME(mod_time) < SUBTIME(NOW(), '0-1-0 0:0:0')

I'll leave you to figure out how to turn that into a DELETE statement.

Peter