Hierarchical Data in MySQL and PHP.php

<?php
/**
 * @see http://www.phpbuilder.com/articles/databases/mysql/handling-hierarchical-data-in-mysql-and-php.html
 */

/**
 * @param $category_id
 * @return array
 * @return void
 */
function get_path($category_id)
{
    // look up the parent of this node
    $result = mysql_query("
SELECT
  c1.parent_id,
  c2.category_name AS parent_name
FROM
  category AS c1
LEFT JOIN category AS c2 ON c1.parent_id=c2.category_id 
WHERE c1.category_id='$category_id'");
    $row    = mysql_fetch_array($result);

    // save the path in this array
    $path = array();

    //continue if this node is not the root node
    if ($row['parent_id'] != null) {
        // the last part of the path to node

        end($path);
        $last_key = key($path);
        $key      = $last_key == 0 ? 0 : $last_key + 1;

        $path[$key]['category_id']   = $row['parent_id'];
        $path[$key]['category_name'] = $row['parent_name'];

        $path = array_merge(get_path($row['parent_id']), $path);
    }

    return $path;
}

/**
 * @param $category_id
 * @param $level
 * @return void
 */
function display_children($category_id, $level)
{
    // retrieve all children
    $result = mysql_query("SELECT * FROM category WHERE parent_id='$category_id'");

    // display each child
    while ($row = mysql_fetch_array($result)) {
        // indent and display the title of this child
        // if you want to save the hierarchy, replace the following line with your code
        echo str_repeat(' . ', $level) . $row['category_name'] . '(' . $row['category_id'] . ")<br/>";

        // call this function again to display this child's children
        display_children($row['category_id'], $level + 1);
    }
}

/**
 * @param $path
 * @return string
 */
function get_path_string($path)
{
    $result = '';

    foreach ($path as $node) {
        $result .= '/' . $node['category_name'];
    }
    return $result;
}

/**
 * @param $path
 * @return string
 */
function get_path_node($path)
{
    $result = '';

    foreach ($path as $node) {
        $result .= '/' . $node['category_id'];
    }
    return $result;
}

//CREATE TABLE IF NOT EXISTS `category` (
//`category_id` int(10) NOT NULL AUTO_INCREMENT,
//  `category_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
//  `parent_id` int(10) DEFAULT NULL,
//  PRIMARY KEY (`category_id`)
//) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

//INSERT INTO `category` (`category_id`, `category_name`, `parent_id`) VALUES
//(1, 'pear', 0),
//(2, 'strawberries', 1),
//(3, 'watermelon', 1),
//(4, 'kiwi', 3),
//(5, 'aple', 2),
//(6, 'orange', 3);

$con = mysql_connect("localhost", "root", "123") or
die("Could not connect: " . mysql_error());
mysql_select_db("prosza_tree_01");

display_children(0, 1);

echo '<br><br>' . var_export(get_path(6), true);
echo '<br><br>' . get_path_string(get_path(6));
echo '<br><br>' . get_path_node(get_path(6));