Previous Thread
Next Thread
Print Thread
Hop To
Good query for non-active subscribers? #231377 11/29/2009 12:14 PM
Joined: Aug 2006
Posts: 583
Basil Offline OP
old hand
OP Offline
old hand
Joined: Aug 2006
Posts: 583
I have four levels of paid subscriptions set up: Bronze, Silver, Gold and, Platinum. When someone subscribes to a level (for example, Bronze), I go in and manually set their "Member Custom Title" to read "Bronze Member". Etc. I also go to Database tools and run a query like:
SQL Query
UPDATE ubbt_USER_PROFILE
SET USER_GROUP_IMAGES = 'a:1:{i:1;s:1:"9";}'
WHERE USER_ID = '2919'


Which sets a little star of appropriate color next to their names.

The problem is, when a subscription expires, those don't automatically get reset - I have to do it manually. But very often someone's subscription will expire, but then they turn around and re-subscribe. The problem is, when I do a query in the Subsciption section and ask to look at all members where "Active=NO" I get pages of members, but many of those same members will also come up if i filter Active = Yes.

What would be a good query to run to see ONLY those members who are not active (and not also see those who went inactive and then re-subscribed). In plain English, I want to see members where Active = No but exclude those where They also have Active = YES for another subscription. (Those who are truly inactive, so I can reset their member status).


Re: Good query for non-active subscribers? [Re: Basil] #231380 11/29/2009 12:51 PM
Joined: Jun 2006
Posts: 9,243
Rick Offline
Former Developer
Offline
Former Developer
Joined: Jun 2006
Posts: 9,243
Unfortunately there isn't a really easy way to do this one in a query, it would need to be scripted.

We need to make a few changes in this area for version 8. When you select a filter such as inactive/active, it should only be showing the current status for that subscription, not all. The way we store everything for history makes this a bit tough, but I believe I have a couple ideas on how to achieve this.

Re: Good query for non-active subscribers? [Re: Rick] #231384 11/29/2009 2:16 PM
Joined: Aug 2006
Posts: 583
Basil Offline OP
old hand
OP Offline
old hand
Joined: Aug 2006
Posts: 583
What I'm doing at present is to use PhPMyADmin and browse ubbt_SUBSCRIPTION_DATA then sort on USER_ID, then I can scan down the list and see which user IDs have SUBSCRIPTION_IS_ACTIVE set to 0 only with no other lines that are set to 1. It works but is a PITA. Maybe have a second SUBSCRIPTION STATUS TABLE (in addition to the current SUBSCRIPTION_DATA table) that would only allow one entry per user and a current status / group. If that changes (e.g., subscription expires) then that field gets set 0 or 1. Just a thought.


Forum Search
ShoutChat Box
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
Mobile app?
by Baldeagle - 12/06/2019 9:32 PM
How do you change Text Line spacing?
by jorb - 11/23/2019 12:14 AM
What happened to FAQ or Forum Help
by Ruben - 11/20/2019 11:58 AM
Search feature encountering an Error message
by jorb - 11/20/2019 12:06 AM
Followed List v7.7.2 Question
by Ruben - 11/12/2019 12:22 PM
Who's Online Now
3 registered members (Gizmo, Gator, Baldeagle), 85 guests, and 431 spiders.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Amusing Terain Scenics
Amusing Terain Scenics
by isaac, August 19
Sky places
Sky places
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Snapshot build 20191023)