Web Database Applications with PHP and MySQL, 2nd Edition

MySQL Session Store

In this section we develop a set of user-defined handlers that store session variables in a MySQL table.

Session Table Structure

For the session handler code that stores session variables, a table is needed to hold sessions. You can create the table as part of your application database (for example, as a table in the winestore database), or create a new database to store only sessions. We follow the former approach, but it doesn't matter what you choose in practice; the database is accessed using its own connection, so you won't have any trouble accessing the table even when other tables are locked.

The following SQL CREATE TABLE statement creates a table to hold the session ID, the serialized session variables, and a timestamp to indicate when the session was last accessed. If you want to add it to the winestore database, login to the MySQL command interpreter as root user and type the following:

mysql>use winestore;
Database changed
mysql> CREATE TABLE PHPSESSION(
    ->   session_id varchar(50) NOT NULL,
    ->   session_variable text,
    ->   last_accessed decimal(15,3) NOT NULL,
    ->   PRIMARY KEY (session_id),
    ->   KEY last_acc (last_accessed)
    -> ) type=MyISAM;
Query OK, 0 rows affected (0.05 sec)

The session_id attribute is the primary key and the last_accessed attribute is indexed to allow fast deletion of dormant sessions using custom garbage-collection code described later.

When the code is up and running, the PHPSESSION table can be examined to see the current sessions. The following ...