How do I save PHP session data to a database instead of in the file system?
<p>I have two websites, One is TLS and one is not, both are for the same client but I need the websites to share with each other (and only each other) common data for <em>users</em>, <em>orders</em>, <em>accounts</em> etc.</p>
<p>This would normally be done with <code>$_SESSION</code> data but I obviously these can't work across other sites, and I have found that I can store session data in a database (MySQL) rather than in the file system.</p>
<p>I have dug around and found <a href="http://culttt.com/2013/02/04/how-to-save-php-sessions-to-a-database/" rel="noreferrer">This useful guide</a> as well as this older but
<a href="http://www.tonymarston.net/php-mysql/session-handler.html" rel="noreferrer">useful guide</a>. I also found <a href="http://www.codeproject.com/Articles/798978/Session-Storage-in-a-MySQL-Database" rel="noreferrer">this guide</a> which has slightly more up to date MySQL.</p>
<p>I have written an interface class but it only partly works, it stores the session data in the database, but it doesn't retrieve it. I have also used the <a href="http://php.net/manual/en/function.session-set-save-handler.php" rel="noreferrer">suggested method from the PHP manual</a>.</p>
<p><strong>My MySQL</strong> (as copied from first couple of the above links):</p>
<pre><code>CREATE TABLE `sessions` (
  `id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `access` int(10) NOT NULL,
  `data` text COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDb DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
</code></pre>
<blockquote>
<p><strong>Please Note:</strong>
Before I show you my interface class please know that the Db connetion uses my own custom made interface and that works perfectly, in itself.</p>
<p>The <code>$sessionDBconnectionUrl</code> contains the Session Database connection details as I am keeping sessions on a seperate Database from the main website contents.</p>
</blockquote>
<p><strong>My interface class</strong> (as based on all the above links)</p>
<pre><code>&lt;?php
/***
 * Created by PhpStorm.
 ***/
class HafSessionHandler implements SessionHandler {
    private $database = null;
    
    public function __construct($sessionDBconnectionUrl){

        if(!empty($sessionDBconnectionUrl) &amp;&amp; file_exists($_SERVER['DOCUMENT_ROOT'].$sessionDBconnectionUrl)) {
            require_once &quot;class.dataBase.php&quot;;
            // Instantiate new Database object
            $this-&gt;database = new Database($sessionDBconnectionUrl);
        }
        else {
            error_log(&quot;Session could not initialise class.&quot;);
        }
        
    }

    /**
     * Open
     */
    public function open($savepath, $id){
         $openRow = $this-&gt;database-&gt;getSelect(&quot;SELECT `data` FROM sessions WHERE id = ? LIMIT 1&quot;,$id);
    if($this-&gt;database-&gt;selectRowsFoundCounter() == 1){
        // Return True
        return $openRow['data'];
        }
    else {
        // Return False
        return ' ';
    }
    /**
     * Read
     */
    public function read($id)
    {
        // Set query
        $readRow = $this-&gt;database-&gt;getSelect('SELECT `data` FROM sessions WHERE id = ? LIMIT 1', $id,TRUE);
        if ($this-&gt;database-&gt;selectRowsFoundCounter() &gt; 0) {
            return $readRow['data'];
        } else {
            error_log(&quot;could not read session id &quot;.$id);
            return '';
        }
    }

    /**
     * Write
     */
    public function write($id, $data)
    {
        $access = time();
        // Set query
        $dataReplace[0] = $id;
        $dataReplace[1] = $access;
        $dataReplace[2] = $data;
        if ($this-&gt;database-&gt;noReturnQuery('REPLACE INTO sessions(id,access,`data`) VALUES (?, ?, ?)', $dataReplace)) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    /**
     * Destroy
     */
    public function destroy($id)
    {
        // Set query
        if ($this-&gt;database-&gt;noReturnQuery('DELETE * FROM sessions WHERE id = ? ', $id)) {
            return TRUE;
        } else {

            return FALSE;
        }
    }
    /**
     * Close
     */
    public function close(){
        // Close the database connection
        // If successful
        if($this-&gt;database-&gt;dbiLink-&gt;close){
            // Return True
            return true;
        }
        // Return False
        return false;
    }

    /**
     * Garbage Collection
     */
    public function gc($max)
    {
        // Calculate what is to be deemed old
        $old = time() - $max;

        // Set query
        if ($this-&gt;database-&gt;noReturnQuery('DELETE * FROM sessions WHERE access &lt; ?', $old)) {
            return TRUE;
        } else {
            return FALSE;
        }
    }
    
    public function __destruct()
    {
        $this-&gt;close();
    }

}
</code></pre>
<p><strong>My Test Page</strong> (written from scratch!)</p>
<pre><code>&lt;?php
require &quot;class.sessionHandler.inc.php&quot;;
$HSH = new HafSessionHandler(&quot;connection.session.dbxlink.php&quot;);
session_set_save_handler( $HSH, TRUE );
session_start();

print &quot;&lt;p&gt;Hello this is an index page&lt;/p&gt;&quot;;
$_SESSION['horses'] = &quot;treesx3&quot;;
$_SESSION['tiespan'] = (int)$_SESSION['tiespan']+7;

print &quot;&lt;p&gt;There should be some session data in the database now. &lt;a href='index3.php'&gt;link&lt;/a&gt;&lt;/p&gt;&quot;;
var_dump($_SESSION);


exit;
</code></pre>
<p><strong>Issue:</strong></p>
<p>The test pages I run save the data to the database ok but they do not seem to retrieve the data,</p>
<p>I have error logging enabled and no PHP errors are reported. No critical MySQL errors are reported.</p>
<p>Why doesn't it work?</p>