storing the sessions in the database

[Overunner]

I've only come across one database session handler script, which I never got to work. So I wonder if you could recommend me (preferably a working) one?

[BillyJoe]

HEre I send you some code I wrote long time ago. I still using it without any problems. In my sessions include file I have some more functions to manage sessions, but I've not posted because aren't necessary for your proposal.

session_set_save_handler("sess_start", "sess_close", "sess_read", "sess_write", "sess_destroy", "sess_gc");

function sess_start($save_path, $_session_name)
{
    global $_DB;
    trace("start sess_start($save_path, $_session_name)",__FILE__,__LINE__);
    return true;
}

function sess_close()
{
    trace('start sess_close()');
    return true;
}

function sess_read($session_id) 
{
    trace("start sess_read($session_id)",__FILE__,__LINE__);
    global $_DB;
    $data = '';


    $sql = "SELECT * FROM " . SESSIONS_TABLE ." WHERE session_id = '$session_id'";
    if ( !($result = $_DB['db']->sql_query($sql)) ) {
        trace('error reading session from db',__FILE__,__LINE__);
        trace( $sql,__FILE__,__LINE__);
    }
    else{
        //session_id  session_user_id  session_start  session_time  session_ip  session_page  session_logged_in 
        if ( $row = $_DB['db']->sql_fetchrow($result) ){
            foreach( $row as $key=>$val) {
                if ( !preg_match("/\d/",$key) ){
                    trace($key.'='.$val);
                    $data .= $key.'|'.serialize($val);
                }
            }
        }
        else{
            //No session vars already set
            $data = '';
        }
    }
    //session_user_id|s:1:"0"; #wrong
    //session_user_id|s:1:"2"; #well
    //$uid = "2";
    //$data = 'session_user_id|'.serialize($uid);
    trace('sess_read() return = '.$data,__FILE__,__LINE__);
    return $data;
}


function sess_write($session_id, $session_data) {
    trace("start sess_write($session_id, $session_data)",__FILE__,__LINE__);
    global $_DB;

    //TO DO ip check
    if(false){
        session_destroy();
        exit;
        return '';
    }


    //UNSERIALIZE DATA & prepare it to insert into database.

    trace('preparing to write session data to db');
    $data_vars = split(';',$session_data);            //it removes ';' from string. Needs to be added again for unserialize 
    $sql_update = '';
    $sql_insert_vars = '';
    $sql_insert_values = '';
    $first_var = true;
    foreach ( $data_vars as $svar){
        if ( preg_match('/session_id/', $svar) ){
            //do nothing;
        }
        elseif ( !empty($svar) ){
            list( $varname, $sdata) = split('\|', $svar);
            trace('$varname='.$varname.'    $sdata='.$sdata);
            $value = unserialize($sdata.';');

            if ($first_var){
                $sql_update .=    ' '.$varname.' = \''.$value.'\'';
                $sql_insert_vars .= $varname;
                $sql_insert_values .= '\''.$value.'\'';
                $first_var = false;
            }
            else{
                $sql_update .=    ', '.$varname.' = \''.$value.'\'';
                $sql_insert_vars .= ', '.$varname;
                $sql_insert_values .= ', \''.$value.'\'';
            }
        }
    }


    $sql = 'SELECT * FROM '.SESSIONS_TABLE.' WHERE session_id = \''.$session_id.'\';';
    if ( !($result = $_DB['db']->sql_query($sql))  ){
        trace( 'error trying to read session in db<br>');
        trace('error QUERY:'.$sql);
    }
    else{
        if($row = $_DB['db']->sql_fetchrow()){    //Session already exists
            trace($row['session_user_id']);
            $sql='UPDATE '.SESSIONS_TABLE.' SET '.$sql_update.
                ' WHERE session_id = \''.$session_id.'\' LIMIT 1 ;';

        }
        else{                                   
            $sql = 'INSERT INTO '.SESSIONS_TABLE.' ('.$sql_insert_vars.', session_id ) VALUES ('.$sql_insert_values.', \''.$session_id.'\');';
        }

        if ( !($result = $_DB['db']->sql_query($sql))  ){
            trace('error saving session on db<br>');
            trace('error QUERY:'.$sql);
        }
        else{
            trace('Session save success');
        }


    }

    return true;
}


function sess_destroy($session_id) {
    global $_DB;
    trace("start sess_destroy($session_id)");
    $sql = 'DELETE FROM '.SESSIONS_TABLE.' WHERE session_id = \''.$session_id.'\';';
    if ( !($result = $_DB['db']->sql_query($sql))  ){
        trace( 'error trying to read session in db<br>');
        trace('error QUERY:'.$sql);
    }
    return true;
}


function sess_gc($_sess_gc_maxlifetime=20) {
    debug_kernel("start sess_gc($_sess_gc_maxlifetime)");

   #DELETE FROM SESSIONS_TABLE WHERE session_time < ". (time() - $_sess_gc_maxlifetime));
   #OPTIMIZE TABLE 

    return true;
} 

[BillyJoe]

$_DB['db'] have the database handler (previous connection).

Sessions database table:
CREATE TABLE nitrophp_sessions (
session_id varchar(32) NOT NULL default '',
session_user_id mediumint(8) NOT NULL default '0',
session_user_group_id mediumint(8) NOT NULL default '0',
session_user_fullname varchar(16) NOT NULL default '',
session_start int(11) NOT NULL default '0',
session_time int(11) NOT NULL default '0',
session_ip varchar(8) NOT NULL default '0',
autologinid varchar(32) NOT NULL default '',
session_page int(11) NOT NULL default '0',
session_logged_in tinyint(1) NOT NULL default '0',
PRIMARY KEY (session_id),
KEY session_user_id (session_user_id),
KEY session_id_ip_user_id (session_id,session_ip,session_user_id)
) TYPE=MyISAM;

this structure can vary depending of your application needs. (ex: probably you wont need user_fullname, user_group_id)

[Overunner]

Thanks BillyJoe!

[pht]

I know this one:

http://www.phpclasses.org/browse/package/1624.html

and documentation:

http://www.phpclasses.org/browse/fil...me/manual.html

[lazy_yogi]

PHP allows you to set your own session handler.
This article gives you the code and explains how it is used. And you use it the same
way as normal sessions after that with the super global $_SESSION so it's all nice and easy as pie.

http://www.zend.com/zend/spotlight/c...lery-wade8.php