[ic] Customer Housekeeping & IC 4.8.7 - Resend

[Mark Bryant]

At 00:21 03/03/2005, you wrote:
>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.


Hi Peter,

Thanks for your response and your suggested SELECT statement.

Sadly I'm stuck with MySQL 3.23 and as expected the above select doesn't 
work :(

Failed to execute SQL : SQL 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'); failed :You have an error in your SQL syntax near '(NOW(), 
'0-1-0 0:0:0')' at line 1

Would anyone know how to get it working for 3.23 and perhaps also provide 
the corresponding DELETE statement? MySQL knowledge is terribly limited and 
I'm already in way over my head.

Many thanks

Mark



Eros Shop
vwe internet ltd
PO BOX 1067
SLOUGH
SL1 7YA
UK

Shop - http://www.eros-shop.co.uk
EMail - info at eros-shop.co.uk
Tel - 0870 284 3369
Fax - 0870 284 4469