I want to create a simple subscription based account management on a PHP and MySQL driven website. I need to keep track of certain types of user accounts and the lengh of the subscriptions. I want the user to be able to select a "subscription package" (bronze, silver, gold) and the timespan (one week, one month, one year) he wants his subscription to last.However, higher rated subscriptions (gold > silver > bronze) should be given priority over lower rated ones. That means, if a user should have both a silver and a gold subscription, the timespan of the gold subscription should obviously be used first and only when it has run out, should the silver one be used.To store subscriptions internally, I created a table:\[code\]INT idINT user_idINT typeDATETIME startDATETIME end\[/code\]My question is: How can I find out if a user account has a valid subscription at a certain moment in time and what type that subscription is? Most importantly I would be interested in how to dynamically add more subscriptions when there's already one subscription runnnig.More precise example:
- user buys 5 days of bronze on day 1 -> subscription "bronze" runs from day 1-5.
- on day 2, user decides that he wants "silver" additionally and buys another 5 days of that
- situation now: bronze (day 1), silver (days 2-6), bronze (days 7-10)