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
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>
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
/*
* 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.
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.
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.
which looks like this.
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 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
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
