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

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

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);

and
If we wanted to add a new node between nodes
SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';



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.

JohnnyAsked:
Who is Participating?
 
SissonConnect With a Mentor Commented:
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'] = "";
      }else{
            $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)");
}
0
 
JohnnyAuthor Commented:
<?php
$username="xxxxxxx";
$password="xxxxxx;
$database="eqa_tree";

mysql_connect("localhost",$username,$password);
@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'] = "";
      }else{
            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']) {
               array_pop($right);
           }
       }

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

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

display_nested_category('Topics');
echo '<HR>';
//mysql_tree_path_to_node('PHP');
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>';
display_nested_category('Topics');
?>

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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.