Recursive function

im trying to create parent child relationship with mysql.

id parent_id title content
1 0 im parent no.1 im content for parent no.1
2 0 im parent no.2 im content for parent no.2
3 2 im child of parent no.2 im content for parent no.2
4 2 im child of parent no.2 im child content for parent no.2
5 4 im grand-child of parent no.2 im grand-child content for parent no.2

i create parent table and children table separately but i was only able to create one level child, so if i have to create multi-nest child i have to create new child table for each nested child… so after doing bit search i came to know about Recursive Function with which i could create multi nested child without any issues.

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 '<li id="'.$row->id.'" class="'.$i.$rt.'">'.$row->name.'</option>';
            categoryTree($row->id, $sub_mark.'-',$rt.'op');
            $i++;
        }
    }
    
}

Issue no.1
222

Now result is every parent and child are in same ‘li’(ul>li)
i want each child in new ul>li with unique id on li
i want title and content on tabs so that i need unquie id but its giving me same id for child title

//Tab-title
<ul class="im-parent">
	<li id="parent-1">im parent one</li>
	<li id="parent-2">im parent two <span>arrow icon</span>
		<ul class="im-child">
			<li id="child-1">
				im first child of parent two
			</li>
			<li id="child-2">
				im second child of parent two <span>arrow icon</span>
				<ul class="im-grand-child">
					<li id="grand-child-1">
						im garnd child of parent two
					</li>
					<li id="grand-child-2">
						im garnd child of parent two
					</li>
				</ul>
			</li>
		</ul>
	</li>
</ul>

5
span tag should only be displayed if they have child, if parent have child and child have grand-child then span tag should be displayed in parent and child only, if parent doesn’t have any child then span tag shouldn’t be displayed

Issue No 2
Now in Tab-content parent content should display only if its does have any child, if parent have child then only child content should be display but if parent have grand-child then only grand-child content should be displayed… in content tabs only last child content should be displayed.

i have easily achieve this options with creating parent and child table separately but with recursive function creating single table im unable to achieve my requirement

Something like that would be good using JavaScript and Ajax, but I digress. First I would fetch all 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 is important then you can just access the array. Below is an example 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);
    <!-- Display all the Records in the database table using pagination -->
    <?php foreach ($cms as $record) { ?>
        <article class="cms">
            <img class="article_image"
                 src="<?php echo htmlspecialchars($record['image_path']); ?>" <?= getimagesize($record['image_path'])[3] ?>
                 alt="article image">
            <h2><?= $record['heading'] ?></h2>
            <span class="author_style">Created by <?= $record['author'] ?>
                    on <?= style_date($record['date_added']) ?>
            </span>

            <?php
            /*
             * Display code using highlight.js that shows
             * the code in proper format and highlighting by
             * call the codingTags() function.
             */
            $content = codingTags($record['content']);
            ?>

            <p><?= nl2br($content) ?></p>
            <?php echo (isset($_SESSION['id'])) ? '<a class="editButton" href="edit.php?id= ' . urldecode($record['id']) . '">Record ' . urldecode($record['id']) . '</a>' : null; ?>
        </article>
    <?php } ?>

You really don’t need a recursive function doing this way, but you would need to know how many levels down you will need to go. That is why I said JavaScript/Ajax would be better as there using recursive function would come in handy as it would be my dynamic than static and easier to figure out how many levels. (I think)

Running queries inside of loops is not efficient, and it is even worse for a recursive function since it is being executed for each row of data, to find if that row has any child data. Even if you pull the prepare statement out of the function and only execute the already prepared query inside the looping, you will not save on the number of executions and will only save about 5% of the total execution time by only preparing the query once.

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

I’m not sure what you are actually using the sub_mark, rt, and html class for or what they should be (your data examples, code, and desired output all don’t match up), so I left any code for them out.

See the following example 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 " <span>arrow icon</span>\n";
		}
		// start a new ul section here
		echo "<ul>\n";
		foreach($menu_data[$parent_id] as $row)
		{
			// produce output
			// start li here for each piece of data
			echo "<li id='{$row['id']}'>{$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 "</li>\n";
		}
		// close the ul here
		echo "</ul>\n";
	}
}

categoryTree($menu_data);

This code has named the connection variable $pdo, and has set the default fetch mode to assoc when the connection was made. You should also set the character set to match your database tables and set emulated prepared queries to false.

1 Like

I agree with mabismad regarding grabbing all the data from ONE query and doing any looping or recursive functions on the data array.

On my version I defined the data into 2 types with key 'categories' I have all data much like mabismad’s $menu_data array with the id as the record key which looks like this.

[categories] => Array
        (
            [1] => Array
                (
                    [id] => 1
                    [parent_id] => 0
                    [title] => Parent No.1
                    [content] => Content for Parent No.1
                )

            [2] => Array
                (
                    [id] => 2
                    [parent_id] => 0
                    [title] => Parent No.2
                    [content] => Content for Parent No.2
                )
///etc

For the second data type I used the key'parent_cats' placing the parent_id as the key then a natural open key and the id as the value. This results in an array 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 arrays were built 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 build the menu list.

function listCategoryTree($parent, $category)
{
    $css_class = ($parent == 0 ? "parent" : "child");	
	
	$html = '';
    if (isset($category['parent_cats'][$parent])) {
        $html .= '<ul class="im-'.$css_class.'">'."\n";
        foreach ($category['parent_cats'][$parent] as $cat_id) {
            if (!isset($category['parent_cats'][$cat_id])) {
                $html .= '<li id="'.$css_class.'-'.$cat_id.'">' . $category['categories'][$cat_id]['title'] . '</li>'."\r";
            } else {
                $html .= '<li id="'.$css_class.'-'.$cat_id.'">' . $category['categories'][$cat_id]['title'] . ' <span><!-- arrow icon --></span>'."\r";
                $html .= listCategoryTree($cat_id, $category);
                $html .= '</li>'."\r";
            }
        }
        $html .= '</ul>'."\n";
    }
    return $html;
}

which looks like this.
menutree
For all the attribute id’s I used the record id so each would be unique and defined css ul class based on the parent_id and added your <span> tags but commented out the arrow icon text for the demonstration… You can edit as you wish.

im trying to display title in tab-title and content in Tab-Content, so when click on tab-title its corresponding content should display on Tab-content

how to find out last child and display content for 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 .= '<ul class="im-'.$css_class.'">'."\n";
        foreach ($category['parent_cats'][$parent] as $cat_id) {
		
            if (!isset($category['parent_cats'][$cat_id])) {			
                $html .= '<li id="'.$css_class.'-'.$cat_id.'">' . $category['categories'][$cat_id]['content'] . '</li>'."\r";
            } else {
                $html .= '<li id="'.$css_class.'-'.$cat_id.'">' . $category['categories'][$cat_id]['title'] . ' <span><!-- arrow icon --></span>'."\r";
                $html .= listCategoryTree($cat_id, $category);
                $html .= '</li>'."\r";
            }
        }
        $html .= '</ul>'."\n";
    }
    return $html;
}

menutree2

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

This 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 .= '<ul class="im-'.$css_class.'">'."\n";
        foreach ($category['parent_cats'][$parent] as $cat_id) {
		
            if (!isset($category['parent_cats'][$cat_id])) {			
                $html .= '<li id="'.$css_class.'-'.$cat_id.'">' . $category['categories'][$cat_id]['content'] . '</li>'."\r";
            } else {
                $html .= '<li id="'.$css_class.'-'.$cat_id.'">' . $category['categories'][$cat_id]['title'] . ' <span><!-- arrow icon --></span>'."\r";
                $html .= listCategoryTree($cat_id, $category);
                $html .= '</li>'."\r";
            }
        }
        $html .= '</ul>'."\n";
    }
    return $html;
}
echo listCategoryTree(0, $categoryMulti);

im trying to achieve above condition.
With your code i have successful list parent and multi-level child title in tab title section

Now in Tab Content im trying list content for the title listed in Tab title
In Tab Content if parent have multi-level child then only last child content should display but if parent does have any child then parent content should display

But im getting content for all title parent as well as nested child here i want last child content but if parent does have child then parent content should be displayed

how to check if parent have any child, if parent have child then display content for last child only, if parent doesn’t have child then display parent content

Code for displaying 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 .= '<ul class="im-'.$css_class.'">'."\n";
        foreach ($category['parent_cats'][$parent] as $cat_id) {
            if (!isset($category['parent_cats'][$cat_id])) {
                $html .= '<li id="">
                <a href="" data-cap= "'.$css_class.'-'.$cat_id.'">'
                 . $category['categories'][$cat_id]['name'] . 
                 '</a>';
                 '</li>'."\r";
            } else {
                $html .= '<li id="">
                 <a href="" data-cap= "'.$css_class.'-'.$cat_id.'">'


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

                '</a> <span>arrow icon </span>'."\r";
                $html .= listCategoryTree($cat_id, $category);
                $html .= '</li>'."\r";
            }
        }
        $html .= '</ul>'."\n";
    }
    return $html;
}

Code trying to display last child content if parent have multi-level-nested child and if parent does have any child display 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 .= '<ul class="im-'.$css_class.'">'."\n";
        foreach ($category['contentChild'][$parent] as $cat_id) {
            if (!isset($category['contentChild'][$cat_id])) {
                $html .= '<li id="'.$css_class.'-'.$cat_id.'">
                <a href="" data-cap= "">im child content test---'
                 . $category['contentCat'][$cat_id]['content'] . 
                 '</a>';
                 '</li>'."\r";
            } else {
                $html .= '<li id="'.$css_class.'-'.$cat_id.'">
                 <a href="" data-cap= "">hello parent test------'


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

                '</a>'."\r";
                $html .= listContentTree($cat_id, $category);
                $html .= '</li>'."\r";
            }
        }
        $html .= '</ul>'."\n";
    }
    return $html;
}

how can i check for last child to display content only for last child and if parent doesnt have any child then display parent content

This mess, btw, is why the Adjacency List Model exists.

2 Likes

at the end of day question remain same… how can find out last child in multi-level-nested child and only display content for last child only and if parent is without any child display parent content only…

your suggested link shows me the process to list out all nested child or list by id but i want to display all child listing on loop and check for last child and display content of last child only… and parent content only if parent doesnt have any child

I just realized you satishinnovstudio are not the original topic creator.

I understand @satishinnovstudio and @esu are colleagues working on the same project.

Discussion continued in new thread:
Find last child in recursive function

1 Like