Yes, sorry Rudy, I'm not sure what I was thinking. It was a long frustrating day. To answer your question, max(payDate) will give the member's most recent payment. Here is my most recent version:
SELECT uID, MAX(date(payDate)) AS lastPayment, type, frequency FROM payments where uID in (select uID from users where subscription <> 'Free') GROUP BY uID;
This is working better, but not perfectly. Each row has a frequency of monthly or yearly. Each row also has a type of gold or silver. So the above query is indeed showing the most recent payment for a member, but the frequency is not always correct. For example, one user had the following payments on file:
5/5/2011 - Monthly Gold
6/5/2011 - Monthly Gold
7/14/2011 - Yearly Gold
7/14/2012 - Yearly Gold
My query is correctly showing that her most recent payment is 7/14/2012 Monthly Gold. Once I can overcome this hurdle, the next thing is to devise a way to use a dynamic having clause: having (DATE_SUB(CURDATE(),INTERVAL 40 DAY) >= lastPayment) depending if the member's most recent payment was a monthly or yearly one. If it was monthly, then the interval of 40 days will show me members that have stopped making monthly payments and need to manually be reverted to the free membership. If the most recent payment was yearly, then the interval of 375 days will show me members that stopped making yearly payments and need to manually to manually be reverted to the free membership.
None of this would be an issue if Paypal's IPN (payment notification program) was not so flaky.