Blog with PHP, MySQL and other tutorials. You can also download my scripts, report a bug or leave a feedback.
In this tutorial we will create walker class. This class will load results by executing given SQL query and then create multidimensional array that will have entries in right position. Let’s say that you have this data in your database.
+----+--------+---------------------------------+---------------------+ | id | parent | message | date | +----+--------+---------------------------------+---------------------+ | 1 | 0 | First message. | 2009-09-02 00:07:07 | | 2 | 0 | Second message. | 2009-09-02 00:07:08 | | 3 | 1 | Reply to first message | 2009-09-02 00:07:30 | | 4 | 2 | Reply to second message | 2009-09-02 00:07:31 | | 5 | 3 | Reply to reply to first message | 2009-09-02 00:07:45 | +----+--------+---------------------------------+---------------------+
What our script will do is something like this.
array 1 => object(stdClass)[3] public 'self' => object(stdClass)[2] public 'id' => string '1' (length=1) public 'parent' => string '0' (length=1) public 'message' => string 'First message.' (length=14) public 'date' => string '2009-09-02 00:07:07' (length=19) public 'childs' => array 3 => object(stdClass)[7] public 'self' => object(stdClass)[6] public 'id' => string '3' (length=1) public 'parent' => string '1' (length=1) public 'message' => string 'Reply to first message' (length=22) public 'date' => string '2009-09-02 00:07:30' (length=19) public 'childs' => array 5 => object(stdClass)[11] public 'self' => object(stdClass)[10] public 'id' => string '5' (length=1) public 'parent' => string '3' (length=1) public 'message' => string 'Reply to reply to first message' (length=31) public 'date' => string '2009-09-02 00:07:45' (length=19) public 'childs' => array empty 2 => object(stdClass)[5] public 'self' => object(stdClass)[4] public 'id' => string '2' (length=1) public 'parent' => string '0' (length=1) public 'message' => string 'Second message.' (length=15) public 'date' => string '2009-09-02 00:07:08' (length=19) public 'childs' => array 4 => object(stdClass)[9] public 'self' => object(stdClass)[8] public 'id' => string '4' (length=1) public 'parent' => string '2' (length=1) public 'message' => string 'Reply to second message' (length=23) public 'date' => string '2009-09-02 00:07:31' (length=19) public 'childs' => array empty
As you can see, all entries are nested and on the right place.
Define Class And VariablesTop
First we will define class and variables. Class name will be walker and she will have 6 variables.
class walker
{
private $_results = array();
protected $_con = null;
private $_refMap = array();
protected $_traced = array();
protected $_parent = 'parent';
protected $_id = 'id';
...
}
MethodsTop
Now we will define methods. There will be 8 methods.
__constructTop
This method takes one argument and that is MySQL connection resource. If passed variable is not MySQL connection resource, new Exception is thrown, otherwise assign it to $_con.
public function __construct($db)
{
//Is valid resource?
if (!is_resource($db) || get_resource_type($db) !== 'mysql link')
throw new Exception('This is not valid MySQL connection resource.');
$this->_con = $db;
}
loadResultsTop
This method takes one argument to and that is SQL query that loads results. If there was an error when loading those results, new Exception is thrown.
public function loadResults($sql)
{
$this->_results = mysql_query($sql);
if (!$this->_results)
throw new Exception('Could not load result.');
return $this;
}
traceTop
Now, this is where things get interesting 
. This function makes mess, loaded from database, something meaningful. While there is still a row in MySQL results resource load it into variable. If current entry does not have parent, just append it to array because it’s first level. But if there is a parent then we need to find where it goes. For that we have functions _traceOrgin and _place (will covere them later). And here we add values to our $_refMap in case that some other entry has as parent this entry. You’ll se later why we need $_refMap.
public function trace()
{
$temp = array();
while (($object = mysql_fetch_object($this->_results)) !== false) {
if ($object->{$this->_parent} > 0) {
$trace = $this->_traceOrgin($object->{$this->_parent});
$temp = $this->_place($trace, $object, $temp);
$this->_refMap[$object->{$this->_id}] = $object->{$this->_parent};
}
else {
$tmpObject = new stdClass();
$tmpObject->self = $object;
$tmpObject->childs = array();
$temp[$object->{$this->_id}] = $tmpObject;
}
}
$this->_traced = $temp;
return $this;
}
_traceOrginTop
This method goes through our $_refMap and finds all entries that current ID has as parents and adds them to array. That array is returned and used later in _place method. As you can see this is recursive method.
private function _traceOrgin($parent, $array = array())
{
//If ID exists in our $_refMap
if (isset($this->_refMap[$parent])) {
return array_merge(
array($parent),
$this->_traceOrgin($this->_refMap[$parent], $array)
);
}
return array($parent);
}
_placeTop
This method finds right spot to place our entry. She goes through each row in array that we got from _traceOrgin method and if there is no more rows in that array then this is our spot. When this is found, then she just inserts is not array and returns it. This method is also recursive.
private function _place($trace, $object, $temp = array())
{
//If there is no more traced ID-s then this is our spot to insert entry
if (count($trace) === 0) {
$tmpObject = new stdClass();
$tmpObject->self = $object;
$tmpObject->childs = array();
$temp[$object->{$this->_id}] = $tmpObject;
return $temp;
}
else {
$key = array_pop($trace);
$temp[$key]->childs = $this->_place($trace, $object, $temp[$key]->childs);
}
return $temp;
}
returnTracedTop
Just returns traced entries that where generated by trace method.
public function returnTraced()
{
return $this->_traced;
}
setIdFieldTop
Sets name of the field that has ID value. If passed variable is not string, new Exception is thrown.
public function setIdField($field)
{
if (!is_string($field))
throw new Exception('Field must be string.');
$this->_id = $field;
return $this;
}
setParentFieldTop
Sets name of the field that has parent value. If passed variable is not string, new Exception is thrown.
public function setParentField($field)
{
if (!is_string($field))
throw new Exception('Field must be string.');
$this->_parent = $field;
return $this;
}
This would be our class. When we put this all together we get this.
class walker
{
private $_results = array();
protected $_con = null;
private $_refMap = array();
protected $_traced = array();
protected $_parent = 'parent';
protected $_id = 'id';
public function __construct($db)
{
//Is valid resource?
if (!is_resource($db) || get_resource_type($db) !== 'mysql link')
throw new Exception('This is not valid MySQL connection resource.');
$this->_con = $db;
}
public function loadResults($sql)
{
$this->_results = mysql_query($sql);
if (!$this->_results)
throw new Exception('Could not load result.');
return $this;
}
public function trace()
{
$temp = array();
while (($object = mysql_fetch_object($this->_results)) !== false) {
if ($object->{$this->_parent} > 0) {
$trace = $this->_traceOrgin($object->{$this->_parent});
$temp = $this->_place($trace, $object, $temp);
$this->_refMap[$object->{$this->_id}] = $object->{$this->_parent};
}
else {
$tmpObject = new stdClass();
$tmpObject->self = $object;
$tmpObject->childs = array();
$temp[$object->{$this->_id}] = $tmpObject;
}
}
$this->_traced = $temp;
return $this;
}
private function _traceOrgin($parent, $array = array())
{
//If ID exists in our $_refMap
if (isset($this->_refMap[$parent])) {
return array_merge(
array($parent),
$this->_traceOrgin($this->_refMap[$parent], $array)
);
}
return array($parent);
}
private function _place($trace, $object, $temp = array())
{
//If there is no more traced ID-s then this is our spot to insert entry
if (count($trace) === 0) {
$tmpObject = new stdClass();
$tmpObject->self = $object;
$tmpObject->childs = array();
$temp[$object->{$this->_id}] = $tmpObject;
return $temp;
}
else {
$key = array_pop($trace);
$temp[$key]->childs = $this->_place($trace, $object, $temp[$key]->childs);
}
return $temp;
}
public function returnTraced()
{
return $this->_traced;
}
public function setIdField($field)
{
if (!is_string($field))
throw new Exception('Field must be string.');
$this->_id = $field;
return $this;
}
public function setParentField($field)
{
if (!is_string($field))
throw new Exception('Field must be string.');
$this->_parent = $field;
return $this;
}
}
ConclusionTop
To use this class just extend it and create some formatting methods. In my next tutorial we will create multilevel comments using this class so stay tuned. You can download source here.
I am Marijan Šuflaj and I am 21 years old. Also I'm a student on FER (3rd year). I program in PHP for a long time but I am also learning other programming languages (like Java). This blog was made because I want to help PHP community by writing tutorials and publishing my PHP scripts so you can use them in your projects. Feel free to contact me and you can also ask for my help if you think I can help you.
[...] that purpose we will use walker class that you can download here and read tutorial how we made it here. So let’s start working on [...]