It’s time to add some refinement to the authentication and navigation systems you built in the last couple of chapters. You’ve created an attractive login screen as well as added authentication to let users into and out of your application. It’s time to go further needs to be improved. It should take in a group (or better a list of groups) for the user and only allow access if the user is in the permitted group, such as an administrator group.
You also have basic navigation, but again, there are some needed improvements: users in certain groups should see an option to administrate users and get a link to (in addition to the standard link to
And then there’s a problem with cookies. , you learned how to go beyond basic authentication by using cookies, and that’s a good thing. But, there are some very real concerns surrounding a high-end application using cookies, and only cookies, for authentication. In this chapter, you’ll do all of the above and more.
Modeling Groups in Your Database
Before you can look up the groups to which a user belongs, you need to have some groups in your database. You need a table to store groups and some means by which you can connect a user to a group. Also, you need to be able to connect one user to multiple groups.
There are a few distinct steps here:
1. Create a table in the database to store groups.
2. Map a user to zero, one, or more groups.
3. Build PHP to look up that mapping .
4. Restrict pages based on any login, or a particular set of groups.
First things first: It all begins with a database table.
Adding a Groups Table
Creating a new table is a trifling thing for you as a PHP and MySQL programmer. You can easily create a new table, name it (9((”.105), give MySQL a few columns, specify which are NOT NULL, and bang; you’re quickly past database table creation.
As usual, each group needs an ID and a name. The description column is optionalit’s not NOT NULL, which is bad grammar but good database design-and that’s all you need
It’s hard to do much testing without some group information, so go ahead and add a few groups into your new Cjl’oups table
As usual, test before moving on
The Many-to-Many Relationship
Next, establish how you’re going to connect users to groups. Before you can start worrying about SOL you need to think clearly about how these two tables are related. Relationships help you to determine in what manner tables are linked.
ONE-TO-ONE, ONE-TO-MANY, MANY-TO-MANY
You’ve already seen an example of a one-to-one relationship. For example, when you were storing images in your database (page 303), you had a single entry in 0′ that was related to a single entry in r between IS r- and
With groups, that’s not the case. You’ve already seen that a single user can be in zero groups, one group, or many groups. Certainly Michael Greenfield can be a luthier, musician, and administrator. You might have another user who is in none of those groups.
From that perspective, you have one user can be related to many groups. “Many” doesn’t have a strict literal meaning here, either. It means more like “as many as you want.” So, “many” can mean 0, 1, 1,000, or anything in between or above
However, that’s only part of the story. You must also consider the point of view of the A group can have many users. For example, the Administrators group might have 4, 5, or 20 users. This means that there’s a one-to-many relationship on the groups-to-users side of things as well as on the users-to-groups side
What you have here is a many-to-many (chieftainship between users and groups (or, if you like, between groups and users). One user can be in many groups; one group can have many users. It’s a multitude relationship, which is a bit more complex to model at the database level but just as important in the real world of data as a one-to-one relationship or a one-to-many relationship.
Lots of Programmers Are Secretly Math Geeks
It’s true: most programmers have at least a little love for math, often buried somewhere deep down. One proof of this is that many programming concepts share naming ideas from math
For example, you might hear about one-to-one (1-10-1, or even, sometimes, 1:1) relationships. You’ll also hear about one-to-many relationships. But. just as often, you’ll hear about a 1-to-N relationship. N is a mathematical term; it’s usually written as lowercase n in math, but it’s more often capital N in programming. That N is just a stand-in for a variable number. So N could be D, or 1,or some large number.
In that light, then, a one-to-many relationship is the same as a l:N relationship. It’s just that l:N is a shorter, more concise way to say the same thing. You know that programmers-like you-tend to favor short and concise. So,on database diagrams you’ll often see l:N, which just tells you that relationship between two tables is one-to-many.
And then, of course, you have N:N, which is just saying that many items in one table are related to many items in another. That said, an N:N relationship (and the many-to-many relationship that it represents) is a conceptual or virtual idea. It takes two relationships at the database level in most systems to model an N:N relationship, as you’ll see on page 459
JOINS ARE BEST DONE WITH IDS
When you related a user to a profile image, you used an 10. Eace image had its own 10, uniquely identifying it. It also had a user _id, which connected the image to a particular user in the users table. That made it easy to grab an image for a user by using something like this
SELECT *
FROM images
WHERE user_id = $user_id;
Or, you can join the two tables like this:
SELECT u.username, u.first_name, u.last_name, i.filename, i.image_data
FROM users u, images i
WHERE u.id ; i.user_id;
In both cases, the IDs are the connectors. That works fine in a one-to-one relationship, as it does in a one-to-many relationship. The “many” side just adds a column that references the ID of the “one” side. Therefore, many images all have a user _id column that references a user with the ID 51(or 2931 or whatever else you have in
But with users and groups, you don’t have a one-to-one or a one-to-many relationship. You have a many-to-many. How do you handle that?
USE A JOIN TABLE TO CONNECT USERS WITH GROUPS
It’s easy to model a one-to-many relationship by using the ID as a connector. When you’re modeling a many-to-many relationship, connecting the IDs is more complex. You need a sort of matrix: a set of user IDs and group IDs that are connected
Think about the many-to-many relationship. In its simplest form, it’s two one-to many relationships; users and groups have a many-to-many relationship going in each direction. You started with one side: users. Then you figured out it was one to many. Then, the other side: groups. Also one-to-many.
But then you also have the one-to-many from groups to users. The ID for “Administrators” might appear in five different rows within user once for each of the five users to which that group relates
To give this idea a concrete form, create the following table
mysql> CREATE TABLE user_groups
-> user_id INT NOT NULL,
-> group_id INT NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
This table becomes a bridge: each row connects one user to one group. So, for “Jeff Traugott” with an ID of 29, and a group “Luthiers” with an ID of 2, you’d add this row to
mysql> INSERT INTO user_groups
-> (user_id, group_id)
-> VALUES (29, 2);
Query OK, 1 row affected (0.02 sec)
Testing Group Membership
To see whether a user is in a grollp, you need to determine whether there’s an entry in both the ID of the ID you want, and the ID of the group you want.
Bingo! This query looks a little complex at first blush, but it’s straightforward if you walk through it step by step
First. you use to return a count on the rows returned from the query. And then there are the three tables involved
SELECT COUNT(*)
FROM users u, groups g, user-groups ug
Next, you indicate the name of the user you want (using any column you want; first name, last name, or user name), and the name of the group you want. This will cause exactly one Cor zero, if there’s no match) row in both uses and groups to be isolated.
SELECT COUNT(*)
FROM users u, groups g, user_groups ug
WHERE u.username = “traugott”
AND g.name = “luthiers
Now, you need to connect those individual rows-each with an ID-to. This is just a regular join. You use the IDs in each table to match up with the ID columns in
SELECT COUNT(*)
FROM users u, groups g, user_groups ug
WHERE u.username = “traugott”
AND g.name = “Luthiers”
AND u.user_id = ug.user_id
AND g.id = ug.group_id;
or a row with a COUNT value of 0, meaning there’s no connection
The task now is to turn this into PHP code.