A user table

We need a way to store login data for each account:

  • User name
  • Password
  • Roles

(Actually, we won't store passwords directly. More later.)

Let's put user data in the DB. There are several ways to do the task. Let's keep it simple, and make a single users table.

The users table

Here's the table's structure.

users table

user_id is the primary key of the table. As usual, an integer (whole number), required (Null is no), and auto increment. As we add records to the table, MySQL will make a new id for us.

name is a character field, with a maximum length of 50 characters. It's required as well.

The last two fields need more explanation.

Password hash

To understand password_hash, you need to know that we should never store passwords in a database. If someone hacks the DB, we don't want them to be able to see passwords.


Never store plain text passwords

Store hashes instead.


Wait, what? How can we check logins, if we don't know people's passwords?

OK, to see how this works, you need to know about hashing functions. A hashing function turns text into gobbledygook.


So you send something in, and you get something out. For example:

Hashing example

DoggsAreGreat! in, and 252cea... out.

Another one:

Hashing example

The function is predictable. Put the same text in, and you always get the same hash out.

The hashing function is a one-way algorithm. Given some plain text, you can compute a hash. But you can't reverse it. If you have a hash, you can't turn that back into plain text. That's why storing hashes in the DB is safe.

There are a bunch of hashing functions in PHP already. We'll use the MD5 algorithm. It's not great from a security point of view, but it's easy to use in a login system. The function is md5(). Give it something, and it will give you back a hash.

  • $hash = md5('Firefly is a great show!');

MD5 always gives you a 32 character result. If the input to md5() is 10 characters long, md5() will return 32 characters. If the input is 1,100 characters, the output will be 32 characters long.

That's why the password_hash in the DB can hold 32 characters:

Table structure

We don't store passwords in the field password_hash. We store hashes. Like this:

Password hashes


Hey, when you created those two records, you wanted MySQL to store hashes, not passwords. How did you do that?

Good question! phpMyAdmin let's you apply MD5 to a field, when you insert a new record.

MD5 in phpMyAdmin

No clear text password

The users table does not store anyone's password. It stores hashes of passwords.

Now, our app has a login form.

Login form

Someone types a username and password. That data goes to a PHP program. It works out the MD5 hash of what the user entered, and compares that to the hash in the database.

If they're they same:

Login worked

What if someone types the wrong password?

Login failed

See what's going on here: the login system works, even though the DB doesn't have passwords in it (it has hashes instead).


Now that's cool.

Aye, it is.


Ooo! I think I figured something out.

Way back in the beginning of the course, you said that you don't know what our passwords are for this site. Is that because this website uses the method you just showed us?

Indeed! It doesn't use MD5, it uses something stronger, but it stores hashes, not plain passwords.

So, ask me what your password is, and I couldn't tell you, even though I have access to the database. I have no way to figure out your password. I can change it to something new, but I can't tell you what the current one is.


Hey, good thinking, Georgina!

Yes, good work!


Here's the table's structure again:

users table

Roles are stored in a text field. Let's see how it works.

Each role has a name, like:

  • admin
  • manager
  • sales-rep
  • service-technician

When someone has more than one role, let's stick the names together, separated by commas. For example...


...would mean someone has the manager and sales-rep goals. So, they should see everything managers can see, and everything sales reps can see.

Easy peasy!

Up next

Let's see how a login program can use the data in this table.