Sessions being stored in database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
prleo1
Forum Newbie
Posts: 4
Joined: Fri Jan 19, 2007 1:07 pm

Sessions being stored in database

Post by prleo1 »

I am using PHP5, Apache 2.x, MySQL 5.

My issue is that I am trying to store PHP sessions in a MYSQL database.
I have created other projects for my company that use the PHPSession flat files.
However, I would like to keep track of the people who are logging in and out and what pages they visit the most.

My question is in order to capture PHP Sessions in a database do I need to change the configuration file, php.ini to allow this?

session.save_handler?
session.save_path? pointing to the MySQL database file?

Any help would be greatly appreciated. And yes, I have looked through the documentation, but I have not found the answer.

{PR}
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Did you look through the user comments for session_set_save_handler?

for reference, here's an OOP savehandler I wrote that implements the Zend save handler. feel free to use whatever you can from it... it uses adodblite

Code: Select all

<?php
class MC2_Session_Adodblite implements Zend_Session_SaveHandler_Interface
{
	/**
	 * Contains the data access object
	 */
	private $_db = null;
	
	/**
	 * Contains user id
	 * @var integer
	 */
	private $_userId = -1;
	
	/**
	 * Name of sessions table
	 */
	private $_sessionTable = 'sessions';
	
	/**
	 * User's IP address
	 */
	private $_remoteAddr = null;

    public function __construct($db, $userId = null, $sessionTable = null)
    {
		$this->_db = $db;
		$this->_remoteAddr = $_SERVER['REMOTE_ADDR'];
		if (!is_null($userId))
		{
            $this->setUserId($userId);
		}
    	if (!is_null($sessionTable))
		{
            $this->_sessionTable = $sessionTable;
		}
    }
    
    public function open($savepath, $name)
    {
		return true;
    }
    
    public function close()
    {
		/**
		 * No need to close my db connection
		 * since it's shared with my application
		 */
		return true;
    }
    
    public function read($id)
    {
		$query = "
			SELECT " . $this->_sessionTable . ".data
			FROM " . $this->_sessionTable . "
			WHERE " . $this->_sessionTable . ".id = ?
		";
		if ($result = $this->_db->execute($query, array($id)))
		{
			return $result->fields['data'];
		}
		return null;
    }
    
    /**
     * This method writes the current session data to the database
     * 
     * @param $id integer
     * @param $data string (serialized session data)
     */
    public function write($id, $data)
    {
		$deleteQuery = "
			DELETE FROM " . $this->_sessionTable . "
			WHERE " . $this->_sessionTable . ".id = ?
		";
		$this->_db->execute($deleteQuery, array($id));
		
		$insertQuery = "
			INSERT INTO " . $this->_sessionTable . "
			(id, user_id, user_ip, data, access)
			VALUES (?, ?, ?, ?, ?)
		";
		$values = array(
			$id,
			$this->_userId,
			$this->_remoteAddr,
			$data,
			time()
		);
		$result = $this->_db->execute($insertQuery, $values);
		return ($result !== false);
    }
    
    public function destroy($id)
    {
		$deleteQuery = "
			DELETE FROM " . $this->_sessionTable . "
			WHERE " . $this->_sessionTable . ".id = ?
		";
		$this->_db->execute($deleteQuery, array($id));
    }
    
    public function gc($maxlifetime)
    {
        $old = time() - $maxlifetime;
		$deleteQuery = "
			DELETE FROM " . $this->_sessionTable . "
			WHERE " . $this->_sessionTable . ".access < ?
		";
		$this->_db->execute($deleteQuery, array($old));
    }
    
    public function setUserId($id)
    {
		if (!ctype_digit($id))
		{
			throw new Exception('Invalid parameter passed to ' . __METHOD__ . ': expecting integer');
		}
		$this->_userId = $id;
    }
}
?>
Play around with it a bit first, and then ask questions as they come up. It implements Zend_Session_SaveHandler_Interface, but it could just as easily be called like this without implementing that by doing something like:

Code: Select all

function __construct() {
   session_set_save_handler(array(&$this, 'open'),
                             array(&$this, 'close'),
                             array(&$this, 'read'),
                             array(&$this, 'write'),
                             array(&$this, 'destroy'),
                             array(&$this, 'gc'));
   register_shutdown_function('session_write_close');
   session_start();
}
EDIT: OH! I almost forgot my db table schema:

Code: Select all

CREATE TABLE `sessions` (
  `id` varchar(40) NOT NULL,
  `user_id` mediumint(9) default NULL,
  `user_ip` varchar(15) default NULL,
  `data` text,
  `access` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;
prleo1
Forum Newbie
Posts: 4
Joined: Fri Jan 19, 2007 1:07 pm

Post by prleo1 »

Cool name!

Is it safe to say that I do not have to change the php.ini file in order to save the sessions in a MySQL database?

Thank you for your quick reply.

{PR}
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

you shouldn't have to.. but there are a few gotchas noted in the user comments of session_set_savehandler such as:
Note that if session.auto_start is set to On in the php.ini, your session_set_save_handler will return false as the session has already been initialized.

If you are finding that your code works OK on one machine but doesn't work on another, check to see if session.auto_start is set to On
prleo1
Forum Newbie
Posts: 4
Joined: Fri Jan 19, 2007 1:07 pm

Post by prleo1 »

Ninja Space goat...I greatly appreciate your help! You got me going in the right direction.
{PR}
Post Reply