Permission tables relationships questions

I am thinking about doing a piano website where users can watch certain piano lessons for free. I would like to give users certain privileges such as primer level, level 1, level 2 and level 3. Only primer level will contain the free piano lessons while users can only access level 1, 2 and 3 if they have paid for it…

I have heard about using enum but someone told me about ysung foreign key… any ideas how to do this?

create a table for users, one for permissions and a cross-table so you can assign permissions to users.

Thanks for the reply but what is a cross table?

A table where you store each relation, from user to permission. Just lookup n-m-relationship.

Can I find this on youtube? Should I have another table where I can store all of my videos?

I have created another table called permissions and inserted the following values:

Is it correct and how would I cross table them?

Didn’t you try to search for it?

You can also have table that contains the relation between permission and file or video, or media.

As simplest, cross tables just contain the two related primary keys.

Are we talking about how to use a foreign key here?

How would I go about if I want to let the user subscribe to more than 1 plan? For example: he or she is at primer level by default but might want to pay for a level 1 course? Or level 2 course at the same time?

I would consider having a “users” table with an id primary key. And a “permissions” table with a composite not null user_id and a not null permission_id index.

I pasted my table that i have at the moment but still don’t understand how to give user multi permission

https://sjc5.discourse-cdn.com/sitepoint/community/uploads/default/optimized/3X/4/9/49ed87764ce7136baf7a2704e93b5cae52c03a47_1_690x141.png

This is my users’ table

Your “id access_type” table looks good as far as getting the names for the id values but I can’t think of an obvious easy was to use it for user permissions.

I would not have any “permission” fields in the users table at all.

I would do something like

users table
id - name - bunch of other fields
1 - Peter - …
2 - Paul - …
3 - Mary - …
4 - Joan - …
5 - Judy - …

permissions table
user_id - permission_id
1 - 0
2 - 0
2 - 1
3 - 0
3 - 1
3 - 2
4 - 1
5 - 3

The two tables could be JOINed on users.id = permissions.user_id to get whatever permissions a user has.

The permission table is related to admin and moderator but i am more concerned about how to give the user multiple access type…

I called it “permissions” thinking that’s what you meant by access, but the same approach could be used for an “access” table of a different name. Name your tables whatever makes sense to you. (though it’s a good idea to not use “reserved, special” words for them)

But i am still confused because right now… the user can only be either a primer level or level 1 but can’t be both

See my example. Some of the users have more than one level.

Can you give more information with regard to your example because I am not sure how to do something like…

Sam 1
Sam 2
Sam 3
Jess 1
George 1
George 2

Etc

I have heard of enum… is that a good idea?

I

I think i have an idea

I have updated my users’ table with the following information and included updated codes for my html form and signup page… but for some reason, it is not inserting the select option:

<?php
   include_once 'header.php';
 ?>
<section class="main-container">
   <div class="main-wrapper">
      <h2>Signup</h2>
      <form class="signup-form" action="includes/signup2.php" method="POST">
         <input type="text" name="first" placeholder="Firstname">
         <input type="text" name="last" placeholder="Lastname">
         <input type="text" name="email" placeholder="E-mail">
         <input type="text" name="uid" placeholder="Username">
         <input type="password" name="pwd" placeholder="Password">
         <select name="subscriptionplan1">
            <option value="none">Subscriptionplan1</option>
            <option value="primer">Primer Level: Free</option>
            <option value="level 1">Level 1: $50/month or $400/year</option>
            <option value="level 2">Level 2: $50/month or $400/year</option>
            <option value="level 3">Level 3: $50/month or $400/year</option>
         </select>
         <select name="subscriptionplan2">
            <option value="none">Subscriptionplan2</option>
            <option value="primer">Primer Level: Free</option>
            <option value="level 1">Level 1: $50/month or $400/year</option>
            <option value="level 2">Level 2: $50/month or $400/year</option>
            <option value="level 3">Level 3: $50/month or $400/year</option>
         </select>
         <select name="subscriptionplan3">
            <option value="none">Subscriptionplan3</option>
            <option value="primer">Primer Level: Free</option>
            <option value="level 1">Level 1: $50/month or $400/year</option>
            <option value="level 2">Level 2: $50/month or $400/year</option>
            <option value="level 3">Level 3: $50/month or $400/year</option>
         </select>
         <select name="subscriptionplan4">
            <option value="none">Subscriptionplan4</option>
            <option value="primer">Primer Level: Free</option>
            <option value="level 1">Level 1: $50/month or $400/year</option>
            <option value="level 2">Level 2: $50/month or $400/year</option>
            <option value="level 3">Level 3: $50/month or $400/year</option>
         </select>

         <button type="submit" name="submit">Sign up</button>
      </form>
   </div>
</section>

<?php
   include_once 'footer.php';
 ?>

This is page where informations are inserted… Everything is inserting fine except for the select option…

<?php
   
   if (!isset($_POST['submit'])) {
      header("Location: ../signup.php");
      exit();
   } else {
   	   include_once 'dbh.php';

   	   $first = mysqli_real_escape_string($conn, $_POST['first']);
   	   $last = mysqli_real_escape_string($conn, $_POST['last']);
   	   $email =mysqli_real_escape_string($conn, $_POST['email']) ;
   	   $uid = mysqli_real_escape_string($conn, $_POST['uid']);
   	   $password = mysqli_real_escape_string($conn, $_POST['pwd']);
   	   $subscriptionplan1 = mysqli_real_escape_string($conn, $_POST['
                            subscriptionplan1']);
       $subscriptionplan2 = mysqli_real_escape_string($conn, $_POST['
                            subscriptionplan2']);
       $subscriptionplan3 = mysqli_real_escape_string($conn, $_POST['
                            subscriptionplan3']);
       $subscriptionplan4 = mysqli_real_escape_string($conn, $_POST['
                            subscriptionplan4']);
   	   $user_activate = mysqli_real_escape_string($conn, $user_activate = 0);
   	   //Error handlers...

   	   if (empty($first) || empty($last) || empty($email) || empty($uid)|| empty($password)) {
   	   	   header("Location: ../signup.php?signup=empty");
   	   	   exit();
   	   } else {
          //Check if input characters are valid
   	   	    if (!preg_match("/^[a-zA-Z]*$/", $first) || !preg_match("/^[a-zA-Z]*$/", $last)) {
                header("Location: ../signup.php?signup=invalid");
                exit();
   	   	    } else {
   	   	    	
   	   	    	//Checking for valid emails
                 if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
                     header("Location: ../signup.php?signup=email");
                     exit();
                 } else {
                   if (Strlen($password) < 5) {
                   header("Location: ../signup.php?signup=invvalidlength");
                   exit();
                   } else {
                   if(!preg_match('/^(?=.*\d)(?=.*[A-Za-z])[0-9A-Za-z!@#$%]{8,20}$/', $password)) {
                       header ("Location: ../signup.php?signup=notalphanumeric");
                       exit();
                      } else {
                      $sql = "SELECT * FROM users WHERE user_uid='$uid'";
                      $result = mysqli_query($conn, $sql);
                      $resultCheck = mysqli_num_rows($result);

                      if($resultCheck > 0) {
                         header("Location: ../signup.php?signup=usertaken");
                         exit();
                      } else {
                         // Supply a random generated token for email activation

                      	$token = 'qqewreqreqwsdfdfdafcbvcQERFGHFGHGFHRETERTDF!@#$%^^()';
                      	$token = str_shuffle($token);
                      	$token = substr($token, 0, 10);

                      	//Hashing the password
                      	$hashedPwd = password_hash($password, PASSWORD_DEFAULT);
                      	//Insert the user into the database
                      	$sql ="INSERT INTO users (user_first, user_last, user_email, user_uid, user_password, user_permission, subscriptionplan1, subscriptionplan2, subscriptionplan3, subscriptionplan4, user_token, user_activate) VALUES ('$first', '$last', '$email', '$uid', '$hashedPwd', '$user_permission', '$subscriptionplan1', '$subscriptionplan2', '$subscriptionplan3', '$subscriptionplan4', '$token', '$user_activate');";

                      	$name = $first .$last;
                        $to = $email;
                        $header = 'From: example@example.com';
        
                        $subject = 'Email activation is required';
                         $message = <<<EMAIL

                        Hello $first  $last, 


                        Thanks for registering with PianoCourse101!
                        Please activate your account below before you  
                        can login. Activate your account by clicking   
                        on the following link below....

                         http://localhost/loginsystem/includes/activate.php?email=$to&activatetoken=$token

EMAIL;
       
                        mail($to, $subject, $message, $header);

                      	mysqli_query($conn, $sql);

                       // insert into permissions table

                      	header("Location: ../signup.php?signup=success");
                      	exit();
                        }
                 }
   	   	  }
   	   }
   	 } 
}


}

I will be using prepared statement in the future…

I read somewhere that it has somethin to do with an array… do I need a {}?

This is weird because when I wrote the code without the mysqli_real_escape_string… then it works… Is this because I am not using prepared statement? Just like to know why it won’t work with mysqli_real_escape_string…