I have both recurring and non-recurring subscriptions available. If a member can't recall if they chose recurring or non-recurring, is there a way that I can tell (maybe some field in a database table?) whether their subscription is recurring or not recurring?
How can I tell if someone has recurring subscription?
Collapse
X
-
Tags: None
-
The recurring flag is set in the subscription table in the cost column. This query "should" give you a list of the userid of anyone with a recurring subscription. It worked on my test server.
Code:SELECT userid FROM subscriptionlog WHERE subscriptionid IN (SELECT subscriptionid FROM subscription WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(cost,';',22),':',-1)=1
Last edited by Replicant; Mon 5 Oct '15, 4:17pm.
-
The recurring flag is set in the subscription table in the cost column. This query "should" give you a list of the userid of anyone with a recurring subscription. It worked on my test server.
Code:SELECT userid FROM subscriptionlog WHERE subscriptionid IN (SELECT subscriptionid FROM subscription WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(cost,';',22),':',-1)=1
Why not just do:
Code:SELECT userid FROM subscriptionlog WHERE subscriptionid=XXXXX
Subscription: SubscriptionNameHere (id: 1)
If you want more info of the user, you can use this query:
Code:SELECT a.userid, b.username, b.email FROM subscriptionlog a, user b WHERE a.userid=b.userid AND a.subscriptionid=XXXXX
Last edited by glennrocksvb; Tue 6 Oct '15, 10:02am.Comment
-
Determining the recurring info from a php-serialized column in the database via its index position in that column is not recommended as they differ depending on the subscription settings.
Why not just do:
Code:SELECT userid FROM subscriptionlog WHERE subscriptionid=XXXXX
Subscription: SubscriptionNameHere (id: 1)
If you want more info of the user, you can use this query:
Code:SELECT a.userid, b.username, b.email FROM subscriptionlog a, user b WHERE a.userid=b.userid AND a.subscriptionid=XXXXX
Comment
-
I'm still not entirely clear on exactly what I need to look at to tell if a specific user has a recurring subscription or not. I have 4 subscription levels (subscriptionid = ID 1 through 4). By the way, the script posted by Glenn above does not work - I get an error that there is an error in my SQL syntax (I do change the names of the tables to add the proper prefix that are in my database (e.g., vbb_subscriptionlog instead of just subscriptionlog. Anyway, is there a way to look a specific user and what specifically do I look for in what field to tell for certain whether they have selected recurring for any of my four subscription IDs? I really wish there was a way that I could look at someone's user record in the Admin CP and see that their subscription is recurring. That would be exceedingly helpful because I constantly get emails from members who don't remember if their subscription is recurring or not and I can't easily tell them. There must be an easy way to tell!Comment
-
What is the SQL query you are using and what is the syntax error message?Comment
widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
Comment