« Generic Object Oriented SQL interface for PHP
Asynchronous background execution with PHP »
Aug 02 2008

SQL backed preference system for PHP

Posted by fli in General, tags:


Sometimes you need to store information and settings that really doesn’t fit into any other SQL table, in these situations it’s nice to have a generic key-value driven preference system.

The preference system consists of multiple stores or trunks, each store is identified by a unique, arbitrary string and each store has multiple key-value pairs. The key-value pairs are dynamically created and flushed to database when the preference object is destroyed or when an explicit flush is requested.

This offers a very flexible system, illustrated by this example

$c = new MySQLConnector('localhost', 'username', 'password', 'db');
/* Create a preference store called 'store1' */
$store1 = new Prefs($c, 'store1');
/* Create a preference store called 'myprefs' */
$myprefs = new Prefs($c, 'myprefs');
 
$store1->some_setting = 10;
$myprefs->abcd = 1234;
$store1->abcd = 4321;
$myprefs->a_list = array(1, 2, 3, 4);
 
print $myprefs->abcd * $store1->some_setting;
 
/* Do an explicit write to the database */
$store1->Flush();

To build a system like this you’ll first need a SQL table in some database, I’ve called the table prefs. The table layout is very simple and only consists of two columns, the first is the store identifier and the second contains key-value pairs.

+-------+-------------+------+-----+---------+-------+
| FIELD | Type        | NULL | KEY | DEFAULT | Extra |
+-------+-------------+------+-----+---------+-------+
| ident | varchar(64) | NO   | PRI |         |       | 
| DATA  | text        | YES  |     | NULL    |       | 
+-------+-------------+------+-----+---------+-------+

You can create this table with the following SQL command

CREATE TABLE prefs (ident varchar(64), DATA text, PRIMARY KEY (ident))

The class itself Prefs is shown below

Note that this requires the SQL connector classes published earlier, if you don’t want to use those you’ll need to modify all SQL queries below to suite your needs.

File: prefs.inc

<?php
require_once('db_iface.inc');
 
/*
 * Automatic SQL-backed preference system
 */
class Prefs {
    /* Connection handle */
    private $m_con;
    /* Store identifier */
    private $m_ident;
    /* Contains key-value pairs */
    private $m_data = array();
 
    private $m_dirty = false;
    private $m_fetched = false;
 
    public function __construct($connector, $ident) {
        $this->m_con = $connector;
        $this->m_ident = addslashes($ident);
    }
 
    /* Write changes to database when object is destroyed */
    public function __destruct() {
        $this->doflush();
    }
 
    private function __get($nm) {
        $this->dofetch();
        if (!isset($this->m_data[$nm]))
            return null;
        else
            return $this->m_data[$nm];
    }
 
    private function __set($nm, $val) {
        $this->dofetch();
        $this->m_data[$nm] = $val;
        $this->m_dirty = true;
    }
 
    /* Do an explicit flush to the database */
    public function Flush()
    {
        $this->doflush();
    }
 
    /*
     * Write key-value pairs to database
     */
    private function doflush() {
        if (!$this->m_dirty)
            return;
 
        $data = addslashes(serialize($this->m_data));
        $ident = $this->m_ident;
        $this->m_con->query("select count(*) from prefs where ident = '$ident'");
        $res = $this->m_con->fetchRow();
        if ($res[0] == 1) {
            $this->m_con->query("update prefs set data = '$data' where ident = '$ident'");
        }
        else {
            $this->m_con->query("insert into prefs(ident, data) values('$ident', '$data')");
        }
 
        $this->m_dirty = false;
    }
 
    /*
     * Read key-value pairs from database
     */
    private function dofetch() {
        if ($this->m_fetched)
            return;
        $ident = $this->m_ident;
        $this->m_con->query("select data from prefs where ident = '$ident'");
        if ($this->m_con->getNumRows() == 1) {
            $res = $this->m_con->fetchRow();
            $this->m_data = unserialize(stripslashes($res[0]));
        }
        $this->m_fetched = true;
    }
}
 
?>

So how can this work then? the class uses the two magic constructs __set and __get to build automatic properties, there routines are called when an non-existing property is accessed.

For example

$obj->foo = 10;

This code would trigger __set in the class associated with $obj, it would be called with __set(”foo”, 10).
The same goes for

print $obj->foo

which would trigger __get(”foo”) in the class associated with $obj.
These implicit pairs are then stored in an array which is serialized and stored in a SQL database.

Note that this system makes use of serialize which is a PHP specific construct, this might make it difficult to access the data from other languages.

If you found this helpful, tell a friend or put a link back to this article on your own website/blog.

This entry was posted on Saturday, August 2nd, 2008 at 2:30 pm and is filed under General. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “SQL backed preference system for PHP”
  1. Ree says:
    October 6, 2008 at 2:06 pm

    Handy shit som man inte orkar skriva själv för alla små hack. Bra kämpat, tack! :)

  2.  
Leave a Reply