PHP uses MySQL to save the session and phpmysqlsession

PHP uses MySQL to save the session and phpmysqlsession

Implementation environment:

PHP 5.4.24MySQL 5.6.19OS 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;

1 <? Php 2/* 3 * the DNS, user name, and password required to connect to the database. Generally, they are not changed in the Code. 4 * the constant format is used, global is not required for function reference. 5 */6 define ('session _ dns', 'mysql: host = localhost; dbname = db; charset = utf8mb4 '); 7 define ('session _ usr ', 'usr'); 8 define ('session _ pwd', 'pwd'); 9 define ('session _ maxlifetime', get_1__var ('session. gc_maxlifetime '); 10 11 // create a PDO connection 12 // persistent connection can provide better efficiency 13 function getConnection () {14 try {15 $ conn = new PDO (SESSION_DNS, SESSION_USR, SESSION_PWD, array (16 PDO: ATTR_PERSISTENT => TRUE, 17 PDO: ATT R_ERRMODE => PDO: ERRMODE_EXCEPTION, 18 PDO: ATTR_EMULATE_PREPARES => FALSE 19); 20 return $ conn; 21} catch (Exception $ ex) {22 23} 24} 25 26 // The open function 27 function sessionMysqlOpen ($ savePath, $ sessionName) of the custom session {28 return TRUE; 29} 30 31 // The close function of the custom session 32 function sessionMysqlClose () {33 return TRUE; 34} 35/* 36 * generally, the data submitted by the user is not directly saved to the session, so there is no injection problem in general cases. 37 * SQL statements that process session data are not used multiple times. Therefore, the benefits of preprocessing cannot be reflected. 38 * therefore, preprocessing is not required in actual engineering. In the 39 */40/* 41 * sessionMysqlRead () function, SELECT count (*) is used to determine whether the sessionID exists. 42 * because the MySQL database provides SELECT support for PDOStatement: rowCount (), 43 * therefore, rowCount () can be used in actual projects for determination. 44 */45 // read function 46 of the custom session // The "expire> time ()" judgment is added to the SQL statement to avoid reading expired sessions. 47 function sessionMysqlRead ($ sessionId) {48 try {49 $ dbh = getConnection (); 50 $ time = time (); 51 52 $ SQL = 'select count (*) AS 'Count' FROM session '53. 'Where skey =? And expire>? '; 54 $ stmt = $ dbh-> prepare ($ SQL); 55 $ stmt-> execute (array ($ sessionId, $ time )); 56 $ data = $ stmt-> fetch (PDO: FETCH_ASSOC) ['Count']; 57 if ($ data = 0) {58 return ''; 59} 60 61 $ SQL = 'select 'data' FROM 'session ''62. 'where' skey' =? And 'expire '>? '; 63 $ stmt = $ dbh-> prepare ($ SQL); 64 $ stmt-> execute (array ($ sessionId, $ time )); 65 $ data = $ stmt-> fetch (PDO: FETCH_ASSOC) ['data']; 66 return $ data; 67} catch (Exception $ e) {68 return ''; 69} 70} 71 72 // The write function 73 of the custom session // the data stored in the expire field is the current time + session life cycle. When this value is less than time () indicates that the session is invalid. 74 function sessionMysqlWrite ($ sessionId, $ data) {75 try {76 $ dbh = getConnection (); 77 $ expire = time () + SESSION_MAXLIFETIME; 78 79 $ SQL = 'insert INTO 'session '('skey', 'data', 'expire ') '80. 'values (?, ?, ?) '81. 'on duplicate key update data = ?, Expire =? '; 82 $ stmt = $ dbh-> prepare ($ SQL); 83 $ stmt-> execute (array ($ sessionId, $ data, $ expire, $ data, $ expire); 84} catch (Exception $ e) {85 echo $ e-> getMessage (); 86} 87} 88 89 // The destroy function of the custom session 90 function sessionMysqlDestroy ($ sessionId) {91 try {92 $ dbh = getConnection (); 93 94 $ SQL = 'delete FROM 'session 'where skey =? '; 95 $ stmt = $ dbh-> prepare ($ SQL); 96 $ stmt-> execute (array ($ sessionId); 97 return TRUE; 98} catch (Exception $ e) {99 return FALSE; 100} 101} 102 103 // gc function of the custom session 104 function sessionMysqlGc ($ lifetime) {105 try {106 $ dbh = getConnection (); 107 108 $ SQL = 'delete FROM 'session 'WHERE expire <? '; 109 $ stmt = $ dbh-> prepare ($ SQL); 110 $ stmt-> execute (array (time (); 111 $ dbh = NULL; 112 return TRUE; 113} catch (Exception $ e) {114 return FALSE; 115} 116} 117 118 // The session id setting function of the custom session 119/* 120 * Because SID and session_id () are invalid before session_start, 121 * use $ _ GET [session_name ()] and $ _ COOKIE [session_name ()] for detection. 122 * if both are null, it indicates that the session has not been created. You need to set the session id for the new session. 123 * using the MySQL database to obtain the uuid as the session id can better avoid session id collision. 124 */125 function sessionMysqlId () {126 if (filter_input (INPUT_GET, session_name () = ''and127 filter_input (INPUT_COOKIE, session_name () = '') {128 try {129 $ dbh = getConnection (); 130 $ stmt = $ dbh-> query ('select uuid () AS uuid '); 131 $ data = $ stmt-> fetch (PDO: FETCH_ASSOC) ['uuuid']; 132 $ data = str_replace ('-', '', $ data ); 133 session_id ($ data); 134 return TRUE; 135} catch (Exception $ ex) {136 retur N FALSE; 137} 138 139} 140 141 // session start function, including session_start () and all previous steps. 143 function startSession () {144 sessions (145 'sessionmysqlopen', 146 'sessionmysqlclose', 147 'sessionmysqlread', 148 'sessionmysqlwrite', 149 'sessionmysqldestroy', 150 'sessionmysqlgc '); 151 register_shutdown_function ('session _ write_close '); 152 sessionMysqlId (); 153 session_start (); 154}

II. Introduction

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

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

 

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>

How to use the database to implement PHP to save SESSION details ??

I hope that you will be able to fully master this knowledge through the methods and skills introduced in this article. PHP $ con = mysql_connection ("127.0.0.1", "user", "pass"); mysql_select_db ("session"); function open ($ save_path, $ session_name) {return (true);} function close () {return (true);} function read ($ id) {if ($ result = mysql_query ("select * from session where id = '$ id'") {if ($ row = mysql_felth_row ($ result )) {return $ row ["data"] ;}} else {return "" ;}} function write ($ id, $ sess_data) {if ($ result = m Ysql_query ("update session set data = '$ sess_data' where id = '$ id'") {return true;} else {return false;} function destroy ($ id) {if ($ result = mysql_query ("delete * from session where id = '$ id'") {return true;} else {return false ;}} function gc ($ maxlifetime) {return true;} session_set_save_handler ("open", "close", "read", "write", "destroy", "gc "); session_start (); // proceed to use sessi Ons normally?