Linking admins and employees dashboard based on company

Hello,

I am currently working on a project for my course in university. I am developing a system in which admin can manage and upload videos, and employees can view the videos and rate them. I have two login link: one for admins and the other for employees. How can I possibly restrict employees to only able to watch videos based on their organization they belong to?

For instance: John Doe is an admin for Apple and posted a video on the dashboard. Mary and Peter are employees for Apple, and Susan works for Microsoft. How can I make sure Mary and Peter are able to watch the videos from John Doe while Susan are not able to access the videos from John?

I have the following columns for the two tables in my database:

admins [“adminsId”, “adminsName”, “adminsEmail”, “adminsUsername”, “adminsPassword”]
employees[“employeesId”, “employeesName”, “employeesUsername”, “employeesPassword”]

Should I create another data tables name company with columns [“companycode”, companyname"]?
What is my next steps? Thanks in advance.

You should only have ONE login system, with one user table. When someone logs in, you are authenticating who they are, not what permissions they have or where they work. You would only store the user’s id in a $_SESSION variable to identify who the logged in user is. You would query on each page request to get any other user information, such as their permissions or company information. The reason for doing this is to insure that any change made to these user values will take affect ‘automatically’ on the very next page request, without requiring the user to log out and back in.

The rest of what you are doing comes down to database normalization. The following are some general notes that may be of help -

user table - id, first_name, last_name, email, password
1, John
2, Mary
3, Peter
4, Susan

company table - id, name
1, apple
2, microsoft

permission table - id, name
1, employee (general staff)
2, admin

comnpany_user table - id, company_id, user_id
1, 1, 1 - apple, john
2, 1, 2 - apple, mary
3, 1, 3 - apple, peter
4, 2, 4 - microsoft, susan

permission_user table - id, permission_id, user_id
1, 2, 1 - admin, john
2, 1, 2 - employee, mary
3, 1, 3 - employee, peter
4, 1, 4 - employee, susan

company_media table - id, company_id, title, description, filename
1, 1, some_title, some_description, some_filename

On each page request, query to get any other user information, such as their permissions and company information. Note: any database example code uses the much simpler PDO database extension.

session_start();
// is the current user logged in
if(isset($_SESSION['user_id']))
{
	// get user's permission_id (and permission name if desired)
	$sql = "SELECT permission_id FROM permission_user WHERE user_id = ?";
	$stmt = $pdo->prepare($sql);
	$stmt->execute([ $_SESSION['user_id'] ]);
	$user['permission_id'] = $stmt->fetchColumn();
	
	// get user's company_id (and company name if desired)
	$sql = "SELECT company_id FROM company_user WHERE user_id = ?";
	$stmt = $pdo->prepare($sql);
	$stmt->execute([ $_SESSION['user_id'] ]);
	$user['company_id'] = $stmt->fetchColumn();
}
else
{
	// action/message for a non-logged in user
}

For uploading a media file, the current user must be an admin. The information about the upload file would get stored in the company_media table, with the current user’s company_id.

// test the current user's permission_id
if($user['permission_id'] == 2)
{
    // any code/content specific to an admin, e.g. navigation, upload form, and upload processing code
}

In the upload processing code, after you have validated the input data and moved the uploaded file to a location outside of the document root folder, insert the uploaded file information.

$sql = "INSERT company_media (company_id, title, description, filename) VALUE (?,?,?,?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([ $user['company_id'], $title, $description, $filename ]);

For listing and viewing media files, the current user’s company_id would be used so that they can only list and view matching media files. You would dynamically output the media file, using its company_media id as the requested file input, and enforce the current user’s company_id requirement.

// get a listing of the media files the current user can view
$sql = "SELECT id, title, description FROM company_media WHERE company_id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([ $user['company_id'] ]);
$media_data = $stmt->fetchAll();

// loop to display media file information and a link to the .php file that will dynamically output the actual file, e.g. play_media.php?id=1

Any rating data would be stored in a media_rating table, using the company_media id value and the user_id to relate it back to the media file and the user who gave the rating

2 Likes

Hello @phdr,

Thank you so much for your detailed response. Love this community, that helps a lot!!

I do have one question for you. For my signup and login form, should I include the following code?

    <select name="permission">
  	<option value="admin">Admin</option>
  	<option value="user">User</option>
  </select>

Or do i have to hard code that in the database using the INSERT function? Thanks a lot.

Sponsor our Newsletter | Privacy Policy | Terms of Service