PHP uses MySQL to save the session implementation idea and sample code, mysqlsession

PHP uses MySQL to save the session implementation idea and sample code, mysqlsession

Implementation environment:

PHP 5.4.24
MySQL 5.6.19
OS X 10.9.4/Apache 2.2.26

I. Code

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;

<? 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 () = ''andfilter_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_start () 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

When you use MySQL to save a session, you need to save three key data: session id, session data, and session life cycle.
Considering how the session is used, there is no need to use the InnoDB engine. The MyISAM engine can achieve better performance. If the environment permits, you can try to use the MEMORY engine.
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.
The columns that save the session life cycle can be designed based on engineering needs. For example, datetime, timestamp, and int. For datetime and int types, you can save the session generation time or expiration time.
If necessary, you can extend the columns of the session table and modify the Read and Write functions to support (maintenance) related columns to save information such as user names.
In the current version, you only need to register the custom session maintenance function through session_set_save_handler. You do not need to use the session_module_name ('user') function before it.
When the read function obtains and returns data, PHP automatically deserializes the data. Generally, do not change the data.
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.
According to the logic of the Code in this section, the settings of the PHP configuration option 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.
The sessionMysqlId () function is used to avoid collisions between large numbers of users and multiple Web servers. Generally, session IDs automatically generated by PHP can meet user requirements.
No

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.

How to log on to the system as a member (php session is used, but MySQL is not used to save user information using TXT files)

This problem can be solved by using array storage, and the output component is separated string, which is a multi-string operation.
I can send you a sample code.

Urgent PHP users log on to the mysql database to store sessions, and use cookies to store the complete source program or Class

$ Gb_DBname = "charles_friend"; // Database Name
$ Gb_DBuser = "charles_friend"; // Database User Name
$ Gb_DBpass = "wxyzoui"; // Database Password
$ Gb_DBHOSTname = "localhost"; // host name or IP address
$ SESS_DBH = "";
$ SESS_LIFE = get_cfg_var ("session. gc_maxlifetime"); // obtain the maximum validity period of the session.
Function sess_open ($ save_path, $ session_name ){
Global $ gb_DBHOSTname, $ gb_DBname, $ gb_DBuser, $ gb_DBpass, $ SESS_DBH;
If (! $ SESS_DBH = mysql_pconnect ($ gb_DBHOSTname, $ gb_DBuser, $ gb_DBpass )){
Echo "<li> MySql Error:". mysql_error (). "<li> ";
Die ();
}
If (! Mysql_select_db ($ gb_DBname, $ SESS_DBH )){
Echo "<li> MySql Error:". mysql_error (). "<li> ";
Die ();
}
Return true;
}
Function sess_close (){
Return true;
}
Function sess_read ($ key ){
Global $ SESS_DBH, $ SESS_LIFE;
$ Qry = "select value from db_session where sesskey = '$ key' and expiry>". time ();
$ Qid = mysql_query ($ qry, $ SESS_DBH );
If (list ($ value) = mysql_fetch_row ($ qid )){
Return $ value;
}
Return false;
}
Function sess_write ($ key, $ val ){
Global $ SESS_DBH, $ SESS_LIFE;
$ Expiry = time () + $ SESS_LIFE;
$ Value = $ val;
$ Qry = "insert into db_session values ('$ key', $ expiry,' $ value ')";
$ Qid = mysql_query ($ qry, $ SESS_DBH );
If (! $ Qid ){
$ Qry = "update db_session set expiry = $ expiry, value = '$ value' where sesskey =' $ key' and expiry>". time ();
$ Qid = mysql_query ($ qry, $ SESS_DBH );
}
Return $ qid;
} ...... Remaining full text>