Web Database Applications with PHP, and MySQL

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. 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:

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)
);

There is an additional index that allows 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:

mysql> SELECT * FROM PHPSESSION;
+------------------------------+--------------------------------+----------------+
| session_id                   | session_variable               | last_updated   |
+------------------------------+--------------------------------+----------------+
| d003a284fbbf982c90aade5485   | count|i:39;start|i:1000900585; | 1000900661.575 |
| b74e720d5395800d5fabe7eab8   | count|i:0;start|i:1000900677;  | 1000900678.705 |
+------------------------------+--------------------------------+----------------+
2 rows in set (0.02 sec)

Handler Implementations

The best way to arrange the functions that implement the session handlers is to place them in a single support file. By placing the functions ...