PHP implementation of the method of saving session with MySQL _php tips

Session is a variable that the server side uses to save the user information in the PHP program design, which has a very wide application value. This article describes the PHP implementation of the use of MySQL to save the session method. Share for everyone to use for reference. The specific steps are as follows:

The implementation environment for This example is:

PHP 5.4.24
MySQL 5.6.19
OS X 10.9.4/apache 2.2.26

One, the code part

1.SQL statement:

CREATE TABLE ' Session ' (
 ' skey ' char (+) CHARACTER SET ASCII not NULL,
 ' data ' text COLLATE utf8mb4_bin,
 ' Expir E ' int (one) not NULL,
 PRIMARY key (' Skey '),
 key ' Index_session_expire ' (' expire ') USING btree
) Engine=myisam DEFAULT CHARSET=UTF8MB4 Collate=utf8mb4_bin;

2.PHP Part code:

<?php/* The DNS, username, password, etc. required to connect to the database are generally not changed in code, * so using the form of constants avoids the need for global references in functions.
* * Define (' Session_dns ', ' mysql:host=localhost;dbname=db;charset=utf8mb4 ');
Define (' session_usr ', ' USR ');
Define (' Session_pwd ', ' PWD ');

Define (' Session_maxlifetime ', Get_cfg_var (' session.gc_maxlifetime ')); Creating a PDO connection//persistent connection can provide better efficiency function getconnection () {try {$conn = new PDO (Session_dns, SESSION_USR, Session_pwd, a Rray (pdo::attr_persistent => TRUE, Pdo::attr_errmode => pdo::errmode_exception, pdo::attr_emulate
    _prepares => FALSE));
  return $conn; 
The Open function Sessionmysqlopen ($savePath, $sessionName) of the custom session of catch (Exception $ex) {}}} {return TRUE; 
 //The Close function function Sessionmysqlclose () {return TRUE;}//The custom session does not have an injection problem because the data submitted by the user is generally not saved directly to the session. * and SQL statements that process session data are not used more than once.
 Therefore, the effectiveness of the pretreatment function can not be reflected.
 * Therefore, the actual project can not be dogmatic use of the preprocessing function.
 In the */* Sessionmysqlread () function, first determine whether the SessionID exists by select COUNT (*). * Because the MySQL database provides select pair PdostAtement::rowcount () support, * Therefore, the actual project can be directly used rowcount () to judge.
///////The Read function of the custom session adds "Expire > Time ()" to the//sql statement to avoid reading expired sessions.
    function Sessionmysqlread ($sessionId) {try {$dbh = getconnection ();
    $time = time (); $sql = ' SELECT count (*) as ' count ' from the 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 for the custom session//expire the data stored in the current time +session lifetime, which indicates that the session is invalid when the value is less than ().
    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 ();
    }//Custom session Destroy function Sessionmysqldestroy ($sessionId) {try {$dbh = getconnection ();
    $sql = ' DELETE from ' session ' where skey =? ';
    $stmt = $dbh->prepare ($sql);
    $stmt->execute (Array ($sessionId));
  return TRUE;
  catch (Exception $e) {return FALSE;
    }//Custom session GC function 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; The session ID setting function of the custom session///* because before session_start (), Sid and SESSION_ID () are not valid, * so use $_get[session_name ()] and $_cookie[session_name ()] for detection.
 * If both are empty, the session is not established and the session ID needs to be set for the new session.
 * Obtaining a UUID from the MySQL database as the session ID is a better way to avoid session ID collisions. */function Sessionmysqlid () {if (Filter_input (Input_get, session_name ()) = = ' and Filter_input (Input_cookie, SE
      Ssion_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 the startup function, including Session_Start () and all previous steps. function Startsession () {session_set_save_handler (' sessionmysqlopen ', ' sessionmysqlclose ', ' sessionm
  Ysqlread ', ' sessionmysqlwrite ', ' Sessionmysqldestroy ', ' SESSIONMYSQLGC ');
  Register_shutdown_function (' Session_write_close ');
  Sessionmysqlid ();
Session_Start ();

 }

Ii. Introduction

1. To save session with MySQL, you need to save three key data: The Sessions ID, the duration data, and the lifetime.

2. Given the way the session is used, there is no need to use the InnoDB engine, and the MyISAM engine can achieve better performance. If the environment allows, you can try using the memory engine.

3. Save the session data column, if necessary, you can use the UTF8 or utf8mb4 character set, the column that holds the session ID is not necessary, generally use the ASCII character set, you can save the storage cost.

4. The column that holds the session life period can be designed according to the engineering needs. such as datetime type, timestamp type, int type. The session build time or expiration time can be saved for datetime, int types.

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. The current version, as long as the custom session maintenance function is registered through Session_set_save_handler, you do not need to use the Session_module_name (' user ') function before it.

7. When the read function gets the data and returns, PHP automatically deserializes it, and don't make any changes to the data in general.

8.PHP the date parameter passed to the Write function is the serialized session data, which can be saved directly, and do not make changes to the data in general.

9. In accordance with the logic of this code, the PHP configuration options for session lifetime settings are no longer valid, this value can be maintained on its own, does not necessarily need to be acquired through Get_cfg_var.

10.sessionMysqlId () function is to avoid large users, multiple Web servers in the case of collisions, the general situation PHP automatically generated session ID can meet user requirements.

Third, demand

when the user is very large, need more than one server to provide applications, using a MySQL storage session relative to the use of session file has a certain advantage . For example, there is minimal storage overhead, such as avoiding the complexity of file sharing, such as better avoiding collisions, for example, better performance than session file sharing. In general, the problem with session files is almost explosive when the number of visits increases linearly, and the problem with using a database save session is linear. Well, to put it more bluntly: If your application user is not large, in fact, let PHP deal with the session itself is good, do not need to consider MySQL.

Reference function and Concept section:

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
5 Http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert

I hope the example described in this article will help you with PHP programming.