Skip to content

Instantly share code, notes, and snippets.

@arvindsvt

arvindsvt/php-fetchCategorytree-function

Last active March 22, 2018 15:56
Show Gist options
  • Save arvindsvt/7c5c0ffaa537adfd5a5ffe66ce3a2808 to your computer and use it in GitHub Desktop.
Save arvindsvt/7c5c0ffaa537adfd5a5ffe66ce3a2808 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
php-fetchCategorytree-function
http://www.iamrohit.in/create-nice-responsive-tree-view-menu-using-jquery-jstree-plugin/
http://www.webslesson.info/2017/05/make-treeview-using-bootstrap-treeview-ajax-jquery-with-php.html
http://avenir.ro/revisiting-the-multilevel-menu-in-php-with-some-additional-code-bootstrap-framework/
https://stackoverflow.com/questions/19324049/creating-menu-list-in-php-from-nested-list-to-multidimensional-array-and-return?rq=1
https://stackoverflow.com/questions/35527414/build-a-bootstrap-drop-down-multi-level-menu-from-a-php-array
https://www.sitepoint.com/community/t/dynamic-multi-level-css-drop-down-menu-with-php-and-mysql-support-problem/34689/3
<?php
$node= 'Jeans';
get_path($node);
function get_path($node) {
global $conn;
$sql = "SELECT parent FROM category WHERE name ='$node'";
$result = $conn->query($sql);
$path = array();
while($row = $result->fetch_assoc()) {
// echo $row["parent"];
echo get_name($row["parent"]).'/';
}
$conn->close();
}
function get_name($Id){
global $conn;
$sql = "SELECT name FROM category WHERE cid ='$Id'";
$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
$name = $row["name"];
}
return $name;
}
?>
<?php
$parenIdOption = $_POST['parenIdOption'];
$name = $_POST['name'];
insert($name , $parenIdOption);
function insert($name , $parenIdOption) {
$servername = "localhost";
$username = "root";
$password = "mysql";
$dbname = "demo";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO category (cid, name, parent)
VALUES ('' , '$name' , '$parenIdOption')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
}
?>
<div id="">
<form action="insert.php" method="post">
<?php
$categoryList = fetchCategoryTree();
?>
<div style="text-align:left;" >
<select name="parenIdOption" style="width:200px;height:35px;border:1px solid #6d37b0;padding:5px;">
<?php foreach($categoryList as $cl) { ?>
<option value="<?php echo $cl["id"]; ?>"><?php echo $cl["id"].$cl["name"]; ?></option>
<?php } ?>
</select>
</div>
Name: <input type="text" name="name"><br>
<input type="submit">
</form>
</div>
<?php
error_reporting(E_ALL & ~E_NOTICE);
/*$link = mysql_connect('localhost', 'root', '');
if (!$link) {
die('Not connected : ' . mysql_error());
}
$db_selected = mysql_select_db('demo', $link);
if (!$db_selected) {
die ('Can\'t select demo : ' . mysql_error());
}*/
$servername = "localhost";
$username = "root";
$password = "mysql";
$dbname = "demo";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
require_once 'functions.php';
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link rel="stylesheet" href="style.css" type="text/css" />
<title>Create Category tree with PHP and mysql</title>
</head>
<body>
<div id="container">
<div id="body">
<div class="mainTitle" >Create Category tree with PHP and mysql</div>
<div class="height20"></div>
<?php echo $emsg; ?>
<article>
<?php
$categoryList = fetchCategoryTree();
?>
<div style="text-align:center;" >
<select style="width:200px;height:35px;border:1px solid #6d37b0;padding:5px;">
<?php foreach($categoryList as $cl) { ?>
<option value="<?php echo $cl["id"]; ?>"><?php echo $cl["name"]; ?></option>
<?php } ?>
</select>
</div>
<div class="height20"></div>
<h4>User Tree Listing will be displayed below(if any user will be in database):</h4>
<ul>
<?php
$res = fetchCategoryTreeList();
foreach ($res as $r) {
echo $r;
}
?>
</ul>
<div class="height10"></div>
</article>
<div class="height10"></div>
</div>
<footer>
<div class="copyright"> &copy; 2013 - 2014 <a href="http://www.thesoftwareguy.in" target="_blank">thesoftwareguy</a>. All rights reserved </div>
<div class="footerlogo"><a href="http://www.thesoftwareguy.in" target="_blank"><img src="http://www.thesoftwareguy.in/thesoftwareguy-logo-small.png" width="200" height="47" alt="thesoftwareguy logo" /></a> </div>
</footer>
</div>
</body>
</html>
<?php
function fetchCategoryTree($parent = 0, $spacing = '', $user_tree_array = '') {
global $conn;
if (!is_array($user_tree_array))
$user_tree_array = array();
$sql = "SELECT `cid`, `name`, `parent` FROM `category` WHERE `parent` = $parent ORDER BY cid ASC";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
//var_dump($row['name']);
$user_tree_array[] = array("id" => $row['cid'], "name" => $spacing . $row['name']);
$user_tree_array = fetchCategoryTree($row['cid'], $spacing . '&nbsp;&nbsp;', $user_tree_array);
}
}
return $user_tree_array;
}
function fetchCategoryTreeList($parent = 0, $user_tree_array = '') {
global $conn;
if (!is_array($user_tree_array))
$user_tree_array = array();
$sql = "SELECT `cid`, `name`, `parent` FROM `category` WHERE `parent` = $parent ORDER BY cid ASC";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
$user_tree_array[] = "<ul>";
while($row = $result->fetch_assoc()) {
$user_tree_array[] = "<li>". $row['name'] ."</li>";
$user_tree_array = fetchCategoryTreeList($row['cid'], $user_tree_array);
}
$user_tree_array[] = "</ul>";
}
return $user_tree_array;
}
?>CREATE TABLE IF NOT EXISTS `category` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`parent` int(11) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=35;
INSERT INTO `category` (`cid`, `name`, `parent`) VALUES
(1, 'Hardware', 0),
(2, 'Software', 0),
(3, 'Movies', 0),
(4, 'Clothes', 0),
(5, 'Printers', 1),
(6, 'Monitors', 1),
(7, 'Inkjet printers', 5),
(8, 'Laserjet Printers', 5),
(9, 'LCD monitors', 6),
(10, 'TFT monitors', 6),
(11, 'Antivirus', 2),
(12, 'Action movies', 3),
(13, 'Comedy Movies', 3),
(14, 'Romantic movie', 3),
(15, 'Thriller Movies', 3),
(16, 'Mens', 4),
(17, 'Womens', 4),
(18, 'Shirts', 16),
(19, 'T-shirts', 16),
(20, 'Shirts', 16),
(21, 'Jeans', 16),
(22, 'Accessories', 16),
(23, 'Tees', 17),
(24, 'Skirts', 17),
(25, 'Leggins', 17),
(26, 'Jeans', 17),
(27, 'Accessories', 17),
(28, 'Watches', 22),
(29, 'Tie', 22),
(30, 'cufflinks', 22),
(31, 'Earrings', 27),
(32, 'Bracelets', 27),
(33, 'Necklaces', 27),
(34, 'Pendants', 27);
* {
margin: 0;
}
html, body {
margin:0;
padding:0;
height:100%;
}
body {
border: 0;
font: 12px "Trebuchet MS";
color: #000;
background:#fff;
}
a{ text-decoration: none; color:#6D37B0; }
a:hover { color: #8D8D8D; }
#body{
width: 1004px;
margin: 0 auto;
max-width: 100%;
padding:20px 0 70px 0;
min-height: 600px;
height: auto;
}
#container {
min-height:600px;
position:relative;
}
footer {
position: relative;
height: 70px;
width: 100%;
background:#f7f7f7;
color:#000;
}
.copyright{float:left;padding:10px 0 0 20px; }
.footerlogo{float:right;padding:10px 20px 0 0;}
.resultRow{width:100%; margin:2px 0; border:1px solid #8D8D8D;padding:2px; color:#000;}
.height10{clear:both;height:10px;}
.height20{clear:both;height:20px;}
.height30{clear:both;height:30px;}
.mainTitle{ font-size:35px; text-align:center; padding-bottom:20px;text-decoration:underline;}
.title{ font-size:20px; padding:10px; text-align:center;}
.links{border:1px solid #000; padding:5px; text-decoration:none;color:#000;}
.links:hover{text-decoration:underline;color:#fff; background:#6D37B0;}
.selected{border:1px solid #000; padding:5px; text-decoration:none;color:#fff; background:#6D37B0;}
/****************************************************/
table {
*border-collapse: collapse; /* IE7 and lower */
border-spacing: 0;
width: 100%;
}
.bordered {
border: solid #ccc 1px;
-webkit-box-shadow: 0 1px 1px #ccc;
-moz-box-shadow: 0 1px 1px #ccc;
box-shadow: 0 1px 1px #ccc;
}
.bordered td, .bordered th {
padding: 10px;
border-bottom: 1px solid #f2f2f2;
}
.bordered th {
background-color: #eee;
border-top: none;
text-align:left;
}
.bordered tbody tr:nth-child(even) {
background: #f5f5f5;
border:1px solid #000;
}
.bordered tbody tr:hover td {
background: #d0dafd;
color: #339;
}
.textboxes{ width:280px; border:1px solid #000; height:20px; }
.required{color:#F00;}
https://stackoverflow.com/questions/35527414/build-a-bootstrap-drop-down-multi-level-menu-from-a-php-array
based on for mysqli https://hotexamples.com/examples/-/-/fetchCategoryTree/php-fetchcategorytree-function-examples.html
https://www.thesoftwareguy.in/create-category-tree-php-mysql/
https://www.sitepoint.com/hierarchical-data-database/
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
http://www.mysqltutorial.org/mysql-adjacency-list-tree/
http://www.mysqltutorial.org/mysql-recursive-cte/
http://www.mysqltutorial.org/mysql-adjacency-list-tree/
http://csharp-video-tutorials.blogspot.in/2012/09/common-table-expressions-part-49.html
https://communities.bmc.com/docs/DOC-9902
https://stackoverflow.com/questions/4697085/path-enumeration-mysql-querying-to-create-a-bread-crumb
https://stackoverflow.com/questions/4831154/which-hierarchical-model-should-i-use-adjacency-nested-or-enumerated?rq=1
https://stackoverflow.com/questions/8833535/how-to-transform-a-mssql-cte-query-to-mysql
https://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159
https://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/7161#7161
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment