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

Posted on 2007-08-07
Medium Priority
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

Accepted Solution

Sisson earned 2000 total points
ID: 19651549
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

ID: 19657578

@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 Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

862 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