Simple Machines Community Forum

Customizing SMF => SMF Coding Discussion => Topic started by: kittonium on July 01, 2018, 09:44:21 PM

Title: SQL queries to loop through all subscriptions a user has?
Post by: kittonium 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?
Title: Re: SQL queries to loop through all subscriptions a user has?
Post by: Antes on July 02, 2018, 07:35:44 AM
You can find proper query under Profile > Notifications, that section already tracks the topics & boards.
Title: Re: SQL queries to loop through all subscriptions a user has?
Post by: Illori on July 02, 2018, 07:44:36 AM
Quote from: Antes on July 02, 2018, 07:35:44 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.
Title: Re: SQL queries to loop through all subscriptions a user has?
Post by: Antes on July 02, 2018, 09:02:48 AM
Quote from: Illori on July 02, 2018, 07:44:36 AM
Quote from: Antes on July 02, 2018, 07:35:44 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.
Title: Re: SQL queries to loop through all subscriptions a user has?
Post by: Arantor 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.
Title: Re: SQL queries to loop through all subscriptions a user has?
Post by: kittonium on July 02, 2018, 08:37:57 PM
Quote from: Arantor 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.


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:


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.
Title: Re: SQL queries to loop through all subscriptions a user has?
Post by: Arantor 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.
Title: Re: SQL queries to loop through all subscriptions a user has?
Post by: Kindred 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)
Title: Re: SQL queries to loop through all subscriptions a user has?
Post by: Arantor 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.