Since the OP haven’t done anything to his database design here we go… maybe he just needs a concrete example to understand what should be done.
Create database and add some demo data (users, plans):
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
-- Dumping database structure for videoservice
CREATE DATABASE IF NOT EXISTS `videoservice` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `videoservice`;
-- Dumping structure for table videoservice.plan
CREATE TABLE IF NOT EXISTS `plan` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`level` tinyint(2) unsigned NOT NULL,
`description` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- Dumping data for table videoservice.plan: ~2 rows (approximately)
/*!40000 ALTER TABLE `plan` DISABLE KEYS */;
INSERT INTO `plan` (`id`, `level`, `description`) VALUES
(1, 1, 'Plan one'),
(2, 2, 'Plan two'),
(3, 3, 'Plan three');
/*!40000 ALTER TABLE `plan` ENABLE KEYS */;
-- Dumping structure for table videoservice.user
CREATE TABLE IF NOT EXISTS `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- Dumping data for table videoservice.user: ~2 rows (approximately)
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` (`id`, `username`) VALUES
(1, 'Bob'),
(2, 'Dave'),
(3, 'Julia');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
-- Dumping structure for table videoservice.user_plan
CREATE TABLE IF NOT EXISTS `user_plan` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL DEFAULT '0',
`plan_id` int(10) unsigned NOT NULL DEFAULT '0',
`plan_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`expiry_date` datetime NOT NULL,
`paid` tinyint(1) unsigned NOT NULL,
`price` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK1_user_id` (`user_id`),
KEY `FK2_plan_id` (`plan_id`),
CONSTRAINT `FK1_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE,
CONSTRAINT `FK2_plan_id` FOREIGN KEY (`plan_id`) REFERENCES `plan` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
Add subscription plans:
/*
Add some plans to users
level 1 plan price = 10
level 2 plan price = 20
level 3 plan price = 30
*/
/* Add all 3 levels for Bob */
insert into user_plan (user_id, plan_id, expiry_date, paid, price) values(1,1,'2018-06-10 12:00:00', 1, 10);
insert into user_plan (user_id, plan_id, expiry_date, paid, price) values(1,2,'2018-06-10 13:00:00', 0, 20);
insert into user_plan (user_id, plan_id, expiry_date, paid, price) values(1,3,'2018-06-10 14:00:00', 0, 30);
/* Add level 1 and 2 plans for Dave */
insert into user_plan (user_id, plan_id, expiry_date, paid, price) values(2,1,'2018-06-10 12:00:00', 0, 10);
insert into user_plan (user_id, plan_id, expiry_date, paid, price) values(2,2,'2018-06-10 13:00:00', 0, 20);
/* Add level 1 plan for Julia */
insert into user_plan (user_id, plan_id, expiry_date, paid, price) values(3,1,'2018-06-10 12:00:00', 0, 10);
Do some stuff with the plans (SQL):
/* Get all plans for Bob, if result set is empty, Bob does not have plans */
select * from user_plan up
join plan p on p.id = up.plan_id
where up.user_id = 1;
/* Get level 2 plans for Bob */
select * from user_plan up
join plan p on p.id = up.plan_id
where up.user_id = 1 and p.`level` = 2;
/* Get total fees for Bob */
select sum(up.price) as total_fees from user_plan up
join plan p on p.id = up.plan_id
where up.user_id = 1;
/* Get all users (and related data) that have level 2 plan */
select * from user_plan up
join plan p on p.id = up.plan_id
join `user` u on u.id = up.user_id
where p.`level` = 2;
/* Remove level 1 plan from Bob */
delete from user_plan where user_id = 1 and plan_id = 1;
/* Get all user plans that expires after 2018-06-10 13:00:00 */
select * from user_plan where expiry_date > '2018-06-10 13:00:00';
And simple example in PHP:
<?php
try {
$pdo = new PDO('mysql:host=localhost;dbname=videoservice', 'dbuser', 'dbpass');
/* Get all plans for Bob, if result set is empty, Bob does not have plans */
$userId = 1;
$stmt = $pdo->prepare('select * from user_plan up
join plan p on p.id = up.plan_id
where up.user_id = :user_id;');
$stmt->bindParam(':user_id', $userId);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_OBJ);
//var_dump($result);
} catch (Exception $e) {
exit($e->getMessage());
}
$userTotalFees = 0;
echo '<h2>Bobs plans</h2>';
if (empty($result)) {
echo 'User does not have any plans.';
} else {
foreach ($result as $obj) {
$paid = ($obj->paid) ? 'Yes' : 'No';
echo $obj->description.' | price: '.$obj->price.' | paid: '.$paid.' plan level: '.$obj->level.' expires: '.$obj->expiry_date.'<br/>';
$userTotalFees += $obj->price;
}
echo '<br/>Total fees: ' . $userTotalFees;
}
