How to grant permission with implode or explode?

PHP
17

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;
}
6 Likes