[ic] Customer Housekeeping & IC 4.8.7 - Resend

Dan Bergan danb at championshipproductions.com
Thu Mar 3 14:03:40 EST 2005


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



More information about the interchange-users mailing list