Categories from Database with Folder View and PHP

Question:

---

How to get my categories from database and present them in Folder View using PHP?

 

Answer:

---

For storing the categories I use table with the following structure:

1. id - Record Id (the primary key of the record).
2. parent_id - store the Id of the parent category (Used for building the tree structure).
3. name - Name of the category.
4. description - Category description.
   

Field Name	Field Type	Length	Extra	Primary
id	int		auto_increment	Yes
parent_id	int
name	varchar	255
description	varchar	255

You could also use the following SQL statement to build the table:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) engine="InnoDB" DEFAULT charset="latin1" auto_increment="11" ;

You could also try the following SQL statement for creating some sample data into the database:

INSERT INTO `categories` (`id`, `parent_id`, `name`, `description`) VALUES (1, 0, 'ASP', 'ASP Category'),
(4, 1, 'Books', 'A wide selection of books regarding ASP programming'),
(5, 1, 'Scripts and Components', 'Categorized database of ASP components and applications'),
(6, 0, 'PHP', 'PHP Category'),
(7, 6, 'Books', 'Selections of PHP programming-related books'),
(8, 6, 'Articles', 'Articles on various aspects of PHP development.'),
(9, 6, 'Tips and Tutorials', 'Online tutorials and guidelines on how to program in PHP'),
(10, 6, 'Software and Servers', 'Software and servers to aid in PHP development');

For reading and building the tree could try the following sample code:

<?php
class Category
{
    //The connection to a MySQL server
    var $dbConnection = null;
   
    //The categories as an array
    var $categories = array();    

    function __construct($cnn)
    {
        $this-> Category($cnn);
    }  

    function Category ($cnn)
    {
        //Initialize the connection and the categories
        $this->dbConnection = $cnn;
        $this->categories = array();
        $this->getCategories();
    }
   
    function getCategories()
    {
        //Read the records and fill the categories
        $query = "SELECT id, parent_id, name, description FROM categories ORDER BY id";
          $result = mysql_query($query, $this->dbConnection) or die(mysql_error());
       
        $i = 0;
        while ($row = mysql_fetch_assoc($result)) {
            $this->categories[$i] = $row;
            $i++;
        }
        //Free the resource
        mysql_free_result($result);
    }

    function viewTree()
    {
        //Generate tree list
        $output = "";
        for ($i = 0; $i < count($this->categories); $i++) {
            if ($this->categories[$i]["parent_id"]=="0")
            {
                $output .= "<li title=\"".$this->categories[$i]["description"]."\">".$this->categories[$i]["name"]."\n<ul>\n";
                $output .= $this->getAllChildren($this->categories[$i]["id"]);
                $output .= "\n</ul>\n</li>";
            }
        }
        return $output;
    }
   
    function getAllChildren($parent_id)
    {
        //Get all the nodes for particular ID
        $output = "";
        for ($i = 0; $i < count($this->categories); $i++) {
            if ($this->categories[$i]["parent_id"] == $parent_id)
            {
                $output .= "<li title=\"".$this->categories[$i]["description"]."\">".$this->categories[$i]["name"]."\n<ul>\n";
                $output .= $this->getAllChildren($this->categories[$i]["id"]);
                $output .= "\n</ul>\n</li>";
            }
        }
        return $output;
    }
}
?>

Now to use the above code for your Folder View you just have to:

1. Paste the above code in into your page
2. Replace the automatically generated list with the one from viewTree method.

For example:

<script language="JavaScript" type="text/javascript">
<!--
dmxListToTree({
  bullets : 'plusminus',
  icons   : true,
  struct  : true,
  theme   : 'Orange_Folders32x32',
  objId   : 'FolderView'
});
//-->
</script>
<ul class="dmxtree Orange_Folders32x32" id="FolderView">
<?php
//$cnn_TestDB is the connection defined by Dreameaver
$tree = new Category($cnn_TestDB);
echo $tree->viewTree();
?>
</ul>
 

---

DISCLAIMER:

This is extra complimentary content which purpose is to show additional usage that is not part of the product, i.e. it suggests tips for extending the functionality of the product by the users themselves. It is provided "as is", without warranty of any kind, express or implied , including but not limited to the warranties of merchantability, fitness for a particular purpose and nonfringement of third party rights.

DMXzone does not take responsibility to support the suggested content to be fully compatible and working as intended and does not provide support for extended functionality which is not included in the current release of the extension.

It is highly recommended that only more advanced developers use it.