PHP MySQL Recursive Function

A sample recursive function that can be considered as a tree walk that can be use to display a menu and its categories.

In one table we have this hierchical relationship where the child points to the parent throught the parent_id field. Then, the own id is also the parent of its children. It can be useful when you need to display many deep levels of information.

Just to remember: a recursive function is a function that calls itself, so here is the code:

$link=null;
$link=mysqli_connect("localhost", "user", "pass", "cities");

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}


// You can change 0 by another id variable that you want
recurseTree(0,$link);

function recurseTree($parent_id,$link)
{
    $sql="SELECT id,parent_id,name FROM city WHERE parent_id=".$parent_id;
    if ($result=mysqli_query($link,$sql )) {
        
        while($row=mysqli_fetch_array($result)) {
            
            if ($row["parent_id"]==0) {
                print "\nCapital: ";
            }
            print $row["name"]." \n";
            
            // Recursive call
            recurseTree($row["id"],$link);
        } 
    }
}



// Output:
/*
Capital: New York 
Albany 
Rochester 

Capital: Austin 
Dallas 
Houston 
San Antonio 

Capital: Phoenix 
Yuma 
Tucson 
*/


/////////////////
/// DATA BASE ///
/////////////////

/*
--
-- Database: `cities`
--

-- --------------------------------------------------------

--
-- Table structure `city`
--

CREATE TABLE IF NOT EXISTS `city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `name` varchar(100) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=14 ;

--
-- Content
--

INSERT INTO `city` (`id`, `parent_id`, `name`) VALUES
(1, 0, 'New York'),
(2, 1, 'Albany'),
(3, 1, 'Rochester'),
(4, 0, 'Austin'),
(5, 4, 'Dallas'),
(6, 4, 'Houston'),
(7, 4, 'San Antonio'),
(8, 0, 'Phoenix'),
(9, 8, 'Yuma'),
(10, 8, 'Tucson');
*/