Join David Powers for an in-depth discussion in this video Creating a database and user account in MySQL, part of PHP: Managing Persistent Sessions.
- In this chapter we'll set up the database to store the session data, auto-login tokens, and user credentials. I like to use a separate database and a dedicated user account but you can use an existing database and user account if you prefer. In my editing program, I've got open "create_db.sql" which you can find in the exercise files for this video. It contains three SQL statements. The first one creates a database called "persistent" and sets collation, in other words, the default sort order to "utf8_general_ci" The "ci" stands for case insensitive.
The second SQL statement creates a dedicated user account called "sess_admin" which has access only on local host and it's identified by the not so secret password "secret" and then the last one that grants select, insert, update and delete privileges on the persistent database to the "sess_admin" account @local host. If you want to use different names for the database or user account, amend the SQL statements accordingly.
And of course in a real world situation you should also use a much stronger password for the user account. But those are the settings that I'm going to be using in the exercise files. To create the database and user account, you need to run these three SQL statements in MySQL or MariaDB. I'm using phpMyAdmin to administer my SQL so I'm going to copy these three statements and switch to phpMyAdmin, so select all of them then copy to my clipboard, go to my browser and phpMyAdmin and then on the home page, click the SQL tab at the top left, add in that field there, paste those three SQL statements and then just click "go." It says "My SQL returned an empty result set." That's fine.
If we just click this "reload navigation" panel icon we can see that the persistent database has now been added to phpMyAdmin. Let's just check the user accounts so click the "users" tab here and we can see that "sess_admin" has been added to the users, and if we click this "edit privileges" here we can see the privileges that have been granted. Just click that, and these are the global privileges.
None have been set. What we want to do is to look at the database specific privileges, so I'm going to click "database" here and this is the database, "persistent" and we've got those four privileges. These are all needed to handle sessions and user registration, but I've given this account no other privileges on the principal of giving user accounts only those privileges they need to perform their task. So that completes setting up the database and dedicated user account.
Next we need to define the tables for persistent sessions.
- Configuring sessions
- Inspecting how session data is stored
- Implementing persistent sessions
- Creating a SQL database to store session data
- Implementing the PHP SessionHandlerInterface
- Writing session data
- Closing and destroying sessions
- Creating an auto-login system with persistent sessions
- Creating persistent logins
- Checking out visitor credentials
- Authenticating users
- Logging out users selectively