Previous Thread
Next Thread
Print Thread
Hop To
Good query for non-active subscribers?
#231377 11/29/2009 1: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?
Basil #231380 11/29/2009 1:51 PM
Joined: Jun 2006
Posts: 9,243
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?
Rick #231384 11/29/2009 3: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.


Link Copied to Clipboard
ShoutChat Box
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
Non-Admin users can't see attached pictures
by jjjjj - 07/11/2020 8:09 PM
[NOTABUG] Report Post bug..
by Morgan - 07/05/2020 1:48 PM
Trial Subscription question.
by Ruben - 07/03/2020 2:34 PM
Unread posts no longer tracked per user...
by KirkW - 06/16/2020 12:21 AM
Error on Insert into POSTS
by jjjjj - 06/07/2020 8:09 PM
Who's Online Now
0 members (), 75 guests, and 45 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
3D Creations
3D Creations
by JAISP, December 30
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