[ic] Customer Housekeeping & IC 4.8.7 - Resend
mark at vwe.net
Wed Mar 2 10:32:31 EST 2005
At 13:10 02/03/2005, you wrote:
>Mark Bryant wrote:
>>This is an edited resend as my last post seems to have slipped through
>>I'm trying to use IC to do a bit of housekeeping via the Admin UI.....
>Here is an SQL statement (I think it will work with your version of Mysql)
>that will select all userdb records that are not found in the transaction
>table. ie, they have not placed orders.
>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.
>I may be wrong, but I think that if a customer has created an account but
>not placed an order, there will only be information in the userdb table
>therefore, all you need to do is delete one row in the userdb table and
>the customer is gone.
Thanks loads for answering my silly question.
Yes, that SELECT does seem to pull out the account names I need :)
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.
I was aware of the possible implications you mentioned above about the age
of the account, but due to my ignorance of what's being stored about a user
account in terms of age (or how to work it out), I'd taken the view that
I'd accept some recently created accounts will also be deleted during this
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.
Once again, many thanks :)
vwe internet ltd
PO BOX 1067
Shop - http://www.eros-shop.co.uk
EMail - info at eros-shop.co.uk
Tel - 0870 284 3369
Fax - 0870 284 4469
More information about the interchange-users