[ic] Customer Housekeeping & IC 4.8.7 - Resend
peter at pajamian.dhs.org
Wed Mar 2 19:21:51 EST 2005
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.
More information about the interchange-users