How to make user access more competent in mysql?

Question

PHP

I am writing an admin panel for content managers. In managing 10 sites, each content manager must have access to specific sites. Tell me if I'm doing it correctly or there is some more elegant solution:
There is a users table (pk: id, nickname, email)
There is a sites table (pk: url, name, roots)
pk - primary key.
Since users can have access to either one or several sites, in the roots field I write user IDs (roots) separated by commas ... And in order to get a list of sites for a specific user, I do a like% id% select, but then like can choose both 1, and 11, and 111 IDs when you do like %1%
How to more competently implement the distribution of roles and the final select for selecting the list of sites that are available to the user?

Answer 1

For a relational database, many-to-many relationships are regularly implemented through a separate link table (user_id, site_id), where this pair is also the primary key.
And using url as the primary key is wrong. If you change the domain, you will have to rebuild all tables that refer to sites.

Answer 2

You create a list of sites with true/false values:
table: id, site_name, flag
Then you either do a check on the back or on the front. See how you implement.