How to grant permission with implode or explode?

I have been experimenting with the implode and explode function and I think I am getting closer to achieving what I want to achieve but I thought that the following code should work but it keeps locking my out of my site but I have granted myself a Level 1 and Level 2 permission in the table as shown below:

Here are my codes:

<!DOCTYPE html>
   <meta charset="utf-8">
   <link rel="stylesheet" type="text/css" href="style.css">



if(!isset($_SESSION['u_uid'])) {
  header("Location: index.php?notlevel1user");
} else {
  include_once 'includes/dbh.php';
                            $sql = "SELECT * FROM memberships WHERE user_uid = ?;";
                             // Create a prepared statement
                            $stmt = mysqli_stmt_init($conn);
                           //Prepare the prepared stement

                           if (!mysqli_stmt_prepare($stmt, $sql)) {
                              echo "SQL statement failed";

                             } else {
                           //Bind parameters to the placeholder
                               mysqli_stmt_bind_param($stmt, "s", $_SESSION['u_uid']);
                               $result = mysqli_stmt_get_result($stmt);

                               while ($row = mysqli_fetch_array($result)) {
                                $subscriptionplan = $row['subscriptionplan'];
                                 if ($subscriptionplan === 'Level 1') {
                                   header("Location: level1videos.php");

                                 } else {
                                      header("Location: index.php?level1=noaccess");

I have used 3 equal sign to denote a string, is that correct? Do I need to use explode or implode? From my understanding, implode is to convert array to strings and vice versa…

The problem here is you are putting multiple values in the same column. Do not do it. Not now, not ever. You need to create another table with incrementing ID’s and level description text along with a joiner table and use a foreign key to relate them. (The “Relational” in Relational Database). You need to learn about “Database Normalization”.

The database is the foundation of all the code you will ever write against it. You need to get that right first.


The three equals means a strict comparison, so there will be no type juggling. So you must compare a string with a string, or a integer with a integer, etc, to get a true result.

Neither should be used for this. You need a proper database design.

1 Like

Your code is:

if ($subscriptionplan === 'Level 1') {

but your data is “Level1, Level2”. How would you expect those two strings to be equal?

As everyone else says, stop storing more than one value in the same column, it just makes everything (inserting, updating, querying) more complicated than it needs to be.

Now that you’ve got the data inserting and are coming to start to use it, I think you will encounter more and more situations where the database design is going to cause more complex code. If you had a decent membership table layout, this job would be

select * from memberships where subscriptionplan = "Level 1" and user_uid = ?

No results means no level 1 sub.

1 Like

I disagree with the folks who think you need a one to many database schema here. Putting multiple values in one column is fine as long as you don’t need to search the database by one of those values. Setting up two tables is overkill.

In your case:

$levels = array_map('trim', explode(',', $row['subscriptionplan']));
if (array_search('Level 1',$levels) !== false)) {
    die('Found it');

Note that the array_map trim is to get rid of any leading/trailing whitespace that might creep in.

A slightly easier approach is to use php’s serialize and unserialize function to just directly store an array of values into the column. So you would have:

$levels = unserialize($row['subscriptionplan']);

Everything gets harder. You can’t just simply grant a permission to a user by using a simple INSERT, you have to get the current permissions first, deserialize, add the new privilege, serialise and then UPDATE. This is also susceptible to raise conditions, whereas a single INSERT is less so.

Similarly removing a permission you have to do the entire update dance again instead of a simple DELETE.

And indeed fetching all users with a specific permission is a royal pain in the @$$.

I’m getting tired just thinking about all the lifting you have to do that you wouldn’t have if you just spent a few more minutes setting up the database properly.


NO, no and no! Pointless to even argue about it.

1 Like

Yes, that can “work”. But it goes back to my earlier “as long as the table is small”.

That approach means SELECTing every row regardless of what CSV values are in it and then using PHP to find the ones that meet the criteria.

It also makes UPDATEs of CSV values more difficult too.

It can be done, and the uneccessary resource use might be acceptable. But even if there is not a “need” to have a more efficient database structure, why not make things easier? eg.

  • a lot of PHP functions, conditionals, memory use
  • a lot of value shuffling in the database for UPDATE - SELECT queries that return result rows that will not be used
  • more PHP functions, conditionals, memory use

as opposed to using less PHP functions, conditionals, memory use and faster database transactions.


Maybe, but in this application, the OP does. Of course you’re correct, there are many ways including those you suggest, or strstr(), or a change to the query, but it still complicates matters. And then you have to add that he needs to find which of the “sets” of columns are required for a given subscription, and it all blows up.


Well no. The OP is querying by a specific user id. The fact that they are looping the results is a programmer error.

Should arrays be stored in database columns? How about an example from popularity? The Symfony framework is perhaps the most widely used php framework in the world. The Doctrine ORM is also the most popular database abstraction library out there.

So here is an example of a Symfony user object. In particular, we have:

 * @ORM\Column(type="array")
private $roles;

See that type=array? That means that $roles is automatically serialized in the database. $roles has the same function as subscription plan.

So we not only have a gazillion apps out there using (gasp!) serialized data but pretty much every database abstraction library supports arrays.

Now it is a bit hard to tell just from the screen copy but the OP does seem to have multiple related columns for storing stuff about the subscription plans. If that is indeed the case (and lets face it, the OP does not exactly have a history of clarity) then a one to many relation is indeed called for.

My recommendation was based on the actual question and not based on what the question might be.

That’s pretty much the crux of it all isn’t it. Using serialisation in a database abstraction layer is (too?) easy, because you never have to do the heavy lifting yourself, it’s all taken care of “behind the screens”.

The OP doesn’t have a database abstraction layer, and code that is fairly complicated already, given what it does, so they really don’t need any more accidental complexity due to serialization.


never say never.

People typically don’t know what they want or will ever need in the future. It is best to anticipate and plan for some basic needs in the future without the need to re-engineer a solution.


You are spot on here! That is my intention! Because I tried it with separate columns, with subscription 1, 2 and 3 and have so many if statements that I don’t think it will work… I have to check if the user is not level 1 || Level 2: not level 1 && Level 2 etc… Sorry for not making it clear in my description…

thanks for the useful information here… I should read about array_map to trip whitespace? I guess this just helps to prevent sql injection?

Actually, they might have a point to make because I can’t figure out how to update the user, when there are more than one strings in one column, it might delete all strings instead of just level 1…

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 NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;

-- Dumping database structure for videoservice
USE `videoservice`;

-- Dumping structure for table videoservice.plan
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `level` tinyint(2) unsigned NOT NULL,
  `description` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)

-- 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
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)

-- 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
  `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',
  `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,


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 = up.plan_id
where up.user_id = 1;

/* Get level 2 plans for Bob */
select * from user_plan up
join plan p on = 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 = 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 = up.plan_id
join `user` u on = 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:

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 = up.plan_id
                            where up.user_id = :user_id;');
    $stmt->bindParam(':user_id', $userId);
    $result = $stmt->fetchAll(PDO::FETCH_OBJ);

} catch (Exception $e) {

$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;

And that’s why, here, I think you should have a separate row in the memberships table for each subscription. That way, your subscriptionplan column will only ever have one value in it. It can be done the way you’re doing it, I’m sure, but the code is very complicated.

They certainly do have a point. My solution was focused on just having one column with multiple values. Updating is actually not hard.

But as many other people have explained, you actually need two tables here.

memberships - id,user_uid
membership_subscriptions - id, membership_id, plan, date, fee, expires etc.

The subscriptions table will contain one row for each subscription a member has and is linked back to the memberships table via membership_id.

It might take quite a bit of effort to implement this the first few times you do so but it is one of the core features of sql databases. So consider this to be the “right” way.

Of course one the other hand, doing it the way you are doing could work.

Your basic problem is that you are still in the stage of mixing all your code into one file. Imagine creating a membership.php file and putting a function in it:

function does_member_have_level($memberId,'Level 1') {
   // Do the necessary queries and return true or false

That would move a bunch of stuff out of your main file and make things easier to read and maintain.

Boy, talk about going all out on a reply. OP, TenDoLLA has gone waay out of his way to hand you a corrected DB and done all the work for you.

If you do not adopt the correct method then I and probably others are done helping you.


This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.