Change result based on who is logged in

Hello I am trying to figure out how I can return filtered MySQL database results based on who is logged in to the php website.

For example “logged in users can only see employees from their admin assigned departments”.

I have a user login table, departments table and employees table.
Employees are assigned to one department.
Logged in users can be assigned to see one or more departments, and only see employees from those allowed departments.

In the users login table I have a super admin user who needs to be able to set which departments a sub logged in user can see.

So a subuser can have multiple departments assigned to them.

What’s the best method to add multiple departments from a list to each user, do i create a look up table? If so how should it be structured?

How should i format the SQL search to filter the departments allowed when the user logs in?

Any help in figuring out how to do this would be very much appreciated as I have no idea how to design or develop this scenario from my current tables.

Thanks.

Well, first, welcome to the site!

There are hundreds of ways to handle this. Usually, you would assign a level field to a user. Perhaps call it “user_level” or something you like. Then, another table would list all the departments that each level can “see”. Then, when you set up the view for that user, first you would know their level from the login script. Then, load all the departments they are allowed to view. Then, list them.

But, all of this depends on your needs. First, you need to define the possible levels. Let’s say, you have 100 users and 100 departments. Will all 100 users be assigned different departments to them? If so, you would need to create an array for each user and store the entire array into the user-level field. If each user can have different departments assigned to them, this is a good way to go. If you have a set amount of departments assign to certain users, then a second table is the way to handle it. So to help you further, some questions…

Are you assigning departments one by one to each user?
Are you assigning groups of departments to each user?
Do you ever need to alter the list to add/delete one department from a user?

Once you think out the use of levels and assignments, it should be easy to create the db structure for it.

Thank you ErnieAlex for your kind reply.

I will try to answer your questions…

Users can have multiple departments that they manage, I have not decided how those departments get assigned to the user, I am open to suggestions.

Don’t know about groups, I guess that would make sense that a user was assigned a single group.

Yes, assume a user may move and then have new or extra departments to oversee.

Hope that answer your questions, thanks again for your help!

Well, database design should be done before you start programming the logic. This is a very important step. Now, you said you do not know how to assign a department to a user. What type of business is this for? If a user can manage more than one department, you need to be able to mark their user record with ALL of the departments they can view. In your ADMIN pages, you could create a USERS section and in there list all of the possible departments that any one user can access. You could just use an array of department numbers in one of the user fields to hold the departments that the one user can access. I guess there are more questions to ask.

How many users do you expect?
How many departments do you expect?
What type of business is this for?

It is hard to help you design a database layout without knowing more about what you want to do.
You can save arrays into a database with ease. You just use the PHP function serialize() to store them.
When you retrieve it, you use unserialize() function to get them back. Answer these new questions and
then, I can help you further.
PS: is this for a class project? If so, the teacher probably gave you a list of needs or items to do. You can some of that if it is for a class.

No, I am learning PHP, javascript and SQL at home during the lockdown and I have been doing html and CSS stuff off and on for a long time but only just trying to understand server-side programming now…

Well at the moment the user’s table and the department’s tables are not linked.

So any user can select any department from a drop-down box list of departments ajax call.
And the Ajax call populates the results.
So no real logic involved.

Now the ‘brief’ is to make the users have restricted access to only departments in the drop-down they are assigned to have access to.

At the moment its only 100 users in the development database, but we should assume that it will grow significantly.
again departments could be 200 or 500.

A chain of stores, with local managers (users) who want to access their own store departments.
And regional managers who oversee several stores.
But other regional managers who can’t see other store departments.

Okay, so the serialize() option is something I am going to have to learn!

Thanks for taking the time to help me.

This tells me a lot about your project. Glad to hear you are learning PHP and SQL, etc. Nice to hear you are interested in those…

So, you have a user’s table set up already in a database. Oh, wait… Are you using SQL, MySQLi or PDO? I suggest learning PDO because it is much more secure than the others. And, it is extremely handy when doing multiple updates as you prepare the query ahead of time and then just fill in the data. Here is a great link to a PDO tutorial that tells you everything you need. ( Also, most professional programmers are using PDO now. ) PDO Tutorial

Now, back to the layout. You have a USERS table with information of 100 users which may become many more. In that table, you should have a field with one or two possible layouts. Since any one user can access different departments, I feel it would be best not to use a canned user-level system. Normally, for this type of system, you would assign a user-level and then have another table that listed the user-level and all of the departments they can access. This works for most layouts. But, since you need to pick and choose which department a user can see, it would make the user-level table complicated. I think just keeping an array of the departments that any one user can view would be easier to handle.

Therefore, in the users table, you would need to add a field to hold the array. This field could be very large if the user is allowed to view large numbers of departments. The array itself would need to hold the store-id department-id. Sometimes when you develop a database layout, you must think from the user’s standpoint. If I was a regional manager, I would like to be able to select a store, then a department to view. Also, I would expect to be able to see reports based on any one store with all of the department totals. To do that, you would need an array that includes a list of all stores I would be allowed to visit and all departments inside each store. If I was a local manager of a huge store, I might only have half or a quarter of all the departments under my direct control. (Some stores have several managers with a general manage above all of them.) Therefore, my array would only have my one store in my list, but, a number of departments listed under that one store entry. Does all this make sense to you so far?

Now, in an array, you can have a multidimensional array with the main index called, let’s say, “stores”. Under that index you would have all of the stores that this user can view. Then, under each store, you would have another array that would list all of the departments this user can view inside this one store. So, to access it you could do a simple lookup like: $depts = $stores[$current_store][$current_dept]…
This is one possible way to handle this.

Another way, which is what a lot of professional programmers would suggest is to create a table of access lists. The advantages to this way is to be able to do super fast queries to acquire the lists when needed. To do this second way, you would create a simple table and call it something like “permissions” or “access” or… The structure for this would be simple, just these items:
id (for indexing)
user_id (to point back to the user)
store_id (to point to the stores table)
depart_id (to point to the departments table)
All entries would be INT(11) format and easy to set up. Handling it this way, you can build very small queries to access any one user’s access-levels. Just SELECT * FROM permissions WHERE user_id=… This would get all the info in one small query for any one user. Then, of course, you can alter the query to get just what you need for displaying like SELECT store_id FROM permissions WHERE user_id=… This would get all the stores that any one user is allowed to visit. And then a simple FOREACH would let you add them into a drop-down for selection of stores. And, once a user has selected a store, you could run a quick AJAX call to load a second drop-down with SELECT depart_id FROM permissions WHERE user_id=… AND store_id=… And, that would get the data to load into the second department drop-down.

I think for your use the second version would be easier to work with and actually more professional. If you were working with less users, stores and departs, the array might work easier, but, with these amounts the real database layout is better. ( And, easier when viewing data, too. )

Well, there are two ways to handle this. Gives you a lot to think about. Good luck with your design!
Ernei

As Ernie has pointed out, this is a database design problem. Once you know that you can find a lot of resources to help with the topic. This article seems like a decent start.

Here are a couple more questions which I think will influence how you design this database.

  1. Is there a difference between a “user” that can log in to this system, and an “employee”? Can employees log in?
  2. Do all stores have the same departments, or can they be different for each store?
1 Like

Hi, Thank you for the replies.

I started with examples of connecting to a database from code I found on the internet, I then discovered I was using the old and out of date examples and switched to PDO, but more recently I have discovered diffrent php frameworks that seem like the way to go?
I am currently testing with Codeignitor as it has been the easiest for me to get working. I have tried a few other frameworks but ran into problems getting them to work.
I was using an sqlite database but now I am using a MySQL database.

The basic tables have been created but can be modified if need be.

I guess I really need to understand the process or logic of how this will all work, and I don’t want to spend days learning something then finding I am doing it the old discontinued way once again!

I am thinking that Ernie’s suggestion of ‘second version’ makes sense, just the question of how I populate the permissions table, not figured that out yet.

Thanks also to Skawid for the link I will check it out.
to answer your questions.

  1. Users table is separate from the employee table. only users can log in, some users may also be in the employee table.

  2. At the moment I just have a table of stores and another table of departments. In my simple thinking, I was planning on every department to be unique and didn’t even consider filtering departments by the store until Ernie suggested it, and now that makes sense but has now added to the logic problem!

Thanks again!

Another thing to think about is that it is very easy to JOIN two or more tables into one result. You can easily query against the store table and have the department table linked to it so that you have both tables in the results. This is how you do complex reports without any real code.

As far as using a “framework”, you may have issue updating it to be exactly how you want it. I have assisted many beginners with WordPress and other frameworks and sometimes the fix for what they want to do is deep inside the code. In some ways it is better to just create your own website as long as you learn all of the code needed.

If you have every department in a “dept” table, normally one field would be the store_id where you would attach it to one store. That is easy enough. Since most dept’s would have their own manager, there would be also a manager_id field, too. Which would of course, link back to the employee table. All of these cross-references would make queries easy and make it very easy as I said before to query data as needed.

If you have all the departments in every store in a table, you can use that list to assign them to any user. You would just query all the stores and display them and when selected query all the departments in that one store, and finally add a button to add a dept to a user’s permission list.

Lastly, you would need an ADMIN page that lets you get to all the database tables. An editor for creating deleting and updating each table. Store, employees, departments, permissions… You do not need a separate users table since every user must be an employee. Unless you have other people who are not employees but need to view the data. The “users” table as you call it could really be the “permissions” table as I called it. Sometimes it is hard to think what someone else has in their mind over these sites like this one. I just keep throwing out ideas to help you design it all…

Good luck!

A framework is a very useful tool to simplify your work and make it more difficult to make certain mistakes, but it won’t help you until you have a solid idea of what your data looks like.

The “Some users are employees, some employees are users” makes for an interesting problem.

  • You could have them in separate tables, but then you have a data redundancy problem - for a user who is an employee, do you store their name in the employee table or the user table?
  • You could have them in the same table and populate a “username” and “password” field for those users that are able to log in, but you have to remember this when accessing lists of users or employees.

I would go for the second option here as I think data integrity is a bigger problem than potentially complicated read queries.

Regarding the stores > departments problem; if noone’s asked for stores, I’d ask the stakeholders if they need it. Better to find out for sure now than halfway through building your system.

This is really not a complicated situation. You all are missing something important. This is not “users” and “employees”. What we are talking about is people. People who have various types such as users, employees, or what ever.

Start with a “people” table. Then you can assign their unique id to your users table, employees table, freaks of nature table or whatever. Very simple and infinitely scalable.

Tables:

people - people_id | first_name | last_name | other_people_data
employees - employee_id | people_id | other_employee_data
users - user_id | people_id | username | password | last_login
department list - department_id | department_description
people_to_departments - people_id | department_id

From your project description, you would really benefit from learning and implementing the “Party Model”. It solves everything that has been talked about. No need to re-invent the wheel.

Thanks, I will google ‘party model’.
Part of the problem I have in finding a solution myself through google is in knowing what the development world out there actually names the problem, now I know… Party model!

Sponsor our Newsletter | Privacy Policy | Terms of Service