[ic] Customer Housekeeping & IC 4.8.7 - Resend

[Mark Bryant]

At 18:50 04/03/2005, you wrote:
>Mark Bryant wrote:
>
>>At 17:30 04/03/2005, you wrote:
>>
>>>Mark Bryant wrote:
>>>
>>>>At 16:08 04/03/2005, you wrote:
>>>>
>>>>>>>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
>>>>>
>>>>>Mark,
>>>>>
>>>>>I use MySQL 3.23.58 and I pasted the query above into phpMyAdmin and 
>>>>>it ran fine.  Do you have any more information about the error?
>>>>>
>>>>>Dan
>>>>
>>>>
>>>>
>>>>Hi Dan,
>>>>
>>>>I'm using mysql-server-3.23.49-3 and the exact error given by webmin is:
>>>>
>>>>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) < SUBDATE (NOW(), 
>>>>INTERVAL 1 MONTH); failed : You have an error in your SQL syntax near 
>>>>'(NOW(), INTERVAL 1 MONTH)' at line 1
>>>>
>>>>Thanks
>>>>
>>>>Mark
>>>
>>>Can you execute either of these queries?
>>>
>>>SELECT SUBDATE(NOW() , INTERVAL 1 MONTH);
>>>
>>>SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
>>>
>>>They should both be available in your version of MySQL.
>>>
>>>Dan
>>
>>
>>Yes, both of those statements will run quite happily.
>>
>>SELECT SUBDATE(NOW() , INTERVAL 1 MONTH);
>>
>>SUBDATE(NOW() , INTERVAL 1 MONTH)
>>2005-02-04 18:19:03
>>
>>
>>SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
>>
>>DATE_SUB(NOW(), INTERVAL 1 MONTH)
>>2005-02-04 18:17:47
>>
>>
>>Mark
>It looks like the only difference between the earlier query and the one 
>above is the space between SUBDATE and the parenthesis.  Maybe MySQL is 
>choking on that space?  That seems unlikely, but if SUBDATE() runs fine, 
>it should also work in the other query.
>
>Dan


Dan, you're absolutely right! It's a syntax issue....

Removing the space after SUBDATE before the ( has made it work :)

Now all I have to figure out is the corresponding delete statement and how 
to "run" it from a link the admin UI.

Thanks very much for all your support on this :)

Mark



vwe internet ltd
EMail - info at vwe.net
WWW - http://www.vwe.net/
Tel - 0870 284 3369
Fax - 0870 284 4469
Tech Support - 0906 470 3369*
*UK only. Calls charged at 60p/min at all times

UK Broadband with modem, 2 filters and half price installation until 
31/03/2005 - http://www.vwe.net/adsl