How can I tell if someone has recurring subscription?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • BasilFawlty
    Senior Member
    • Jul 2012
    • 255
    • 4.2.X

    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?
  • Replicant
    Senior Member
    • Sep 2014
    • 527

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


    Comment

    • glennrocksvb
      Former vBulletin Developer
      • Mar 2011
      • 4011
      • 5.7.X

      #3
      Originally posted by Replicant
      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 glennrocksvb; Tue 6 Oct '15, 10:02am.

      Flag Icon Postbit Insert GIPHY Impersonate User BETTER INITIALS AVATAR Better Name Card Quote Selected Text Bookmark Posts Post Footer Translate Stop Links in Posts +MORE!

      Comment

      • BasilFawlty
        Senior Member
        • Jul 2012
        • 255
        • 4.2.X

        #4
        Originally posted by Glenn Vergara
        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

        • BasilFawlty
          Senior Member
          • Jul 2012
          • 255
          • 4.2.X

          #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

          • glennrocksvb
            Former vBulletin Developer
            • Mar 2011
            • 4011
            • 5.7.X

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

            Flag Icon Postbit Insert GIPHY Impersonate User BETTER INITIALS AVATAR Better Name Card Quote Selected Text Bookmark Posts Post Footer Translate Stop Links in Posts +MORE!

            Comment

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