PHP implements the method of saving session with MySQL

This article describes how to use MySQL to save sessions in PHP. it is a useful technique in PHP programming, for more information, see session, which is a variable used by the server to save user information in PHP programming. it has a wide range of application values. This example describes how to use MySQL to save a session in PHP. Share it with you for your reference. The procedure is as follows:

InstanceImplementation environment:

PHP 5.4.24
MySQL 5.6.19
OS X 10.9.4/Apache 2.2.26

I. code

1. SQL statement:

CREATE TABLE `session` ( `skey` char(32) CHARACTER SET ascii NOT NULL, `data` text COLLATE utf8mb4_bin, `expire` int(11) NOT NULL, PRIMARY KEY (`skey`), KEY `index_session_expire` (`expire`) USING BTREE) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2. some PHP code:

<? Php/** the DNS, user name, and password required to connect to the database. Generally, they are not changed in the code. * use a constant, global is not required for function reference. */Define ('session _ dns', 'MySQL: host = localhost; dbname = db; charset = utf8mb4 '); define ('session _ usr', 'usr '); define ('session _ pwd', 'pwd'); define ('session _ MAXLIFETIME ', get_1__var ('session. gc_maxlifetime '); // Create a PDO connection // persistent connection can provide better efficiency function getConnection () {try {$ conn = new PDO (SESSION_DNS, SESSION_USR, SESSION_PWD, array (PDO: ATTR_PERSISTENT => TRUE, PDO: ATTR_ERRMODE => PDO: ERRMODE_EXCEPTION, PDO:: ATTR_EMULATE_PREPARES => FALSE); return $ conn;} catch (Exception $ ex) {}} // The open function sessionMysqlOpen ($ savePath, $ sessionName) of the custom session) {return TRUE;} // The close function of the custom session sessionMysqlClose () {return TRUE;}/** generally, data submitted by the user is not directly saved to the session, therefore, there is no injection problem in normal cases. * SQL statements that process session data are not used multiple times. Therefore, the benefits of preprocessing cannot be reflected. * Therefore, pre-processing functions are not required in actual engineering. * // ** In the sessionMysqlRead () function, SELECT count (*) is used to determine whether the sessionID exists. * Because the MySQL database supports SELECT for PDOStatement: rowCount (), * in actual projects, rowCount () can be used for determination. * /// Added the "expire> time ()" judgment in the read function of the custom session to avoid reading expired sessions. Function sessionMysqlRead ($ sessionId) {try {$ dbh = getConnection (); $ time = time (); $ SQL = 'SELECT count (*) AS 'count' FROM session WHERE skey =? And expire>? '; $ Stmt = $ dbh-> prepare ($ SQL); $ stmt-> execute (array ($ sessionId, $ time )); $ data = $ stmt-> fetch (PDO: FETCH_ASSOC) ['count']; if ($ data = 0) {return '';} $ SQL = 'SELECT 'data' FROM 'session 'WHERE 'skey' =? And 'expire '>? '; $ Stmt = $ dbh-> prepare ($ SQL); $ stmt-> execute (array ($ sessionId, $ time )); $ data = $ stmt-> fetch (PDO: FETCH_ASSOC) ['data']; return $ data;} catch (Exception $ e) {return '';}} // The write function of the custom session // the data stored in the expire field is the current time + session life cycle. if the value is smaller than time (), the session becomes invalid. Function sessionMysqlWrite ($ sessionId, $ data) {try {$ dbh = getConnection (); $ expire = time () + SESSION_MAXLIFETIME; $ SQL = 'Insert INTO 'session '('skey', 'data', 'expire ')'. 'values (?, ?, ?) '.' On duplicate key update data = ?, Expire =? '; $ Stmt = $ dbh-> prepare ($ SQL); $ stmt-> execute (array ($ sessionId, $ data, $ expire, $ data, $ expire);} catch (Exception $ e) {echo $ e-> getMessage () ;}// function sessionMysqlDestroy ($ sessionId) of the destroy function of the custom session) {try {$ dbh = getConnection (); $ SQL = 'delete FROM 'session' where skey =? '; $ Stmt = $ dbh-> prepare ($ SQL); $ stmt-> execute (array ($ sessionId); return TRUE;} catch (Exception $ e) {return FALSE ;}// the gc function of the custom session sessionMysqlGc ($ lifetime) {try {$ dbh = getConnection (); $ SQL = 'delete FROM 'session 'WHERE expire <? '; $ Stmt = $ dbh-> prepare ($ SQL); $ stmt-> execute (array (time (); $ dbh = NULL; return TRUE ;} catch (Exception $ e) {return FALSE ;}// set the session id of the custom session function/** because the SID and session_id () before session_start () invalid. * Use $ _ GET [session_name ()] and $ _ COOKIE [session_name ()] for detection. * If both are NULL, it indicates that the session has not been created. you must set the session id for the new session. * Using the MySQL database to obtain the uuid as the session id can better avoid session id collisions. */Function sessionMysqlId () {if (filter_input (INPUT_GET, session_name () = ''and filter_input (INPUT_COOKIE, session_name () = '') {try {$ dbh = getConnection (); $ stmt = $ dbh-> query ('select uuid () AS uuid '); $ data = $ stmt-> fetch (PDO:: FETCH_ASSOC) ['uuid']; $ data = str_replace ('-', '', $ data); session_id ($ data); return TRUE ;} catch (Exception $ ex) {return FALSE ;}}// session start function, including session_st Art () and all previous steps. Function startSession () {session_set_save_handler ('sessionmysqlopen ', 'sessionmysqlclose', 'sessionmysqlread', 'sessionmysqlwrite', 'sessionmysqldestroy', 'sessionmysqlgc '); register_shutdown_function ('session _ write_close '); sessionMysqlId (); session_start ();}

II. Introduction

1. when you use MySQL to save a session, you need to save three key data: session id, session data, and session life cycle.

2. Considering the session usage, there is no need to use the InnoDB engine, so the MyISAM engine can achieve better performance. If the environment permits, you can try to use the MEMORY engine.

3. you can use the utf8 or utf8mb4 character set to save the session data column if necessary. it is not necessary to save the session id column. Generally, you can use the ascii character set to save the storage cost.

4. the columns that save the session life cycle can be designed as required by the project. For example, datetime, timestamp, and int. For datetime and int types, you can save the session generation time or Expiration Time.

5. if necessary, you can extend the columns of the session table and modify the read and write functions to support (maintain) related columns to save information such as user names.

6. in the current version, you only need to use session_set_save_handler to register a custom session maintenance function. you do not need to use the session_module_name ('user') function before it.

7. when the read function gets the data and returns it, PHP will automatically deserialize It. generally, do not change the data.

8. the date parameter passed by PHP to the write function is the serialized session data and can be saved directly. generally, do not change the data.

9. according to the logic of the code in this section, the PHP configuration option's settings about the session life cycle are no longer valid. this value can be maintained on its own and may not be obtained through get_cfg_var.

10. the sessionMysqlId () function is used to avoid collisions between large numbers of users and multiple Web servers. Generally, the session id automatically generated by PHP can meet user requirements.

III. requirements

When a large number of users require multiple servers to provide applications, using MySQL to store sessions is superior to using session files. For example, it has the minimum storage overhead, for example, it can avoid the complexity of file sharing, for example, it can better avoid collision, for example, it has better performance than session file sharing. In general, when there is a sharp increase in access traffic, if the problem of saving sessions using databases increases linearly, the problem of using session files is almost explosive. Well, let's put it bluntly: if the number of users in your application is small, you can let PHP handle the session by itself, and there is no need to consider MySQL.

IV. reference functions and concepts:

1 http://cn2.php.net/manual/zh/function.session-set-save-handler.php
2 http://cn2.php.net/manual/zh/session.idpassing.php
3 http://cn2.php.net/manual/zh/pdo.connections.php
4 http://cn2.php.net/manual/zh/pdo.prepared-statements.php
Http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert

I hope the examples described in this article will be helpful for PHP programming.