hierarchical-data in catagory's how to add via php

Posted on 2007-08-07
Last Modified: 2013-12-12

using the above reference how does one add a child node and a node to the tree via php code

if we instead want to add a node as a child of a node that has no existing children
SELECT @myLeft := lft FROM nested_category

WHERE name = '2 WAY RADIOS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;

INSERT INTO nested_category(name, lft, rgt) VALUES('FRS', @myLeft + 1, @myLeft + 2);

If we wanted to add a new node between nodes
SELECT @myRight := rgt FROM nested_category

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

alternately id like to be able to delete and do most functions on that page but i don't mind asking them in separate questions if need be.
but id really like to understand how to add the data to my table im making
thank you in advance for any code or help you may provide.

Question by:Johnny
    LVL 3

    Accepted Solution

    heh i had a little difficulty with this one considering mysql's limited query quantity.
    Note that the $parentName parameter can be easily modified to use a category_id or some better method of normalization.

    function mysql_tree_add($title, $parentName = "") {
          $sql_pe = "SELECT * FROM categ WHERE title='$parentName'";
          $q = mysql_query($sql_pe) or die(mysql_error());
          if(mysql_num_rows($q) == 0) {
                //$pe['lft'] = 1;
                //$pe['rgt'] = 2;
                //$pe['title'] = "";
                $pe = mysql_fetch_assoc($q);

          //now update the rows
          mysql_query("UPDATE categ SET rgt=rgt+2 WHERE rgt>{$pe['rgt']}-1");
          mysql_query("UPDATE categ SET lft=lft+2 WHERE lft>{$pe['rgt']}-1");
          //now add the item
          mysql_query("INSERT INTO categ (`parent`,`title`,`lft`,`rgt`) VALUES ('{$pe['title']}','$title',{$pe['rgt']},{$pe['rgt']}+1)");

    Author Comment


    @mysql_select_db($database) or die(mysql_error());

    function mysql_tree_add($name, $parentName = "") {
          $sql_pe = "SELECT * FROM nested_category WHERE name='$parentName'";
          $q = mysql_query($sql_pe) or die(mysql_error());
          if(mysql_num_rows($q) == 0) {
                Echo $name.' must be parent<BR>';
                //$pe['lft'] = 1;
                //$pe['rgt'] = 2;
                //$pe['title'] = "";
                echo "adding sub ".$name. ' to parent '.$parentName.'<BR>';
                $pe = mysql_fetch_assoc($q);
    //echo 'rgt:'.$pe['rgt'].'<BR>';
    //echo 'lft:'.$pe['lft'].'<BR>';

          //now update the rows
          mysql_query("UPDATE nested_category SET rgt=rgt+2 WHERE rgt>{$pe['rgt']}-1") or die(mysql_error());
          mysql_query("UPDATE nested_category SET lft=lft+2 WHERE lft>{$pe['rgt']}-1") or die(mysql_error());
          //now add the item
          mysql_query("INSERT INTO nested_category (`name`,`lft`,`rgt`) VALUES ('$name',{$pe['rgt']},{$pe['rgt']}+1)") or die(mysql_error());

    function display_nested_category($root) {
       // retrieve the left and right value of the $root node
       $result = mysql_query('SELECT lft, rgt FROM nested_category '.
                              'WHERE name="'.$root.'";');
       $row = mysql_fetch_array($result);

       // start with an empty $right stack
       $right = array();

       // now, retrieve all descendants of the $root node
       $result = mysql_query('SELECT name, lft, rgt FROM nested_category '.
                              'WHERE lft BETWEEN '.$row['lft'].' AND '.
                              $row['rgt'].' ORDER BY lft ASC;');

       // display each row
       while ($row = mysql_fetch_array($result)) {
           // only check stack if there is one
           if (count($right)>0) {
               // check if we should remove a node from the stack
               while ($right[count($right)-1]<$row['rgt']) {

           // display indented node name
           echo str_repeat('.&nbsp;.&nbsp;.',count($right)).$row['name']."<br>\n";

           // add this node to the stack
           $right[] = $row['rgt'];

    echo '<HR>';
    echo '<HR>';
    mysql_tree_add('C++.NET', '.NET');
    mysql_tree_add('C#.NET', '.NET');
    mysql_tree_add('J#.NET', '.NET');
    mysql_tree_add('ASP.NET', '.NET');
    echo '<HR>';

    the above works great for me
    thanks for setting me on the right track, i don't think im going to convert the other ones.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now