Advertisement:

Author Topic: SQL queries to loop through all subscriptions a user has?  (Read 457 times)

Offline kittonium

  • Semi-Newbie
  • *
  • Posts: 62
SQL queries to loop through all subscriptions a user has?
« on: July 01, 2018, 09:44:21 PM »
Hi, I'm making a DLC loader for a game and for that I'm using the "smf_subscriptions" and "smf_members" tables.

My Question

how would you know if a user has a subscription?
how could I get or loop through all the subscribtions a user has?

Offline Antes

  • Evil Black Cat
  • SMF Friend
  • SMF Hero
  • *
  • Posts: 8,748
  • Gender: Male
  • Black cat rulz!
    • Antes on GitHub
    • merta on LinkedIn
    • @XinYenFon on Twitter
    • Lunarfall
Re: SQL queries to loop through all subscriptions a user has?
« Reply #1 on: July 02, 2018, 07:35:44 AM »
You can find proper query under Profile > Notifications, that section already tracks the topics & boards.


Past Project(s): [ ezPortal ]

Offline Illori

  • Project Manager
  • SMF Master
  • *
  • Posts: 49,736
Re: SQL queries to loop through all subscriptions a user has?
« Reply #2 on: July 02, 2018, 07:44:36 AM »
You can find proper query under Profile > Notifications, that section already tracks the topics & boards.

how does that answer the OPs question? they are trying to integrate another software with SMF, that in no way gives then a query they can use in their software.

Offline Antes

  • Evil Black Cat
  • SMF Friend
  • SMF Hero
  • *
  • Posts: 8,748
  • Gender: Male
  • Black cat rulz!
    • Antes on GitHub
    • merta on LinkedIn
    • @XinYenFon on Twitter
    • Lunarfall
Re: SQL queries to loop through all subscriptions a user has?
« Reply #3 on: July 02, 2018, 09:02:48 AM »
You can find proper query under Profile > Notifications, that section already tracks the topics & boards.

how does that answer the OPs question? they are trying to integrate another software with SMF, that in no way gives then a query they can use in their software.

Because that's the only logical solution/answer to the OP within the information OP shared with us. If they are doing such thing, its better to use SMF as a base, mark specific board as DLC board, and use the notification queries (edited version) to output the desired value.

If your assumption is the correct one then its not related to SMF because SMF does not query the outside so OP should be looking for stackoverflow.

3rd assumption,  if OP has external script for tracking and want to mirror it to SMF, then #1 logic still applies because that query is the thing OP should look at.


Past Project(s): [ ezPortal ]

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 69,123
    • StoryBB/StoryBB on GitHub
Re: SQL queries to loop through all subscriptions a user has?
« Reply #4 on: July 02, 2018, 09:35:02 AM »
Eh? The user buys a DLC through the paid subscriptions system and the OP wants to find out which DLCs a user has by which subscriptions they have.

Bad idea, though, way too easy to screw that up. For example, bulk subscription to cover multiple DLCs.

Better solution: each DLC has a user group, have the subscriptions add groups to users and then query the groups in the members table. Everything becomes way more reliable and you can do group purchases etc.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

Nothing turns to hate so bitter as what once was love.

Offline kittonium

  • Semi-Newbie
  • *
  • Posts: 62
Re: SQL queries to loop through all subscriptions a user has?
« Reply #5 on: July 02, 2018, 08:37:57 PM »
Eh? The user buys a DLC through the paid subscriptions system and the OP wants to find out which DLCs a user has by which subscriptions they have.

Bad idea, though, way too easy to screw that up. For example, bulk subscription to cover multiple DLCs.

Better solution: each DLC has a user group, have the subscriptions add groups to users and then query the groups in the members table. Everything becomes way more reliable and you can do group purchases etc.


I already added a few columns in the smf_subscriptions table, mostly things like Download Link, and the custom credit-based Cost (so im not really using the SMF subscription payment system), which if 0 then it would be free (most of them are planned to be free :))

what im doing right now:

  • Checking if the user+pass combo exists
  • Looping through all smf_subscriptions
  • If the cost of them is 0, then allow it to be loaded by the user

but now I need to think of something for the other part, if the user HAS a subscription without checking his membergroups, but if theres no easy way to check for user's subscription status, ill just do your membergroup idea :)
right now im going to research how SMF handles subscriptions.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 69,123
    • StoryBB/StoryBB on GitHub
Re: SQL queries to loop through all subscriptions a user has?
« Reply #6 on: July 03, 2018, 12:38:09 AM »
There were better ways of doing it than hacking up the existing structures. Good luck to you.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

Nothing turns to hate so bitter as what once was love.

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 56,966
  • Gender: Male
    • Kindred-999 on GitHub
Re: SQL queries to loop through all subscriptions a user has?
« Reply #7 on: July 03, 2018, 07:06:48 AM »
Don't bother Arantor - this is the second or third time he's done this.

He comes with a question, however, as far as I can tell, he has already determined the method he's going to use regardless of what the suggestions are in response to the apparent question (and regardless of how ill-advised he is told that his method is)
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 69,123
    • StoryBB/StoryBB on GitHub
Re: SQL queries to loop through all subscriptions a user has?
« Reply #8 on: July 03, 2018, 08:33:28 AM »
Oh, I know. It amuses me that he thinks he can achieve 'god' status, but the only true way is to understand what everything does and more importantly *why* things work the way they do.

Like the notion that tying a product directly to the subscription pretty much prevents any ability to do bulk deals or 'buy everything' all access passes, which are a very popular way to handle DLC once there is more than a couple of SKUs of it.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

Nothing turns to hate so bitter as what once was love.