PHP User Authentication - the database


This lesson is part of an ongoing User Authentication tutorial. The first part is here: User Authentication along with all the files you need.

The database you need for this project can be found in the databases folder, and is called login.sql. Load up phphMyAdmin and import this SQL file, just like you did for the membertest database in the previous section. When you import the file, the left side of phpMyAdmin should look like this:

List of databases in phpMyAdmin

When you click on the login table in the login database, the right side of phpMyAdmin should like like this (except for the first unencrypted row):

Values in the MySQL login table

So we have three users set up, usernameTest, test2, and testUser1. These three can be seen in the L1 column. But notice the passwords for each, in the L2 column. The first user, usernameTest has a raw password, with no encryption. It just says passwordTest. This is precisely what you don't want to see in your own database tables - unencrypted passwords. Rows like this should be deleted. To delete a row in phpMyAdmin, put a check in one (or all) of the boxes on the left. Then click Delete. You should see this:

The Confirm Delete dialogue box in phpMyAdmin

Click OK to confirm the delete, and you'll see a message:

phpMyAdmin message for a successful query

The two encrypted rows look like this:

Two rows with encrypted passwords

Those really are encrypted values in column L2, and not just somebody typing in really long and complicated passwords. Let's see how to do encryption.

Click on the Structure tab at the top of phpMyAdmin to see how we've set up the various fields in the table:

The Structure tab in phpMyAdmin

Of course, you could create the database and table yourself in phpMyAdmin. If you fancy a try, or didn't successfully import the SQL file, create a new database called login. Create a new table also called login. For the login table, add these fields:

Set the ID field to be the primary key. (You did this in an earlier lesson, as well as setting an Auto Increment.)

Now on to the PHP scripts.