[ic] Customer Housekeeping & IC 4.8.7 - Resend

Mike Heins mike at perusion.com
Thu Mar 3 16:29:37 EST 2005


Quoting Mark Bryant (mark at vwe.net):
> 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.

If you are talking about 260 usernames, from a performance standpoint it is
not worth the trouble and the potential loss of business to remove the
records.

If you don't want to see them in the UI, why don't you just mark them
as inactive?

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.765.647.1295  tollfree 800-949-1889 <mike at perusion.com>

The U.S. Senate -- white male millionaires working for YOU!  -- Dave Barry


More information about the interchange-users mailing list