How to access particular database based on user roles ID using php and mysql?

Question

Hi I have model table where i have mentioned the user role ID.

What i want to know is

when the user login i want to check the user data with the model
table and i want get the user role ID.
after getting the role id i want to bind or assign the particular database to the user based on the user role's ID.
Each client want to be assigned with individual database.

What i have

I have more than one client and a single client will have 10 to 20 user
I have a Native mobile application on front end and i use PHP on backend.

Till now i used one database per app so i have no idea about dynamically assigning database to individual client/user.

In my case

I am planing to rent AWS, or Microsoft Azure where i want to assign individual database to individual clients.

Client - is an organization. User- are people who use my application within thin the organization.

Thanks in advance....

are you sharing user data across multiple different apps? Not only does this sound like a security risk, it also might violate a few data protection principles. I would almost always use a separate database for each app. — Juakali92
– Juakali92, Commented Mar 13, 2018 at 15:43
Your explanation is a bit confusing. What are the relations between users, clients and apps? Where are the users stored? What does a "user role" actually mean? What actual data lives in the different databases? — M. Eriksson
– M. Eriksson, Commented Mar 13, 2018 at 15:43
my app will be used by different client-simply they are organization and user-are the people within the organization — Vignesh
– Vignesh, Commented Mar 13, 2018 at 15:46
@Juakali92 - no no i want to provide individual database for individual clients — Vignesh
– Vignesh, Commented Mar 13, 2018 at 15:48
Why do you need to use different databases? If they all use the same app, I would simply have one database. Then you have a client column in the users table, defining what client they belong to. Then you don't need to set up new databases to add more clients. — M. Eriksson
– M. Eriksson, Commented Mar 13, 2018 at 16:02

GrowingBrick · Accepted Answer · 2018-03-13 19:12:04Z

1

If I understand it correctly you just need only open a new database link connection with the database of the user's company. If all the databases are on the same host machine then it is trivial depending on what PHP library you are using. For example with MySQLi:

Same Host:

$databaseLink->select_db("company_of_the_user_db_name");

Other host:

$databaseLink->close();
$databaseLink = new mysqli("newhost", "newUser", "newPass");
$databaseLink->select_db("company_of_the_user_db_name");

Where $databaseLink is your old db link where the users table is stored.

Almost all PHP Database libraries have a function like select_db, otherwise you can close the connection and open a new one.

answered Mar 13, 2018 at 19:12

GrowingBrick

7514 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vignesh Over a year ago

yes you are so close. Ok since i am using android app i can not maintain session so 1. how can i implement this method for multiple user scenario? 2. Does binding the user database dynamically affect other users? please correct me if i am wrong..

GrowingBrick Over a year ago

This already handles multi user scenario, you just need to take DB credentials or db name for the specific user for example from the common table. When you close the connection and open a new one in a PHP script it doesn't affect anyone else in fact hundreds of users could run the same script making totally different connections without affecting each other

Vignesh Over a year ago

Ok i am already working on it i'll update once its completed. Thanks @GrowingBrick

Collectives™ on Stack Overflow

How to access particular database based on user roles ID using php and mysql?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related