what is the best way to relate users to categories in a database?

Beginners - Learning PHP
#1

Hello, first off let me say I’m a newbie teaching myself without spending any money on training materials so please go easy on me as many others have not.

I’m not asking for someone to write the code for me, I’m just asking for a push in the right direction.

I have a database of users and categories, I need the users to be assigned either user rights and/or admin rights to each category and I need my PHP scripts to be able to distinguish this relationship.

right now the way I have it done is by having a “adminOf” column and “userOf” column in the users table, which contains a comma separated list of category ID’s

which I know is the wrong way to do it.

#2

The best method in my opinion is to have one column, maybe called userType then assign the user the appropriate levels that you want in userType. For example you might have three levels - public, member, admin, just simply give the user the appropriate level. This would be in your user database table. I hope I understood you correctly.

Then you could do something like the following: (Just a pseudo example)

[php]if ($_SESSION[‘user’][‘userType’] == ‘admin’) {
// Code to delete a record would go here.
}[/php]

#3

Thanks for the reply, unfortunately I wasn’t quite specific enough in my original post I apologize. I also need some users to only be admins of certain categories and have either no access or only user access to other categories. That’s why I set up the columns to determine it in my users table, but that method was met with ridicule on another site.

#4

Well this is still the best way to do it, then what you do is put in the categories table a column like createdBy, if you want the OP to be able to edit it.

Then you can do this

[php]echo (isset($_SESSION[‘user’][‘userType’] == ‘admin’ || $createby == $_SESSION[‘user’][‘username’]) ? 'Edit : NULL;[/php]

or you could simply do this
[php]echo (isset($_SESSION[‘user’][‘userType’] == ‘admin’ ) ? 'Edit : NULL;[/php]

Giving access only to the administrator, you can do this with add a page though with different code (maybe a button?), delete or what have you. Again this is kind of pseudo code. You can have your access levels (userType) reflect the categories, for example have 'baseball" for baseball categories. Go Tigers! ;D
[color=green]You have the access level check in the user’s table, for that is where the access is granted or denied, I learned that a long time ago before PHP when I ran a BBS (Bulletin Board Service) Actually, you can have both the categories in the user’s table and the categories table and cross reference it, now that I think of it. Anyway you do it would be just fine.

#5

Can you export the DLL of both of your current tables and paste it here and I think [member=57087]Strider64[/member] and I would be able to come up with a better design for you.

So basically just show us the current create statement for the 2 tables you have.

#6

Ok, so basically my code works perfectly for what I want to do, so I don’t actually have a problem that needs helping. That being said, the reason I came here is because people on another site ridiculed my methods but offered no insight as to why the way I was doing it was wrong/bad so I would love to hear your opinions.

Here’s my tables.

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `thumbnail` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=53;


CREATE TABLE IF NOT EXISTS `users` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(100) NOT NULL,
  `type` tinyint(1) NOT NULL,
  `name` varchar(50) NOT NULL,
  `userOf` varchar(100) NOT NULL,
  `adminOf` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userOf` (`userOf`),
  KEY `adminOf` (`adminOf`),
  KEY `username` (`username`)
)ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=25;

The only relevant parts are the primary id of the categories table and the “userOf” and “adminOf” fields on the users table. Those two columns contain a comma separated list of the category ids.

I have this function to create two arrays which I then call at the beginning of each page it’s needed:

[php]
function checkUser_cat($userid,$type) {
global $con;
$query = “SELECT {$type} FROM users WHERE id = ‘{$userid}’”;
$users = mysqli_query($con, $query);
$user = mysqli_fetch_assoc($users);
$array = explode(",",$user[$type]);
mysqli_free_result($users);
return $array;
}

$userOf = checkUser_cat($_SESSION[“userid”],“userOf”);
$adminOf = checkUser_cat($_SESSION[“userid”],“adminOf”);
if (!in_array($catId,$userOf)){
$_SESSION[“msg”] = “You are not authorized to view this page, please log in.”;
redirect_to(“index.php”);
}
[/php]

anyone who has the category id in the adminOf column also has it in the userOf column, but not necessarily vice-versa.

after those arrays are defined I use “if in array” statements to determine what content will be generated for users or admins.

So basically my question is, is this a poor way of doing this?

Thank you for your input.

#7

If it was me, i would do three tables. I didn’t test any of the code below - It’s just me jotting off the top of my head.

When you break it into that third table, you can then do a lot more query wise and you’ll always be able to get information easier.

[php]CREATE TABLE IF NOT EXISTS categories (
cat_id int(4) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
thumbnail varchar(50) NOT NULL,
PRIMARY KEY (cat_id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=53;

CREATE TABLE IF NOT EXISTS users (
user_id int(4) NOT NULL AUTO_INCREMENT,
username varchar(50) NOT NULL,
password varchar(100) NOT NULL,
type tinyint(1) NOT NULL,
name varchar(50) NOT NULL,
PRIMARY KEY (user_id),
)ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=25;

CREATE TABLE IF NOT EXISTS users_categories (
user_id int(4) NOT NULL,
cat_id int(4) NOT NULL.
access_level bit NOT NULL,
PRIMARY KEY (user_id','cat_id','access_level),
)ENGINE=MyISAM DEFAULT CHARSET=utf8;[/php]

[php]function checkUser_cat($userid,$type,$catId) {
global $con;
if ($type == “userOf”) {
$query = “SELECT 1 FROM user_categories WHERE user_id = ‘{$userid}’ and cat_id = ‘{$catId}’ and access_level=0”;
} else {
$query = “SELECT 1 FROM user_categories WHERE user_id = ‘{$userid}’ and cat_id = ‘{$catId}’ and access_level=1”;
}
$users = mysqli_query($con, $query);
if (mysqli_affected_rows($con) > 0)
{
return true;
}
return false;
}

if (!checkUser_cat($_SESSION[“userid”],“userOf”, $catId)){
$_SESSION[“msg”] = “You are not authorized to view this page, please log in.”;
redirect_to(“index.php”);
}[/php]

#8

It’s probably way above your level right now, but what you want to learn is called a party data model.

Sponsor our Newsletter | Privacy Policy | Terms of Service