PHP uses MySQL to save session implementation ideas and sample code, mysqlsession_php tutorial

Source: Internet
Author: User
Tags session id php session rowcount

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


Implementing the Environment:

PHP 5.4.24
MySQL 5.6.19
OS X 10.9.4/apache 2.2.26

One, the code

CREATE TABLE ' Session ' (' Skey ' char (+) CHARACTER SET ASCII not NULL, ' data ' text COLLATE utf8mb4_bin, ' expire ' int (one) 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, password, etc. that are required to connect to the database, and so on, are not changed in the code, so you can avoid referencing it in a function by using a constant in the form of a global. */define (' Session_dns ', ' mysql:host=localhost;dbname=db;charset=utf8mb4 ');d efine (' session_usr ', ' USR ');d efine (' Session_pwd ', ' PWD ');d efine (' Session_maxlifetime ', Get_cfg_var (' session.gc_maxlifetime '));//Create PDO connection// A persistent connection can provide a 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) {}}//A custom session's Open function function Sessionmysqlopen ($savePath, $sessionName) {return TRUE;} The close function of the custom session functions Sessionmysqlclose () {return TRUE;} /** because the user submitted data will not be saved directly to the session, there is no injection problem in the general situation. * and SQL statements that process session data are not used more than once. Therefore, the benefits of preprocessing function can not be reflected. * Therefore, the actual project can not be dogmatic use of pre-processing functions. In the *//** sessionmysqlread () function, the SessionID is first judged by the existence of a SELECT count (*). * Because the MySQL database provides support for Select to Pdostatement::rowcount (), the actual project can be judged directly using RowCount (). *///the read function of the custom session//SQL statement adds "exPire > Time () "To avoid reading the expired session. 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//expire field stores the data for the current time +session lifetime, which indicates that the session fails 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;}} The GC function of the custom session functions SESSIONMYSQLGC ($lifetime) {try {$dbh = getconnection (); $sql = ' DELETE from ' Session ' WHERE exp Ire <? '; $stmt = $dbh->prepare ($sql), $stmt->execute (Time ()), $DBH = Null;return TRUE;} catch (Exception $e) {return FALSE;}} The session ID setting function for a custom session is/** because the SID and Session_id () are not valid until session_start (), so use $_get[session_name ()] and $_cookie[ Session_name ()] for testing. * If both are empty, the session is not established and the session ID needs to be set for the new session. * Using the MySQL database to get UUID as 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 ();}

Second, Introduction

Using MySQL to save the session, you need to save three key data: Session ID, session data, session lifetime.
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 permits, you can try using the memory engine.
Save the column of session data, if necessary, you can use the UTF8 or utf8mb4 character set, save the column of session ID is not necessary, generally use the ASCII character set, you can save the cost of storage.
The column that holds the session lifetime can be designed according to the engineering needs. such as datetime type, timestamp type, int type. for datetime, int types, you can save session build time or expiration time.
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.
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.
When the read function gets the data and returns it, PHP automatically deserializes it, and generally does not make changes to the data.
The date parameter that PHP passes to the Write function is the session data after serialization, which can be saved directly, and the data should not be changed in general.
In accordance with the logic of this code, the PHP configuration options regarding session lifetime settings are no longer valid, this value can be self-maintained, not necessarily need to be obtained through Get_cfg_var.
Sessionmysqlid () function is to avoid large users, multiple Web servers in the case of collisions, the general situation of PHP automatically generated session ID is to meet user requirements.
No more.

Third, the demand

When the user is very large and needs more than one server to provide the application, using the MySQL storage session relative to the use of the session file has some advantages. For example, there are minimal storage overhead, such as avoiding the complexity of file sharing, such as better avoiding collisions, such as better performance compared to session file sharing. In general, the problem with session files is almost explosive if the problem caused by using a database save session increases linearly when traffic spikes. Well, for a more straightforward statement: If your application is not a large number of users, in fact, let PHP handle the session itself is good, there is no need to consider MySQL.


How to be a member login system (use PHP session but do not use the TXT file to save user information in MySQL)

This problem can be used to store the array, the final output of a delimited string, is a multi-string operation.
I can send you a sample code.

A full source program or class that uses a MySQL database to store a session and also store it with a cookie.

$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");//Get the maximum expiration date 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 "

  • MYSQL Error: ". Mysql_error ()."
  • ";
    Die ();
    }
    if (!mysql_select_db ($GB _dbname, $SESS _dbh)) {
    echo "
  • MYSQL Error: ". Mysql_error ()."
  • ";
    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 >>

    http://www.bkjia.com/PHPjc/876528.html www.bkjia.com true http://www.bkjia.com/PHPjc/876528.html techarticle PHP uses MySQL to save the session implementation ideas and sample code, mysqlsession implementation of the environment: PHP 5.4.24 MySQL 5.6.19 OS X 10.9.4/apache 2.2.261, Code CREATE TABLE ' Session ' (' Ske ...

  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.