Recursive function – PHP – SitePoint Forums

I’m trying to create a parent-child relationship with mysql.

i am creating a parent table and child table separately but i was only able to create one level child so if i need to create a multi-nest child i have to create a new child table for each nested child…so after having done a bit research, I learned about Recursive function which I could create multiple nested children with without any problems.

Using PDO mysqli as below code

try {
    $host="localhost";
    $dbname="55";
    $user="root";
    $password   = '';

    $conn = new PDO("mysql:host=$host;dbname=$dbname",$user,$password);
    $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    //$e->getMessage();
    die("Something went wrong please contact your adminstrator");
}


function categoryTree($parent_id = 0, $sub_mark = '',$rt=""){
    global $conn ;
    
    $data = $conn->prepare("SELECT * FROM categories WHERE parent_id = :parent_id");
    $data->bindParam(':parent_id', $parent_id);      
    $data->execute();        
    if ($data ->rowCount()) {
        $i=1;
        while($row = $data->fetch(PDO::FETCH_OBJ) ){
            echo '
'.$row->name.'';
            categoryTree($row->id, $sub_mark.'-',$rt.'op');
            $i++;
        }
    }
    
}

Number 1

Now the result is that all parents and children are in the same ‘li’(ul>li)
i want each child in the new ul>li with a unique id on li
i want title and content on tabs so i need unique id but it gives me same id for child title

//Tab-title

	
im parent one
	
im parent two arrow icon
		

			

				im first child of parent two
			
			

				im second child of parent two arrow icon
				

					

						im garnd child of parent two
					
					

						im garnd child of parent two
					
				
			
		
	

The span tag should only be displayed if they have a child, if the parent has a child and the child has a grandchild, the span tag should be displayed only in the parent and the child, if the parent n ‘has no children, the span tag should not be displayed

Number 2
Now in tab content parent content should only be displayed if it has child, if parent has child only child content should be displayed but if parent has grandchild , only grandchild content should be displayed… in content tabs only last child content should be displayed.

I easily achieved these options by creating a parent and child table separately, but with a recursive function creating a single table, I’m not able to meet my needs

Something like this would be fine using JavaScript and Ajax, but I digress. First I would look for everything in PHP PDO – something like this →

/*
 * PHP PDO connection
 */
$db_options = array(
    /* important! use actual prepared statements (default: emulate prepared statements) */
    PDO::ATTR_EMULATE_PREPARES => false
    /* throw exceptions on errors (default: stay silent) */
, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    /* fetch associative arrays (default: mixed arrays)    */
, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO('mysql:host=" . DATABASE_HOST . ";dbname=" . DATABASE_NAME . ";charset=utf8', DATABASE_USERNAME, DATABASE_PASSWORD, $db_options);

/*
 * Pagination Format
 * Read all the data from the database table in an array format
 */
function readData($pdo, $table, $page, $perPage, $offset) {
    $sql="SELECT * FROM " . $table . ' WHERE page=:page ORDER BY date_added DESC LIMIT :perPage OFFSET :blogOffset';
    $stmt = $pdo->prepare($sql); // Prepare the query:
    $stmt->execute(['perPage' => $perPage, 'blogOffset' => $offset, 'page' => $page]); // Execute the query with the supplied data:
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Don’t pay attention to the pagination part, it’s the $stmt->fetchALL(PDO::FETCH_ASSOC) that’s important so you can just access the array. Here’s an example of what I’m trying to say →

/* Finally, call for the data from the database table to display */

$cms = readData($pdo, 'cms', 'blog', $per_page, $offset);

    
    
        

            " 
                 alt="article image">
            

            Created by 
                    on 
            

            

            

            Record ' . urldecode($record['id']) . '' : null; ?>
        
    

You really don’t need a recursive function this way, but you would need to know how many levels you need to go down. That’s why I said JavaScript/Ajax would be better because using the recursive function would be helpful because it would be my dynamic than static and easier to figure out the number of levels. (I think)

Executing queries inside loops is not efficient, and it’s even worse for a recursive function since it is executed for each row of data, to find if that row has child data. Even if you extract the prepare statement from the function and only execute the already prepared query in the loop, you won’t save on the number of executions and only save about 5% of the execution time total by preparing the query only once.

The best way to do this is to query and fetch all the data at once, indexing/pivoting the data using the parent_id as the main array index, then running the recursive function on the data already fetched.

I’m not sure what you actually use for the sub_mark, rt and html classes or what they should be (your sample data, code and desired output don’t match) so I left everything code for them.

See the following sample code –

// get all the data at once, indexing/pivoting the data using the parent_id as the main index
$sql = "SELECT parent_id, id, name FROM your_table ORDER BY parent_id, id";
$stmt = $pdo->query($sql);
$menu_data = $stmt->fetchAll(PDO::FETCH_GROUP);

function categoryTree($menu_data, $parent_id = 0, $sub_mark = '',$rt="",$level=0)
{
	// if any matching rows, add to output
	if(isset($menu_data[$parent_id]))
	{
		if($level > 0)
		{
			// close the previous li
			echo " arrow iconn";
		}
		// start a new ul section here
		echo "
n";
		foreach($menu_data[$parent_id] as $row)
		{
			// produce output
			// start li here for each piece of data
			echo "
{$row['name']}";
			// get any children for the current id and output them
			categoryTree($menu_data, $row['id'], $sub_mark.'-',$rt.'op',$level++);
			// close the li for either a piece of data or for a whole ul section
			echo "
n";
		}
		// close the ul here
		echo "
n";
	}
}

categoryTree($menu_data);

This code named the connection variable $pdo and set the default recovery mode to assoc when the connection was established. You also need to set the character set to match your database tables and set emulated prepared statements to false.

I agree with mabismad about entering all data from ONE query and performing all looping or recursive functions on the array of data.

On my version I defined the data in 2 types with key 'categories' I have all the data much like mabismad’s $menu_data table with the id as a registration key that looks like this.

[categories] => Array
        (
            [1] => Array
                (
                    [id] => 1
                    [parent_id] => 0
                    Recursive function - PHP - SitePoint Forums => Parent No.1
                    [content] => Content for Parent No.1
                )

            [2] => Array
                (
                    [id] => 2
                    [parent_id] => 0
                    Recursive function - PHP - SitePoint Forums => Parent No.2
                    [content] => Content for Parent No.2
                )
///etc

For the second data type, I used the key'parent_cats' place the parent_id as key then a natural open key and the id as value. This results in a range of parents and their children.

[parent_cats] => Array
        (
            [0] => Array
                (
                    [0] => 1
                    [1] => 2
                    [2] => 7
                )

            [2] => Array
                (
                    [0] => 3
                    [1] => 4
                )

            [4] => Array
                (
                    [0] => 5
                    [1] => 6
                )

            [5] => Array
                (
                    [0] => 8
                    [1] => 9
                    [2] => 10
                )

            [9] => Array
                (
                    [0] => 11
                )

            [7] => Array
                (
                    [0] => 12
                )

        )

These tables were constructed like this.

$categoryMulti = array(
    'categories' => array(),
    'parent_cats' => array()
);	 
$sql = "SELECT `id`, `parent_id`, `title`, `content` FROM categories";
$query = $conn->query($sql);
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    $categoryMulti['categories'][$row['id']] = $row;
    $categoryMulti['parent_cats'][$row['parent_id']][] = $row['id'];
}

I used the following recursive function to create the menu list.

function listCategoryTree($parent, $category)
{
    $css_class = ($parent == 0 ? "parent" : "child");	
	
	$html="";
    if (isset($category['parent_cats'][$parent])) {
        $html .= '
'."n";
        foreach ($category['parent_cats'][$parent] as $cat_id) {
            if (!isset($category['parent_cats'][$cat_id])) {
                $html .= '
' . $category['categories'][$cat_id]['title'] . '
'."r";
            } else {
                $html .= '
' . $category['categories'][$cat_id]['title'] . ' '."r";
                $html .= listCategoryTree($cat_id, $category);
                $html .= '
'."r";
            }
        }
        $html .= '
'."n";
    }
    return $html;
}

which looks like this.

For all the attribute ids I used the record id so that each is a unique css ul class and defined according to the parent_id and added your tags but commented on arrow icon text for demo… You can edit it as you like.

I’m trying to show the title in the tab title and the content in the tab content, so when you click on the tab title, its corresponding content should show on the tab content ‘tab

how to find the last child and display content for the last child only…

And how about this version?

function listCategoryTree($parent, $category)
{
    $css_class = ($parent == 0 ? "parent" : "child");	
	
	$html="";
    if (isset($category['parent_cats'][$parent])) {
        $html .= '
'."n";
        foreach ($category['parent_cats'][$parent] as $cat_id) {
		
            if (!isset($category['parent_cats'][$cat_id])) {			
                $html .= '
' . $category['categories'][$cat_id]['content'] . '
'."r";
            } else {
                $html .= '
' . $category['categories'][$cat_id]['title'] . ' '."r";
                $html .= listCategoryTree($cat_id, $category);
                $html .= '
'."r";
            }
        }
        $html .= '
'."n";
    }
    return $html;
}

echo listCategoryTree(0, $categoryMulti) (solved) error resolved

This is my complete test code.

$categoryMulti = array(
    'categories' => array(),
    'parent_cats' => array()
);	 
$sql = "SELECT `id`, `parent_id`, `title`, `content` FROM categories";
$query = $conn->query($sql);
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    $categoryMulti['categories'][$row['id']] = $row;
    $categoryMulti['parent_cats'][$row['parent_id']][] = $row['id'];
}


function listCategoryTree($parent, $category)
{
    $css_class = ($parent == 0 ? "parent" : "child");	
	
	$html="";
    if (isset($category['parent_cats'][$parent])) {
        $html .= '
'."n";
        foreach ($category['parent_cats'][$parent] as $cat_id) {
		
            if (!isset($category['parent_cats'][$cat_id])) {			
                $html .= '
' . $category['categories'][$cat_id]['content'] . '
'."r";
            } else {
                $html .= '
' . $category['categories'][$cat_id]['title'] . ' '."r";
                $html .= listCategoryTree($cat_id, $category);
                $html .= '
'."r";
            }
        }
        $html .= '
'."n";
    }
    return $html;
}
echo listCategoryTree(0, $categoryMulti);

I am trying to achieve the above condition.
With your code I have successfully listed parent and multilevel child title in tab title section

Now in Tab content I am trying to list the content for the title listed in Tab title
In the tab content, if the parent has a multi-level child, only the last child content should display, but if the parent has a child, the parent content should display.

But I am getting content for all parent titles as well as nested children here, I want the last child content but if the parent has a child then the parent content should be shown

how to check if parent has child, if parent has child, show content of last child only, if parent has no child, show content of parent

Code to display multi-level nested child title in tab title

$categoryMulti = array(
    'categories' => array(),
    'parent_cats' => array()
);   

$sql = "SELECT `id`, `parent_id`, `name`, `content` FROM categories";
$query = $conn->query($sql);
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
   $categoryMulti['categories'][$row['id']] = $row;
    $categoryMulti['parent_cats'][$row['parent_id']][] = $row['id'];
}


//title for tabs
function listCategoryTree($parent, $category)
{
    $css_class = ($parent == 0 ? "parent" : "child");   
    
    $html="";
    if (isset($category['parent_cats'][$parent])) {
        $html .= '
'."n";
        foreach ($category['parent_cats'][$parent] as $cat_id) {
            if (!isset($category['parent_cats'][$cat_id])) {
                $html .= '

                '
                 . $category['categories'][$cat_id]['name'] . 
                 '';
                 '
'."r";
            } else {
                $html .= '

                 '


                . $category['categories'][$cat_id]['name'] . 

                ' arrow icon '."r";
                $html .= listCategoryTree($cat_id, $category);
                $html .= '
'."r";
            }
        }
        $html .= '
'."n";
    }
    return $html;
}

Code trying to show last child content if parent has multi-level nested child and if parent has child show parent content

//content for tabs
$contentTab = array(
    'contentCat' => array(),
    'contentChild' => array()
);   
$sql = "SELECT `id`, `parent_id`, `name`, `content` FROM categories";
$query = $conn->query($sql);
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
   $contentTab['contentCat'][$row['id']] = $row;
    $contentTab['contentChild'][$row['parent_id']][] = $row['id'];
}

//content for tabs
function listContentTree($parent, $category)
{
    $css_class = ($parent == 0 ? "parent" : "child");   
    
    $html="";
    if (isset($category['contentChild'][$parent])) {
        $html .= '
'."n";
        foreach ($category['contentChild'][$parent] as $cat_id) {
            if (!isset($category['contentChild'][$cat_id])) {
                $html .= '

                im child content test---'
                 . $category['contentCat'][$cat_id]['content'] . 
                 '';
                 '
'."r";
            } else {
                $html .= '

                 hello parent test------'


                . $category['contentCat'][$cat_id]['content'] . 

                ''."r";
                $html .= listContentTree($cat_id, $category);
                $html .= '
'."r";
            }
        }
        $html .= '
'."n";
    }
    return $html;
}

how can i check that the last child shows the content only for the last child and if the parent has no child show the content of the parent

This mess, by the way, is why the adjacency list pattern exists.

at the end of the day the question remains the same…how can one find the last child in a multilevel nested child and display only the contents of the last child only and if the parent is childless, display only the contents of the parent…

your suggested link shows me the process to list all nested children or list by id, but i want to list all child list in loop and check last child and show last child content only…and parent content only if the parent has no children