This is a simple PHP script to generate a Category Tree structure based on parent/child MySQL database. Database structure looks like this:
And will generate a structure like this:
Category 1
This is much simpler and works very well. The other code I found on this forum didn't work at all and was very complicated, so I wrote an easy script. Just 1 note: It's meant for a structure that does not have a root element.
If you do use the code, I just ask that you give me credit for the code. Thanks!
<?php
// Recursive function to generate a parent/child tree
// Without the need for a Root parent
// Written by: Brian Parnes
// 13 March 2006
//$connect = mysql_connect(HOST_NAME, USERNAME, PASSWORD);
//mysql_select_db(DATABASE);
$nav_query = mysql_query("SELECT * FROM `categories` ORDER BY `category_id`");
$tree = ""; // Clear the directory tree
$depth = 1; // Child level depth.
$top_level_on = 1; // What top-level category are we on?
$exclude = array(); // Define the exclusion array
array_push($exclude, 0); // Put a starting value in it
while ( $nav_row = mysql_fetch_array($nav_query) )
{
$goOn = 1; // Resets variable to allow us to continue building out the tree.
for($x = 0; $x < count($exclude); $x++ ) // Check to see if the new item has been used
{
if ( $exclude[$x] == $nav_row['category_id'] )
{
$goOn = 0;
break; // Stop looking b/c we already found that it's in the exclusion list and we can't continue to process this node
}
}
if ( $goOn == 1 )
{
$tree .= $nav_row['title'] . "<br>"; // Process the main tree node
array_push($exclude, $nav_row['category_id']); // Add to the exclusion list
if ( $nav_row['category_id'] < 6 )
{ $top_level_on = $nav_row['category_id']; }
$tree .= build_child($nav_row['category_id']); // Start the recursive function of building the child tree
}
}
function build_child($oldID) // Recursive function to get all of the children...unlimited depth
{
global $exclude, $depth; // Refer to the global array defined at the top of this script
$child_query = mysql_query("SELECT * FROM `categories` WHERE parent_id=" . $oldID);
while ( $child = mysql_fetch_array($child_query) )
{
if ( $child['category_id'] != $child['parent_id'] )
{
for ( $c=0;$c<$depth;$c++ ) // Indent over so that there is distinction between levels
{ $tempTree .= " "; }
$tempTree .= "- " . $child['title'] . "<br>";
$depth++; // Incriment depth b/c we're building this child's child tree (complicated yet???)
$tempTree .= build_child($child['category_id']); // Add to the temporary local tree
$depth--; // Decrement depth b/c we're done building the child's child tree.
array_push($exclude, $child['category_id']); // Add the item to the exclusion list
}
}
return $tempTree; // Return the entire child tree
}
echo $tree;
?>
I just wrote this a few minutes ago...perhaps it will help someone. Searches for all root elements. Also supports multipe child->parent relationships. Supports up to 5 levels.
$query = "SELECT faq_categories_id id FROM faq_cat_relationship WHERE faq_parent_id is Null OR faq_parent_id = ''";
$firstLevel = $this->runQuery($query);
while(list($firstID) = @mysql_fetch_row($firstLevel)){
$firstCategoryName = $this->getCatNameByID($firstID);
$query = "SELECT faq_categories_id FROM faq_cat_relationship WHERE faq_parent_id = '$firstID'";
$secondLevel = $this->runQuery($query);
$catArray[$firstID] = '';
while(list($secondID) = @mysql_fetch_row($secondLevel)){
$secondCategoryName = $this->getCatNameByID($secondID);
$query = "SELECT faq_categories_id FROM faq_cat_relationship WHERE faq_parent_id = '$secondID'";
$thirdLevel = $this->runQuery($query);
$catArray[$firstID][$secondID] = '';
while(list($thirdID) = @mysql_fetch_row($thirdLevel)){
$thirdCategoryName = $this->getCatNameByID($thirdID);
$query = "SELECT faq_categories_id FROM faq_cat_relationship WHERE faq_parent_id = '$thirdID'";
$fourthLevel = $this->runQuery($query);
$catArray[$firstID][$secondID][$thirdID] = '';
while(list($fourthID) = @mysql_fetch_row($fourthLevel)){
$query = "SELECT faq_categories_id FROM faq_cat_relationship WHERE faq_parent_id = '$fourthID'";
$fifthLevel = $this->runQuery($query);
$catArray[$firstID][$secondID][$thirdID][$fourthID] = '';
while(list($fifthID) = @mysql_fetch_row($fifthLevel)){
$catArray[$firstID][$secondID][$thirdID][$fourthID][$fifthID] = '';
}
}
}
}
unset($fourthID, $thirdID, $secondID, $firstID);
}//end while loop
return $catArray;
}
It's great, but hard to understand and seems like you don't care about performance. You might be looking for something like: http://www.browse-tutorials.net/tutorial/create-treeview-using-recursion-in-php
Hey thank you so much for sharing code I was trying to build this code from last few days but i can't achieved displaying properly in dropdown upto infinite level.It works & shows subcategoris after respective parent but one can't differentiate it. Your one is perfect.
My function was as follows
<?php
function finddepth($byid)
{
//check weather category has child
//echo "**Passed Id=".$byid;
$re_query=mysql_query("SELECT FROM categories where parent='$byid'");
$num_rows = mysql_num_rows($re_query);
//echo " Has children:".$num_rows."
";
if(mysql_num_rows($re_query) > '0')
{
while($recursive_rw=mysql_fetch_array($re_query))
{
$p=$recursive_rw['id'];
$catnm=$recursive_rw['category'];
$parent=$recursive_rw['parent'];
$pre="|_";
if($parent=="0")
{
?>
<option><?php echo $catnm; ?></option>
<?php
}
else
{
echo '<option>'.$pre.$catnm.'</option>' ;
}
finddepth($p);
}//end while
}//end if
}//end of function
?>
Thanks for the code but i tried my before seen this code it helpme alot
this i writed before seen your code...
<?php
$dbconn = @mysql_connect("localhost","ersin","") or die("DB ERROR");
@mysql_select_db("cats",$dbconn) or die("DB ERROR");
function childcat($id,$name){
$query = "SELECT * FROM categories WHERE parent='$id'";
$result = @mysql_query($query) or die ("Table doesn't exists!");
while($row = mysql_fetch_array($result)){
echo "
$query = "SELECT * FROM categories WHERE parent='0'";
$result = @mysql_query($query) or die ("Table doesn't exists!");
echo "
echo "
This is nice. But, consider if you have 100 or more categories, how many times will you execute the query inside your function per request. You have already fetched all categories from a table. So, it would be much faster to pass an array of all categories as the second parameter and loop throughout them.
Hi ersin,
I am willing to fetch all the subcategories and their subcategories by passing parent category name to a MYSQL select statement.
Can you please share if you are aware about it?