Jump to content

SQL Object Class


teynon

Recommended Posts

Sorry mods, if there is a better place to post this. I've had some time to play with PHP5 more now that I am out of the army and back in school. I created a class that I think can be pretty useful. I was hoping someone could look through this and let me know what they think. Is there any improvements or essentials that are missing?

 

Basically the class runs a provided SQL query and converts the MySQL resource to a multidimensional array. The object can then be iterated through and extended to allow children. The example demonstrates how it can be used. I've used this in a couple of different areas so far.

 

I have attached the files.

 

I will also post the Example.php code here:

 

<?php
require "SQL_Object.php";

// Example extended class
class forumObject extends SQL_Object {
    function buildForum() {
        $sql = "SELECT * FROM `forum_cats` WHERE `PID` = '{$this->data[$this->pointer]['ID']}'";
        $this->object[$this->pointer]['Forum'] = new forumObject($sql);
    }
}

// Make sure you connect to your database first.
$id_link = mysql_connect("localhost", "root", "");
mysql_select_db("testing");

// ------------------------------------------------

// Example Recursive Forum Builder.
function buildForumCategories($forums) {
// Build a container so the child boards indent.
    echo "<div style=\"margin-left: 10px;\">";

// We can use foreach to increment the internal pointer on the class.
    foreach ($forums as $key => $forum) {
	// Echo the forum name. (The field name is the MySQL row or whatever you specified in the query.
        echo $forums->name . "<br />";

	// We can see if there are any child forums using count.
	// $forums->Forum will invoke PHP 5's magic method "__get"
	// SQL_Object also implements countable interface so we can
	// override the count function with our own.
        if (count($forums->Forum) > 0) {
            // Call child boards.
            buildForumCategories($forums->Forum);
        }
    }
// We can also manually do it with a for:
// 		Example: for ($forums->rewind(); $forums->valid(); $forums->next())
//
// We could use the internal objects, but the methods work just fine.

    echo "</div>";
}

// Do you're normal query for the initial boards.
$sql = "SELECT * FROM `forum_cats` WHERE `PID` = 0";

// Pass the query to the extended class.
$forums = new forumObject($sql);

// Build the forum
buildForumCategories($forums);

 

EDIT: I'll go ahead and post the class code in here too:

 

<?php
####################################################
# Extendable SQL Object Class                      #
# ------------------------------------------------ #
#    This class takes a valid SQL string and       #
# converts the resulting MySQL resource into an    #
# object that uses dynamic variables.              #
#                                                  #
#    This allows ease of use and quick access to   #
# data information.                                #
# ------------------------------------------------ #
# CREATED BY: Thomas Eynon                         #
#             www.thomaseynon.com                  #
# CREATED ON: 1/15/2012                            #
####################################################

if (!interface_exists("Object_Array")) {
    // Blend the interfaces.
    interface Object_Array extends Iterator, Countable {

    }
}

if (!class_exists("SQL_Object")) {
    class SQL_Object implements Object_Array {
        public $data, $object, $sql, $pointer, $size;
        
        // Default constructor
        function __construct($sql = "", $autoRun = true) {
            $this->data = array();
            $this->object = array();
            $this->sql = "";
            $this->pointer = 0;
            $this->size = 0;
            
            if (!empty($sql)) {
                $this->run($sql, $autoRun);
            }
        }
        
        // Run a custom query.
        function SQL_Data($sql, $autoRun = true) {
            $this->run($sql, $autoRun);
        }
        
        
        function run($sql, $autoRun = true) {
            $this->sql = $sql;
            
            if ($autoRun) {
               $this->runQuery();
            }
        }

        function runQuery() {
            if ($query = @mysql_query($this->sql)) {
                while ($req = mysql_fetch_assoc($query)) {
                    foreach ($req as $key => $value) {
                        $this->data[$this->pointer][$key] = $value;
                    }
                    $this->pointer++;
                }
                $this->size = $this->pointer;
                $this->pointer = 0;
            }
            else {
                trigger_error("SQL Object: " . mysql_error(), E_USER_WARNING);
            }
        }
        
        function check($object) {
            if (isset($this->object[$this->pointer][$object])) {
                return $this->object[$this->pointer][$object];
            }
            else {
                if (method_exists($this, "build{$object}")) {
                    $method = "build{$object}";
                    $this->$method();
                    
                    if (isset($this->object[$this->pointer][$object])) {
                        return $this->object[$this->pointer][$object];
                    }
                }
            }
            
            return false;
        }
        
        // PHP 5 Magic Method
        // ----------------------------
        // Pulls the requested variable.
        // If the variable is not defined, throw a notice.
        function __get($param) {
            // Check if its a data value.
            if (isset($this->data[$this->pointer][$param])) {
                return $this->data[$this->pointer][$param];
            }
            
            // Check if its an object.
            $this->check($param);
            
            // Check if its a object value.
            if (isset($this->object[$this->pointer][$param])) {
                return $this->object[$this->pointer][$param];
            }
            
            // See if variable exists now.
            if (isset($this->$param)) {
                return $this->$param;
            }
            
            $trace = debug_backtrace();
            
            trigger_error('Undefined property: ' . $param .
            ' in ' . $trace[0]['file'] .
            ' on line ' . $trace[0]['line'],
            E_USER_NOTICE);
            
            return null;
        }
        
        // Iterator template methods
        function current() {
            return $this->data[$this->pointer];
        }
        
        function next() {
            $this->pointer++;
            if (isset($this->data[$this->pointer])) {
                return $this->data[$this->pointer];
            }
            
            return false;
        }
        
        function valid() {
            if (isset($this->data[$this->pointer])) {
                return true;
            }
            return false;
        }
        
        function rewind() {
            $this->pointer = 0;
        }
        
        function key() {
            return $this->pointer;
        }
        
        function count() {
            return $this->size;
        }
    }
}

 

I also can't upload the sql file so here is the sql to create the table for the example:

 

CREATE TABLE IF NOT EXISTS `forum_cats` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `PID` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `forum_cats` (`ID`, `name`, `PID`) VALUES
(1, 'Main Category 1', 0),
(2, 'Sub Category 1', 1),
(3, 'Sub Sub Category 1', 2),
(4, 'Main Category 2', 0);

17354_.php

17355_.php

Link to comment
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.