PhpMyAdmin Connecting Tables

[SprigusK]

Hi, so I have a database with 2 Tables. 1 table is called Users and 2nd is called Perms.
Inside table 1, I have ID, Username, Email, Password etc etc.
Inside table 2, I have isStaff, isAdmin, isMod, etc etc.

Im trying to connect the user in table 1 to the permissions they have in table 2.
ID 1 of Users table Should connect to ID 1 of Perms table.

How do I do this in phpmyadmin?

After that is done, how would I check if the user has a permission.
My Idea is to place on the page a if statement that does something like check if the user viewing the page has permission and if true echo the page, and if not, redirect them to another page.

 

[ginerjm]

You have a possibly poor db design.  It appears that you have designed the Perms table to hold 4 or more permissions on a single record.  The perms s/b all on individual records keyed by id and your query will join all the matching records using the following query

$q = "select u.id, u.username, p.perm from users u, perms p where u.Id = p.Id"

Your perms table should be simply "id, perm".  The query will return a record for every permission that the id is assigned.

[SprigusK]

If I am reading that line right, you are saying the 2nd table should be ID and Perm only. And every new Perm the user gets will show as:
ID      Perm
1        isStaff
1        isAdmin
2        isStaff
2        isMod
1         editNews

User 1 is a Staff, Admin, and has ability to Edit News.
User 2 is Staff and is a Mod.

is that correct? Any sorry ahead of time if I misunderstood. Im very new to database tables.

Also, do you know how to link the 2 tables? Do i just create a relationship between Primary u.id and foreign key p.id?
 

Edited November 10, 2021 by SprigusK

[ginerjm]

You have the design idea.  And I gave you a query to use.

I would also assign a code to the perms so that you each record doesn't have a 'literal' in it.

Users would be user info with a userid as key

Perms would be the perm names and their permcode with permid as key

User_perms would be the table that connects an userid to a permid  to retrieve a permcode and you would add the perms table to the query (thus 3 tables)

Edited November 10, 2021 by ginerjm

[SprigusK]

Thanks very much. I'll give this a shot.

[ginerjm]

Actually change this line to read:

User_perms would be the table that connects a Users userid to the User_perms userid  to retrieve a permcode and you would add the perms table to the query (thus 3 tables) to get the literal value of the user's permissions. 

May seem silly but a good practice is to avoid using literal values in tables in case they need to be modified down the road.

Edited November 10, 2021 by ginerjm