php mysql 树_php / Mysql最佳树形结构

$host = "localhost";

//Database user name.

$login = "root";

//Database Password.

$dbpass = "";

$dbname = "abc";

$PDO = new PDO("mysql:host=localhost;dbname=$dbname", "$login", "$dbpass");

$rows = array();

$sql = 'SELECT id, parent_id, name FROM employee';

$query = $PDO->prepare($sql);

$query->execute();

$rows = array();

if (!$query)

{

$error = 'Error fetching page structure, for nav menu generation.';

exit();

}

while($row = $query->fetch(PDO::FETCH_ASSOC)){

if( strcasecmp($row['parent_id'],'null') === 0 || empty($row['parent_id']) ) {

$row['parent_id'] = null;

}

$rows[] = $row;

}

// covert raw result set to tree

$menu = convertAdjacencyListToTree(null,$rows,'id','parent_id','links');

// echo '

',print_r($menu),'

';

// display menu

echo themeMenu($menu,1);

/*

* ------------------------------------------------------------------------------------

* Utility functions

* ------------------------------------------------------------------------------------

*/

/*

* Convert adjacency list to hierarchical tree

*

* @param value of root level parent most likely null or 0

* @param array result

* @param str name of primary key column

* @param str name of parent_id column - most likely parent_id

* @param str name of index that children will reside ie. children, etc

* @return array tree

*/

function convertAdjacencyListToTree($intParentId,&$arrRows,$strIdField,$strParentsIdField,$strNameResolution) {

$arrChildren = array();

for($i=0;$i

if($intParentId === $arrRows[$i][$strParentsIdField]) {

$arrChildren = array_merge($arrChildren,array_splice($arrRows,$i--,1));

}

}

$intChildren = count($arrChildren);

if($intChildren != 0) {

for($i=0;$i

$arrChildren[$i][$strNameResolution] = convertAdjacencyListToTree($arrChildren[$i][$strIdField],$arrRows,$strIdField,$strParentsIdField,$strNameResolution);

}

}

return $arrChildren;

}

/*

* Theme menu

*

* @param array menu

* @param runner (depth)

* @return str themed menu

*/

function themeMenu($menu,$runner) {

$out = '';

if(empty($menu)) {

return $out;

}

$out.='

• ';

foreach($menu as $link) {

$out.= sprintf(

'

%s%s'

,$runner

,$link['name']

,themeMenu($link['links'],($runner+1))

);

}

$out.='';

return $out;

}

?>