Category System

[Arcalypse]

So I am having some difficulties with a category system. I want my code to search the database an unlimited amount of times to the very last directory and then display it in tree view on my screen. I'm having some trouble because I am having to specify how many depths the code will search. How can I get it to do this on it's own? Currently my code does this...
 

<?php
// Establish SQL Connection.

$mysqli			=	mysqli_connect( $sql['host'], $sql['user'], $sql['pass'], $sql['db'] ) or die( "DARN");
$query			=	$mysqli->query( "SELECT * FROM folders ORDER BY name ASC" );
$num_rows	=	$query->num_rows;


echo "Found <b>" . $num_rows . "</b> row(s) in the database!<br>";


	while ( $row = mysqli_fetch_array( $query ) )
	{
		// DO NOT REMOVE
		$id		=	$row['id'];
		$name	=	$row['name'];
		$parent	=	$row['parent'];
		
		/***************************************************************************************************/
		
		if ( $parent == 0 )
		{
			echo $id . " " . $name . " " . $parent . "<br>";
			
			$query2	=	$mysqli->query( "SELECT * FROM folders WHERE parent = " . $id . " ORDER BY name ASC" );
			
			while ( $row2 = mysqli_fetch_array( $query2 ) )
			{
				echo "     " . $row2['name'] . "<br>";
			}
		}
	}
?>

I know it's messy, doing the best I can.

 

 

DB looks a little like this...

 

+----+---------+--------+

| ID |  NAME   | PARENT |

+----+---------+--------+

|  1 | Admin   |      0 |

|  2 | Blog    |      0 |

|  3 | Shop    |      0 |

|  4 | Forum   |      0 |

|  5 | Sandbox |      0 |

|  6 | Config  |      1 |

|  7 | Login   |      1 |

|  8 | Display |      1 |

|  9 | Lib     |      2 |

| 10 | Default |      8 |

| 11 | JS      |      9 |

| 12 | CSS     |      9 |

| 13 | HTML    |      9 |

| 14 | IMG     |     10 |

+----+---------+--------+

Edited July 25, 2014 by Arcalypse

[ginerjm]

Would be a good place to write a recursive function.  Read up on them for some examples.  Be sure to rely on local vars passed as arguments to the function and also have a bit of logic to handle the amount of spaces (?) you want to use to indent each lower nest of results or to un-indent  once done with a nest/level

 

PS - be sure to set your execution time in your php.ini file to something very very small while developing this.  I'd suggest one second..

[Arcalypse]

Would be a good place to write a recursive function.  Read up on them for some examples.  Be sure to rely on local vars passed as arguments to the function and also have a bit of logic to handle the amount of spaces (?) you want to use to indent each lower nest of results or to un-indent  once done with a nest/level

 

PS - be sure to set your execution time in your php.ini file to something very very small while developing this.  I'd suggest one second..

 

I was looking on Google for the recursive function in the most basic form and it's enough to make my brain ache lol. With that being said, I appreciate the execution time tip. Could prove very helpful for those "server_crashing_requests" I had earlier by an accidental loop.

[ginerjm]

If you want to produce an output of unknown layers such as you described, recursion is the only way to handle it that I know of.  I don't think you can build a query to produce a complete set of data where you have an unknown number of generations to map.

[Arcalypse]

Wouldn't it be possible to just have SQL count the number of rows in the database and then query against that value?

[Psycho]

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

[Barand]

Instead of recursively querying the database use a single query and store in an array. Then do a recursive print of the array

$sql = "SELECT id, name, parent
        FROM folder";
$res = $db->query($sql);
$data = array();
//
// Store data in array indexed by parent
//
while (list($id, $name, $parent) = $res->fetch_row()) {
    $data[$parent][$id] = $name;
}

//
// Recursive function to print the tree
//
function printFolders(&$data, $parent, $level=0)
{
    $indent = str_repeat('— ', $level);
    
    foreach ($data[$parent] as $id => $name) {
        echo "$indent $name<br>";
        if (isset($data[$id])) {  // any children?
            printFolders($data, $id, $level+1);    // call function to print child data
        }
    }
}
     
printFolders($data, 0);   

Outputs ->

Admin
— Config
— Login
— Display
— — Default
— — — IMG
Blog
— Lib
— — JS
— — CSS
— — HTML
Shop
Forum
Sandbox

[Arcalypse]

 

Instead of recursively querying the database use a single query and store in an array. Then do a recursive print of the array

$sql = "SELECT id, name, parent
        FROM folder";
$res = $db->query($sql);
$data = array();
//
// Store data in array indexed by parent
//
while (list($id, $name, $parent) = $res->fetch_row()) {
    $data[$parent][$id] = $name;
}

//
// Recursive function to print the tree
//
function printFolders(&$data, $parent, $level=0)
{
    $indent = str_repeat('— ', $level);
    
    foreach ($data[$parent] as $id => $name) {
        echo "$indent $name<br>";
        if (isset($data[$id])) {  // any children?
            printFolders($data, $id, $level+1);    // call function to print child data
        }
    }
}
     
printFolders($data, 0);   

Outputs ->

Admin
— Config
— Login
— Display
— — Default
— — — IMG
Blog
— Lib
— — JS
— — CSS
— — HTML
Shop
Forum
Sandbox

OMG, I love you! Thank you!