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.