[ic] Customer Housekeeping & IC 4.8.7 - Resend

Mark Bryant mark at vwe.net
Fri Mar 4 10:22:59 EST 2005


At 19:03 03/03/2005, you wrote:
>Mark Bryant wrote:
>
>>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.
>This is untested...
>
>SELECT userdb.username from userdb LEFT JOIN transactions ON 
>userdb.username=transactions.username WHERE transactions.code IS NULL AND 
>FROM_UNIXTIME(mod_time) < SUBDATE (NOW(), INTERVAL 1 MONTH);
>||
>Dan


Hi Dan,

Many thanks for the select :)

Sadly it also fails in a similar way to the previous one and spews an error 
about (NOW(), INTERVAL 1 MONTH) which would indicate it doesn't like 
anything after < SUBDATE.

I suspect the solution is to upgrade to a newer version of MySQL as clearly 
3.23 is lacking in a fair bit of functionality.

Once again, many thanks for your help :)

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




More information about the interchange-users mailing list