How can a MySQL database be structured to store and manage premium subscription information in PHP?

To store and manage premium subscription information in a MySQL database using PHP, you can create a table to store user information, including their subscription status and expiration date. You can then use PHP to query and update this table based on user actions such as subscribing, renewing, or canceling their subscription. 

// Create a table to store user subscription information
CREATE TABLE subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    status ENUM('active', 'expired', 'canceled') DEFAULT 'active',
    expiration_date DATE
);

// Query to insert a new subscription record for a user
$user_id = 1;
$expiration_date = date('Y-m-d', strtotime('+1 month'));
$query = "INSERT INTO subscriptions (user_id, expiration_date) VALUES ($user_id, '$expiration_date')";
mysqli_query($connection, $query);

// Query to update a user's subscription status
$user_id = 1;
$status = 'expired';
$query = "UPDATE subscriptions SET status = '$status' WHERE user_id = $user_id";
mysqli_query($connection, $query);

// Query to check if a user has an active subscription
$user_id = 1;
$query = "SELECT * FROM subscriptions WHERE user_id = $user_id AND status = 'active'";
$result = mysqli_query($connection, $query);
if (mysqli_num_rows($result) > 0) {
    echo "User has an active subscription";
} else {
    echo "User does not have an active subscription";
}

Keywords

MySQL database premium subscription PHP data structure subscription management

Related Questions