This class actually is not ment for direct use, it's more like an abstract class for working with database records as objects.

DBObject($table, $prefix)

constructor, pass table name and field prefix as parameters

getObject($id)

get object from database, return all fields by given id

getObjectList($status, $offset = 0, $limit = 100, $orderby = '')

get object list

getObjectListWithParams($params = array(), $status, $offset = 0, $limit = 100, $orderby = '')

get object list with params - if you need to specify additional params, not just status, order and limit

delete($id)

delete record by given id

changeStatus($id, $status)

change status by given id

showErrors()

return error list formatted as unordered list

updateField($id, $field, $value)

update single field by given id

updateRecord($id, $fields)

update whole record by given id and array of field names and values

insertRecord($fields)

insrt new record into database

buildFieldsArray($fields)

build array of sql statement parts from fields array

raisePriority($id, $params = array())

raise record priority by given id and additional params

lowerPriority($id, $params = array())

lower priority by given id and additional params

getParentObject($id)

return parent object of record with specified id

getChildObjects($id)

get child object list of record with specified id

First of all you will need a database structure with some deault fields and rules how you define your database.

All fields in table start with the same prefix, let's say we would like to build article system, we will have a table called articles, and fields, each starting with art_ prefix. So a regular database structure would look like:

art_id
art_status
art_priority
art_parent

Of course these are the basic fields we can have and handle them with DBObject class. The DBObject class was initially written as some sort of universal class for database record manipulations in some CMS solutions, so it's suitable as for article, comment, news management, as well as for building multilingual menus and any other types of databases.

Why do we need those 4 default fields? Well they are the very basic fields of each record: id is a primary key auto incremental integer, art_status is the record status - whether to show it public or not, art_priority is the priority of the record, if we have multiple articles in a category, we can order them by priority and get the article placement as we wish. The same about menu items - there are times when you have to move a menu item up or down, and this is where the priority field comes and helps you. Finally the art_parent field will let you define a structure of records, like the menu items we were talking just a second ago - you can create a simple structure of menu by just setting the parent value.

So, as this is a more or less abstract class, we will not use it directly:

Class Article extends DBObject{

}

$article = new Article('article', 'art);
$articleList = $article -> getObjectList('active');

foreach ($articleList as $articleItem){
    // perform some action with $articleItem
}

The manipulation of database records is just as simple, just use the methods provided. For example, let's do some menu manipulation:

// let's pretend we have a menu table
// m_id
// m_status
// m_parent
// m_priority
// m_title
// with some data in it

Class Menu extends DBObject{

}
$menu = new Menu('menu' 'm');

// now let's move menu item with id 15 up, where the menu parent is menu item with id 7
$menu -> raisePriority(15, array('m_parent' => '7');

// the same thing about moving menu item down, let's add some other parameters, like language
$menu -> lowerPriority(11, array('m_parent' => '2', 'm_language' => 'en');

// delete the menu item
$menu -> delete(15);

Actually nobody forbids you to add some code of your own to extended class, like building a XHTML menu in the menu class.

In my opinion a class like this one can be used for a really wide range of solutions. I use it in my own built CMS solutions as well as simple blogs and other type of PHP software.

In order for this code to function correctly you will need a MySQL Singleton class, or you can rewrite the DBObject class so that it uses the mysql functions directly.

Actually the MySQL singleton class is nothing special - that's also a class written by me. All it does is compile the mysql_* family functions into one class, so that I don't have to write mysql_query(), I can just write $sql -> query(). The MySQL singleton class is included in the download.zip.

<?php


    // abstract class for working with table records as database objects
    Class DBObject{
        
        var $tableName;
        var $fieldPrefix;
        // sql library that you can use to make queries, fetch results, etc.
        var $sqlLib;
        
        // constructor, pass table name and field prefix as parameters
        function DBObject($table, $prefix){
            // set table name
            if (!empty($table)){
                $this -> tableName = $table;
            }else{
                $this -> errors[] = 'DBObject: Table name cannot be empty!';
            }
            
            // set field prefix
            if (!empty($prefix)){
                $this -> fieldPrefix = $prefix;
            }else{
                $this -> errors[] = 'DBObject: Fieldname prefix cannot be empty!';
            }
            
            // instantinate Singleton pattern of MySQL class
            $this -> sqlLib = MySQL::instance();
        }
        
        // get object from database, return all fields by given id
        function getObject($id){
            $qry = "SELECT * FROM " .  $this -> tableName . " WHERE " . $this -> fieldPrefix . "id = $id";
            $this -> sqlLib -> query($qry);
            return $this -> sqlLib -> fetchAssoc();
        }
        
        // get object list
        function getObjectList($status, $offset = 0, $limit = 100, $orderby = ''){
            $qry = "SELECT * FROM " . $this -> tableName . (!empty($status) ? " WHERE "
                     . $this -> fieldPrefix . "status = '$status'" : '') . (!empty($orderby) ? ' ORDER BY '
					 . $orderby : '') . " LIMIT $offset, $limit";
            $this -> sqlLib -> query($qry);
            return $this -> sqlLib -> getAssoc();
        }
        
        // get object list with params - if you need to specify additional params, not just status, order and limit
        function getObjectListWithParams($params = array(), $status, $offset = 0, $limit = 100, $orderby = ''){
            $qry = "SELECT * FROM " . $this -> tableName . " WHERE " 
                     . join(' AND ', $this -> buildFieldsArray($fields)) . (!empty($status) ? ' AND '
		     . $this -> fieldPrefix . "status = '$status'" : '') . (!empty($orderby) ? ' ORDER BY '
		     . $orderby : '') . " LIMIT $offset, $limit";
            $this -> sqlLib -> query($qry);
            return $this -> sqlLib -> getAssoc();
        }
        
        // delete record by given id
        function delete($id){
            $qry = "DELETE FROM " .  $this -> tableName . " WHERE " . $this -> fieldPrefix . "id = $id";
            $this -> sqlLib -> query($qry);
        }
        
        // change status by given id
        function changeStatus($id, $status){
            $qry = "UPDATE " . $this -> tableName . " SET " . $this -> fieldPrefix . "status = '$status'";
            $this -> sqlLib -> query($qry);
        }
        
        // return error list formatted as unordered list
        function showErrors(){
            if (!empty($this -> errors[])){
                $html = '<ul>';
                foreach ($this -> errors as $error){
                    $html .= "<li>$error</li>";
                }
                $html .= '</ul>';
            }
            return $html;
        }
        
        // update single field by given id
        function updateField($id, $field, $value){
            $qry = "UPDATE " . $this -> tableName . " SET " . $field . '="' . (SLASH ? addslashes($value) : $value) 
                    . '" WHERE '  . $this -> fieldPrefix . 'id = "' . $id . '"';
            $this -> sqlLib -> query($qry);
        }
        
        // update whole record by given id and array of field names and values
        function updateRecord($id, $fields){
            $qry = "UPDATE " . $this -> tableName . ' SET ' . join(' AND ', $this -> buildFieldsArray($fields)) . ' WHERE '
                   . $this -> tablePrefix . 'id = "' . $id . '"';
            $this -> sqlLib -> query($qry);
        }
        
        // insrt new record into database
        function insertRecord($fields){
            $qry = "INSERT INTO " . $this -> tableName . ' SET ' . join(' AND ', $this -> buildFieldsArray($fields));
            $this -> sqlLib -> query($qry);
        }
        
        // build array of sql statement parts from fields array
        function buildFieldsArray($fields){
            # converts array
            # array('field' => 'value', 'field2' => 'value2') => array('field="value"', 'field2="value2"');
            foreach ($fields as $field => $value){
                $tmp_values[] = $field . '="' . $value . '"';
            }
            return $tmp_values;
        }
        
        // raise record priority by given id and additional params
        function raisePriority($id, $params = array()){
            $sql = $this -> sqlLib;
            #build additional query parameters - if we re-order elements for some parent element
            $whereClause = join(' AND ', $this -> buildFieldsArray($params));
            # select the priority of current element
            $qry = "SELECT " . $this -> fieldPrefix . "priority FROM " . $this -> tableName . ' WHERE ' 
                   . $this -> fieldPrefix . "id = '$id'";
            $sql -> query($qry);
            $item = $sql -> fetchAssoc();
            #select the next element for the same parent element (if specified)
            $qry = 'SELECT ' . $this -> fieldPrefix . 'id FROM ' . $this -> tableName . ' WHERE ' 
                  . $this -> fieldPrefix . "priority = '".($item[$this -> fieldPrefix . 'priority'] - 1)."'" 
		  . (!empty($whereClause) ? ' AND ' . $whereClause : '');
            $sql -> query($qry);
            # if we have found the element with bigger priority, change the priorities for both of the elements
            if ($sql -> getRows() == 1) {
                $item2 = $sql -> fetchAssoc();
                $qry = 'UPDATE ' . $this -> tableName . ' SET ' . $this -> fieldPrefix . 'priority = 
                       ' . $this -> fieldPrefix . 'priority - 1 WHERE ' . $this -> fieldPrefix . "id = '$id'";
                $sql -> query($qry);
                $qry = 'UPDATE ' . $this -> tableName . ' SET ' . $this -> fieldPrefix . 'priority = 
                       ' . $this -> fieldPrefix . 'priority + 1 WHERE ' . $this -> fieldPrefix . "id = '" 
		       . $item2[$this -> fieldPrefix . 'id'] . "'";
                $sql -> query($qry);
            }
        }
        
        // lower priority by given id and additional params
        function lowerPriority($id, $params = array()){
            $sql = $this -> sqlLib;
            #build additional query parameters - if we re-order elements for some parent element
            $whereClause = join(' AND ', $this -> buildFieldsArray($params));
            # select the priority of current element
            $qry = 'SELECT ' . $this -> fieldPrefix . 'priority FROM ' . $this -> tableName . ' WHERE ' 
                   . $this -> fieldPrefix . "id = '$id'";
            $sql -> query($qry);
            $item = $sql -> fetchAssoc();
            #select the next element for the same parent element (if specified)
            $qry = 'SELECT ' . $this -> fieldPrefix . 'id FROM ' . $this -> tableName . ' WHERE ' 
                  . $this -> fieldPrefix . "priority = '".($item[$this -> fieldPrefix . 'priority'] + 1)."'" 
				  . (!empty($whereClause) ? ' AND ' . $whereClause : '');
            $sql -> query($qry);
            # if we have found the element with smaller priority, change the priorities for both of the elements
            if ($sql -> getRows() == 1) {
                $item2 = $sql -> fetchAssoc();
                $qry = 'UPDATE ' . $this -> tableName . ' SET ' . $this -> fieldPrefix . 'priority = '
                       . $this -> fieldPrefix . 'priority + 1 WHERE ' . $this -> fieldPrefix . "id = '$id'";
                $sql -> query($qry);
                $qry = 'UPDATE ' . $this -> tableName . ' SET ' . $this -> fieldPrefix . 'priority = ' 
                      . $this -> fieldPrefix . 'priority - 1 WHERE ' . $this -> fieldPrefix . "id = '" 
					 . $item2[$this -> fieldPrefix . 'id'] . "'";
                $sql -> query($qry);
            }
        }
        
        // return parent object of record with specified id
        function getParentObject($id){
            $qry = "SELECT " . $this -> fieldPrefix . "parent FROM " . $this -> tableName . " WHERE " 
                     . $this -> fieldPrefix  . "id='$id'";
            $this -> sqlLib -> query($qry);
            $parentId = $this -> sqlLib -> getField(0);
            $qry = "SELECT * FROM " . $this -> tableName . " WHERE " . $this -> fieldPrefix . "id='$parentId'";
            $this -> sqlLib -> query($qry);
            return $this -> sqlLib -> fetchAssoc();
        }
        
        // get child object list of record with specified id
        function getChildObjects($id){
            $qry = "SELECT * FROM " . $this -> tableName . " WHERE " . $this -> fieldPrefix . "parent='$id'";
            $this -> sqlLib -> query($qry);
            return $this -> sqlLib -> getAssoc();
        }
        
    }
    
?>

Ingus Rukis, http://www.wisecms.com, ingus.rukis@gmail.com. I've been working as a PHP programmer for a while (some 3 years, I guess) and on my spare time I enjoy writing some simple and easy tutorials for beginners. I mostly write in Latvian, that's my mother language, so if you notice some spelling or grammar mistakes, please excuse me for that.

P.S. if you found this piece of code useful, feel free to add any comments and vote.