PHP Session Management using MySQL and PDO
While working on WiredCMS, I came across an Issue that I wanted to take care of while it was fresh in my mind. How do I store sessions in a database, rather than a flat file?
There are several reasons I wanted to do this. The main reason being the ability to load balance if needed, and because then I can query sessions from the database to see who is logged in much easier as well.
I came across a great article from DevShed giving an overview of how to do this. The only difference was that the author of this article used his own proprietary functions to query the database. Below is my finished SessionManager class, as well as the DbCreator class to create the PDO object, using the PDO abstraction layer to connect to the database.
dbcreator.inc.php
- <?php
- /**
- *DBCreator will create a PDO object that corresponds to database settings
- *If more than one server/host or database is used, this will make the change
- *pretty seamless.
- *@author Mike Conway
- *@copyright 2008, Mike Conway
- *@since 01-May-2008
- */
- class DBCreator{
- /**
- *creates a PDO object - will not initialize duplicate objects
- *@param $database - The database to attach the PDO object to
- *@return - returns the PDO object for the given database.
- */
- public static function GetDbObject($database){
- // We need to make this more secure - dont store UN and PW in plain text.
- //Also, make it more dynamic to accept data from an install script for a fully unknown database
- static $db_pdo;
- if (!isset($db_pdo)){
- $db_pdo = array();
- }
- if (array_key_exists($database, $db_pdo) == false){
- $host = 'dbhost';
- $user = 'dbuser';
- $pass = 'dbpass';
- $dsn = 'mysql:dbname=' . $database .';host='. $host;
- try {
- $pdo = new PDO($dsn, $user, $pass);
- } catch (PDOException $e) {
- die('Connection failed: ' . $e->getMessage());
- }
- $db_pdo[$database] = $pdo;
- }
- return $db_pdo[$database];
- }
- }
- ?>
sessionmanager.inc.php
- <?php
- /*
- * Created on May 22, 2008
- *
- * To change the template for this generated file go to
- * Window - Preferences - PHPeclipse - PHP - Code Templates
- * from http://www.devshed.com/c/a/PHP/Storing-PHP-Sessions-in-a-Database/
- * require_once("sessions.php");
- * $sess = new SessionManager();
- * session_start();
- */
- class SessionManager {
- var $life_time;
- private $DB;
- function __construct() {
- // Read the maxlifetime setting from PHP
- $this->life_time = get_cfg_var("session.gc_maxlifetime");
- $this->DB = DBCreator::GetDbObject('db_name');
- // Register this object as the session handler
- session_set_save_handler(
- array( &$this, "open" ),
- array( &$this, "close" ),
- array( &$this, "read" ),
- array( &$this, "write"),
- array( &$this, "destroy"),
- array( &$this, "gc" )
- );
- }
- function open( $save_path, $session_name ) {
- global $sess_save_path;
- $sess_save_path = $save_path;
- // Don't need to do anything. Just return TRUE.
- return true;
- }
- function close() {
- return true;
- }
- function read($id) {
- // Set empty result
- $data = '';
- // Fetch session data from the selected database
- $time = time();
- $sql = $this->DB->prepare("SELECT `session_data` FROM `cms_sessions` WHERE `session_id` = :id AND `expires` > :time");
- $sql->bindParam(':id',$id);
- $sql->bindParam(':time',$time);
- $sql->execute();
- $a = $sql->rowCount();
- if($a > 0) {
- $row = $sql->fetch(PDO::FETCH_ASSOC);
- $data = $row['session_data'];
- }
- return $data;
- }
- function write( $id, $data ) {
- // Build query
- $time = time() + $this->life_time;
- $sql = $this->DB->prepare("REPLACE `cms_sessions` (`session_id`,`session_data`,`expires`) VALUES(:id, :data, :time)");
- $sql->bindParam(':id',$id);
- $sql->bindParam(':data',$data);
- $sql->bindParam(':time',$time);
- $sql->execute();
- return TRUE;
- }
- function destroy( $id ) {
- // Build query
- $sql = $this->DB->prepare("DELETE FROM `cms_sessions` WHERE `session_id` = :id");
- $sql->bindParam(':id',$id);
- $sql->execute();
- return TRUE;
- }
- function gc() {
- // Garbage Collection
- // Build DELETE query. Delete all records who have passed the expiration time
- $sql = $this->DB->prepare("DELETE FROM `cms_sessions` WHERE `expires` < UNIX_TIMESTAMP();");
- $sql->execute();
- // Always return TRUE
- return true;
- }
- }
- ?>
To implement the SessionManager class:
- require_once("./includes/sessionmanager.inc.php");
- $sess = new SessionManager();
- session_start();
OS Independence
Yesterday I wrote this post, and it got lost due to a tragic fatality of my Firefox browser, So I will try again:
Lately I have been getting bit by the Linux bug, and I have had no outlet. Sure, I tried surfing the Linux forums, mailing lists and just tried to keep up on Linux releases, but that can only go so far. I needed hands-on play-time!
A client of mine that I do some work for turned me onto the idea of running Virtual Machines. While I wasn't sure my PC was quite up to par as far as specs go for this type of use, I gave it a shot.
The first Virtualization App I tried out was Microsoft VirtualPC 2007. I got as far as creating the first VM, and realized it would not do Linux, and scrapped it.
I started browsing the internet for free open source solutions, when I came across VMWare's VMserver. This is simply the free desktop version of their enterprise Virtual Center solution. Not only does VMserver support Linux, it supports UNIX and MAC OS X.
I spent the better part of Friday night and Saturday playing around with the different features, and installed Linux several times successfully. Man, it feels good to have Linux back. For my fiance, who has missed her Mac Mini since I "convinced" her to sell it, I installed OS X. While I haven't been able to test out this install, it does seem to have worked correctly.
As far as Linux, For those of you wondering what flavor I went for, I chose my favorite: Debian. I'm running a mixed box with the SID kernel tools, and mostly stable packages. I also started with GNOME, but after a reinstall, I went with XFCE, and I must say, I am in love with the XFCE environment. It's resource light, not bloated and looks better than Gnome.
Let this mark the trigger for more Linux posts!
GRR
So I had this wonderful Post typed up, and was trying to attach an image and Firefox Crashed, because my PC at work is very unstable and that happens alot, and I am too lazy to use MS Word to write posts.. I lost it all. Maybe I'll get the motivation to write it all again.
WiredCMS
Ive been spending alot of my free time on WiredCMS the last few days, and the admin backend is starting to take shape. Not only can a user log in or out, you can also register a new account. There is also permissions checking in force to validate you to the admin backend. From here, you can view the user list, and delete users. Soon I will have user accounts getting updated, but I have to create user profiles for this to work.
I would be happy to show it all to you, but unfortunately it is still unstyled. Once the backend is complete I have plans to put up a demo. Stay Tuned!
The Eclipse SDK
The past week or so, I have been diligently working with a CMS I am writing as a project, as well as trying to learn ColdFusion. ColdFusion itself is pretty easy to understand, but it is not the best language to use an editor like Notepad++ on, as it has many MANY attributes for tags and functions.
The person I have been working very closely with this on turned me onto an application called Eclipse. This base application is aimed at Java development, but with an add on called CFEclipse, it is turned into a powerful Development environment for ColdFusion.
Once I started using Eclipse, I looked around for add ons for PHP, which I found with PHP Eclipse. On top of that, I also found for Javascript and jQuery development.
After the past week or so of using Eclipse, i don't think I can ever go back to using a plain text editor, or even Notepad++, which is my old editor of choice. With all the tools at my disposal in Eclipse, it takes the burden off of developing apps.
My favorite feature of Eclipse is the fact that hovering over any function, self defined or built-in will result in a tool tip showing arguments required. It also allows you to hover over a variable and shows all instances int eh entire application that the variable exists.
Now, at the beginning of this post, I mentioned that I am writing a CMS. This is true, and in the very early stages of development. So far, user functions include session based login and logout, with a handful of other features. Once this is more mature, I will consider posting a Demo site. I have big plans for this, and Hope that it can replace Drupal for Wiredbyte some day.
The details so far include:
- This is written in PHP 5 using object Oriented programming
- Templating will be handled by the Smarty engine
- The user Interface will be Powered by jQuery, but fully degradeable to standard static HTML (blah)
Maploco!
Ahhhh, Mondays - the time where no one wants to work, yet the most work of the week is generated.
I was browsing my usual haunts online and i came across a small map that shows the relative geolocations of site visitors. I thought this was quite interesting and visited the parent site of the image - MapLoco.
The setup takes all of about 3 seconds, just choose the map graphic and the symbol to use, then copy and paste the HTML they generate for you. I have added this to the sidebar on the site to see how well it works. Unfortunately I made it a little small, but it can be enlarged by simply clicking the image.
DOH!
I was working on some other projects today when I had a small mental 'sticky' note come to mind... I forgot to add the "new account' links to the site! DOH!
While i get this fixed, you can use this to take you to a page to create a new account, or remind you of a forgotten password. I apologize for this oversight.
During this time, I have also noticed that user profiles are not working. These will be up shortly.
Update:Profiles are now functional!
Site Upgrade - Done
Sorry for the lack of warning, but the site is officially updated! I still have to go through all of the old posts and update them, so some posts will be teasers, and not have images. This will change over the next week or so.
Wiki and Forums are unavailable at this time, I'm sorry for the inconvenience. Also, as old posts get updated, thier URL paths are going to change, so bookmarks to them will become broken - just fair warning.
All-in-all I am very happy with the update. What does everyone think?
Modifying the jQuery UI Sortables Serial Function
Since jQuery interface is no longer supported, Most developers are moving to jQuery UI. The UI sortables is taking the place of Interface, and can even serialize the results.
The serialize function however lacks one large feature: a useful way to serialize the data.
By default, the string returned looks similar to:
- element[]=1&element[]=2
The downfall to using the default serialize method, is that the sortable IDs must have a _ in them, otherwise serialize will return blank results.
I do not want to use _ in my ids, as the function to generate the data was already written and working. I also need to pass more data than one element to my functions for processing. The Interface serialize string worked perfectly for what I was trying to do, and was actually a hash that looked like:
- elementParent1[]=Child1&elementParent1[]=Child2
I therefore changed the serialize function in UI sortables to return the more verbose string, which was really quite easy.
On lines 143-146 of ui.sortables.js:
- items.each(function() {
- var res = ($(this).attr(o.attribute || 'id') || '').match(o.expression || (/(.+)[-=_](.+)/));
- if(res) str.push((o.key || res[1])+'[]='+(o.key ? res[1] : res[2]));
- });
Change this function to:
- items.each(function() {
- str.push($(this).parent().attr("id") +'[]='+this.getAttribute(o.attribute || 'id'));
- });
Once this is done, save the modified file, and you are set!
Interface Demo with jQuery UI - Part 1 - Correction
The example written yesterday for the UI sortables demo is incorrect. I was using UI 1.5b, and downloaded 1.5b2 this morning.
[nodeinfo|95|body|0.97641300 1209130936]The example written yesterday for the UI sortables demo is incorrect. I was using UI 1.5b, and downloaded 1.5b2 this morning.
The old requirements for sortables no longer applies, so please forgive me as I post the correction to the includes.
Include the following scripts in your HTML header:
- <script type="text/javascript" src="jquery.ui/1.5b2/jquery-1.2.4a.js"></script>
- <script type="text/javascript" src="jquery.ui/1.5b2/ui.base.js"></script>
- <script type="text/javascript" src="jquery.ui/1.5b2/ui.sortable.js"></script>
The full example has also been updated to reflect this change.
