Announcement

Collapse
No announcement yet.

How can I tell if someone has recurring subscription?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How can I tell if someone has recurring subscription?

    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?

  • #2
    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 5th Oct '15, 4:17pm.


    Comment


    • #3
      Originally posted by Replicant View Post
      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
      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
      where XXXXX is the id of the recurring subscription. You can easily get the subscription id in AdminCP > Paid Subscriptions > Subscription Manager. If you open a subscription, you can see this in the Subscription Manager:

      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 Glenn Vergara; Tue 6th Oct '15, 10:02am.

      GIPHY for vB5 AutoLinker Auto-Create Flag Report Topic Social Icons in Postbit Clear Cache Cron DragDrop Upload Topic AJAX AutoUpdate Custom Avatars Selector Stop Links in Posts...and more!

      Comment


      • #4
        Originally posted by Glenn Vergara View Post
        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
        where XXXXX is the id of the recurring subscription. You can easily get the subscription id in AdminCP > Paid Subscriptions > Subscription Manager. If you open a subscription, you can see this in the Subscription Manager:

        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
        thanks!

        Comment


        • #5
          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


          • #6
            What is the SQL query you are using and what is the syntax error message?

            GIPHY for vB5 AutoLinker Auto-Create Flag Report Topic Social Icons in Postbit Clear Cache Cron DragDrop Upload Topic AJAX AutoUpdate Custom Avatars Selector Stop Links in Posts...and more!

            Comment

            widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
            Working...
            X