Geek asks and answers

Managing your account with PHP and MySQL, adding Timespan?

I want to create a simple account management based on PHP and MySQL. I need to track certain types of user accounts and subscription length. I want the user to be able to choose a “subscription package” (bronze, silver, gold) and a period of time (one week, one month, year), he wants his subscription to continue.

However, higher nominal subscriptions (gold> silver> bronze) should take precedence over lower nominal. This means that if the user must have both a silver and a gold subscription, then the period of using the gold subscription should obviously be used first, and only when it is over should silver be used.

To keep the subscriptions inside, I created a table:

INT id
INT user_id
INT type
DATETIME start
DATETIME end

My question is: how can I find out if a user account has a valid subscription at a specific point in time and what type of subscription? Most importantly, I would be interested in how to dynamically add additional subscriptions when there is already one subscription to a subscription.

A more accurate example:

  • the user buys 5 days of bronze on the 1st day → subscription "bronze" works from day 1-5.
  • on day 2, the user decides that he wants "silver" additionally and buys another 5 days of this
  • situation now: bronze (day 1), silver (days 2-6), bronze (days 7-10)

Preferably, I would like to do most of the work inside a MySQL query, but I'm not sure if this is possible at all. If all else fails, I will have to return to PHP.

Thank you for your time!

+3
1

, , :

$query = "SELECT * ".
         "FROM subscriptions_table ".
         "WHERE user_id = $user_id ".
         "  AND start < '" . date("Y-m-d") ." 23:59:59' ".
         "  AND end > '" . date("Y-m-d") ." 00:00:00' ".
         "ORDER BY type";
$results = mysql_query($query);

. , , date("Y-m-d H:i:s")

. , , , $subscriptionRow = mysql_fetch_array($results);

, . , . , $subscriptionRow :

$adjustmentQueries = array();
while ($subscriptionRow = mysql_fetch_array($results))
{
   if ($subscriptionRow['type'] > $purchaseType) { } //do nothing
   else
   {
      $newDate = date("Y-m-d H:i:s", strtotime("+".$intNumDaysPurchased." days", strtotime($subscriptionRow['end'])));
      $adjustmentQueries[] = "UPDATE subscriptions_table SET end = '$newEndDate' WHERE id=$subscriptionRow['id']";
   }
}

foreach ($adjustmentQueries as $adjQuery) { mysql_query($adjQuery); }
+2

More articles:


All Articles