Converting MySQL Navigation Data to PHP Array

[Glowball]

I spent some time on solving this issue but I can't seem to get to where I need to be. I have navigation in a MySQL database like so.

ID    Name      Parent_ID    Order
----------------------------------
1     Category 1    0         1
2     Category 2    0         2
3     Product 1     1         1
4     Product 2     1         2
5     Attribute 1   3         2
6     Attribute 2   3         1

Basically, the Parent_ID field lists the ID of the item just above each one in the hierarchy. If the Parent_ID is 0, it is at the top level. The Order number is the order that each item is in within that level. This is pretty standard stuff.

I want to put the entire thing into an array. The final array would look like this.

(
    [1] => Array
        (
	[id] => 1
        [name] => Category 1
            [1] => Array
                (
		[id] => 3
                [name] => Product 1
		        [1] => Array
		                (
				[id] => 6
		                [name] => Attribute 2
							
		                )
	
	            [2] => Array
		                (
				[id] => 5
		                [name] => Attribute 1
		                )
					
                )

            [2] => Array
                (
		[id] => 4
                [name] => Product 2
                )
        )

    [2] => Array
        (
	[id] => 2
        [name] => Category 2
        )

)

I want to do this recursively so I don't have to limit the number of levels. How would I build this array? I've tried a few things but nothing's working right.

Thanks!

 

[jpadie]

you might be better off with a more robust query. this may help you

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

however i think your query is easy enough to manipulate into an array as you require. the code below uses PDO and sqlite so that i can use memory based tables to emulate your environment. if you use mysql then just change the foreach loop to a while ($row - mysql_fetch_assoc ($result)) loop

<?php
$pdo = new PDO ('sqlite::memory:');
$pdo->exec ('create table tmp_table (ID int, Name text, Parent_ID int, OrderID int)');
$s= $pdo->prepare ('Insert into tmp_table (ID, Name, Parent_ID, OrderID) values (?,?,?,?)');
if (!$s){print_r($pdo->errorinfo());}

$array = array(
			array(1, 'Category 1', 0, 1),
			array(2, 'Category 2', 0, 2),
			array(3, 'Product 1', 1, 1),
			array(4, 'Product 2', 1, 2),
			array(5, 'Attribute 1', 3, 2),
			array(6, 'Attribute 2', 3, 1));
foreach ($array as $a){
	$result = $s->execute($a);
	if($result === false){
		echo "Error: " . print_r($s->errorinfo(), true);
	}
}
$query = "Select * from tmp_table order by Parent_ID asc, OrderID asc";

$outputArray = array();
foreach ($pdo->query($query) as $row){
	if($row['Parent_ID'] == 0 ){
		$outputArray[$row['ID']] = array (	'id'=>$row['ID'], 
											'name'=>$row['Name']);
	} else {
		$outputArray[$row['Parent_ID']][] = array(	'id'=>$row['ID'], 
											'name'=>$row['Name']);
	}
}
echo "<pre>";
print_r($outputArray);

the code above yields the output

Array
(
    [1] => Array
        (
            [id] => 1
            [name] => Category 1
            [0] => Array
                (
                    [id] => 3
                    [name] => Product 1
                )

            [1] => Array
                (
                    [id] => 4
                    [name] => Product 2
                )

        )

    [2] => Array
        (
            [id] => 2
            [name] => Category 2
        )

    [3] => Array
        (
            [0] => Array
                (
                    [id] => 6
                    [name] => Attribute 2
                )

            [1] => Array
                (
                    [id] => 5
                    [name] => Attribute 1
                )

        )

)

 

[Glowball]

Thanks for taking a look at this. I guess I wasn't as clear as I should have been. The database table I've shown is just an example, but in reality there could be any number of levels in the hierarchy. So I wouldn't be able to build an array beforehand in the code.

In other words, one particular line in the hierarchy could have 10 levels. Or perhaps the first level has 10 items in it. I know it would have to be a recursive funtion, but I'm having issues figuring out what to pass the function each time, so I put the next item on the right level and under the right parent.

Any ideas? Thanks!

 

[jpadie]

my function above is agnostic to hierarch levels. you can have as many as you like in any order you like.

 

[Glowball]

I'm talking about this bit.

$array = array(
            array(1, 'Category 1', 0, 1),
            array(2, 'Category 2', 0, 2),
            array(3, 'Product 1', 1, 1),
            array(4, 'Product 2', 1, 2),
            array(5, 'Attribute 1', 3, 2),
            array(6, 'Attribute 2', 3, 1));

You have the final array defined in the code. I want to keep this as portable as possible, so it can be applied to any data as long as the schema remains defined in the same way.

Say, for example, that I'm building a class file. That class file should be able to be used by any application that organizes its navigation in that way. I would like to be able to call the class and a method within the class and have the array as the result, without having to prebuild the array. Something like this, in pseudocode.

function getNav() {
    get data
    add data for level 0 to array
    if there are any children under level 0
        getNav(level_id)
    return array
    }
$navArray = getNav();

So it recursively calls itself over and over until the array is built. It wouldn't matter how many levels there were, and the array does not need to be prebuilt.

Hope that helps.

 

[jpadie]

no no. the final array is nor predefined at all. that part just provides some dummy database data to work with. if your table is already filled you can cut out everything until the foreach (and replace it with you mysql query).

 

[Glowball]

It seems that your code will only build one level. Using my example data, Attribute 1 and Attribute 2 have Product 1 as a parent. In your example output array, Attribute 1 and Attribute 2 are on the same level as Product 1 and have no relationship to Product 1.

It's building the big multidimensional array that I'm struggling with.

 

[jpadie]

oh. i see. that's easy to fix. will post back.

 

[jaxtell]

I actually plan on doing something similar to this for a project I'm working on. Here is an attempt.

function getRowsAtLevel($level){
$query = "Select id, name, parent_id from my_table where parent_id = $level order by Parent_ID asc, OrderID asc";
$outputArray = array();
foreach ($pdo->query($query) as $row){

$children = array();
$children = getRowsAtLevel($row['id']);
if ($child != null) {
        $outputArray[$row['ID']] = array (    'id'=>$row['ID'],                                         'name'=>$row['Name'],
'children=>$children);
    }
else{
$outputArray[$row['ID']] = array (    'id'=>$row['ID'],                                         'name'=>$row['Name']);
}
}
return $outputArray;
}

Does recursion work in PHP? Most of my experience is in Java, so I might be way off base here. It is Friday afternoon, so that might not make any sense at all. If it doesn't work and you don't get a better answer, I should have some working code within the day.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[Glowball]

Yep, recursion works the same in PHP. I think I see where you're going with that code -- I've gotten to the same place and then got stuck. The problem I'm having is putting all of those child arrays in the correct place in the big array.

If I can find a solution to this, it will make an existing approach much better. So it's not desperate that I get an answer today. It's something I've worked on in the past and I'm working on now, and it would be good to find a solution. It seems like it would be a great way to handle navigation.

1. Make one call to the database to get all of the navigation elements.
2. Put everything in an array.

That way I can use the array to build lists and internal page elements that rely on the whole hierarchy, all with one database call. The array could even be cached using an XML document, so the class could be altered to build either an array or an XML document. It seems like a solution would be something that could be used in many different applications.

Let me know what you think, and thanks. I'll keep working on it and let you know if I find anything.

 

[jpadie]

sorry for the delay - kids were acting up.

unless i'm misunderstanding the recursion is straightforward. you said you wanted a class so i've built one for you, but in reality a procedural function would work just as well.

the code below mocks up the database as before but i have marked clearly where it is set up code. i've also included both PDO and mysql code.

this code

<?php
//build temporary dataset
$pdo = new PDO ('sqlite::memory:');
$pdo->exec ('create table tmp_table (ID int, Name text, Parent_ID int, OrderID int)');
$s= $pdo->prepare ('Insert into tmp_table (ID, Name, Parent_ID, OrderID) values (?,?,?,?)');
if (!$s){print_r($pdo->errorinfo());}

$array = array(
            array(1, 'Category 1', 0, 1),
            array(2, 'Category 2', 0, 2),
            array(3, 'Product 1', 1, 1),
            array(4, 'Product 2', 1, 2),
            array(5, 'Attribute 1', 3, 2),
            array(6, 'Attribute 2', 3, 1));
foreach ($array as $a){
    $result = $s->execute($a);
    if($result === false){
        echo "Error: " . print_r($s->errorinfo(), true);
    }
}

//end building
$query = "Select ID, Name, Parent_ID, OrderID from tmp_table order by Parent_ID asc, OrderID asc";
$outputArray=array();

/*
 * uncomment the next few lines for mysql use
 * $result = mysql_query($query) or die (mysql_error());
 * while ($row = mysql_fetch_assoc($result)){
 *  	$outputArray[] = $row;
 *  }
 */


/*
 * comment this next block if you are not using PDO
 */
 foreach($pdo->query($query) as $row){
	$outputArray[] = $row;
}
//end of PDO block

$tree = new tree($outputArray);
$tree->buildTree();
echo "<pre>" . print_r($tree->tree, true) . "</pre>";

class tree{
	
	private $data = array();
	public $tree = array();
	
	public function __construct($data){
		$this->loadData ($data);
	}
	
	public function buildTree(){
		$this->tree = $this->getChildren(0);
	}

	private function loadData($data){
		set_time_limit(10); //just in case we get into a never ending loop...
		$this->data = $data;
	}

	private function getChildren($parentID){
		$children = array();
		foreach($this->data as $key=>$i){
			if ($i['Parent_ID'] == $parentID){
				unset ($this->data[$key]); //remove the row to speed up the loop
				//this is a child of the current item
				$child = array(		'id' => $i['ID'],
									'name'=>$i['Name']);
				$temp = $this->getChildren($i['ID']);
				if (count($temp) > 0 ){	//don't add on an array element if there are no children
					$child['children'] = $temp;
				}
				$children[] = $child;
			}
		}
		return $children;
	}
}

yields this output

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Category 1
            [children] => Array
                (
                    [0] => Array
                        (
                            [id] => 3
                            [name] => Product 1
                            [children] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 6
                                            [name] => Attribute 2
                                        )

                                    [1] => Array
                                        (
                                            [id] => 5
                                            [name] => Attribute 1
                                        )

                                )

                        )

                    [1] => Array
                        (
                            [id] => 4
                            [name] => Product 2
                        )

                )

        )

    [1] => Array
        (
            [id] => 2
            [name] => Category 2
        )

)