SitePoint Sponsor

User Tag List

Page 2 of 4 FirstFirst 1234 LastLast
Results 26 to 50 of 81
  1. #26
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, The only different was the ORDER BY parent.rgt, then instead of iterating the child of bananas it iterated its parents, very interesting.

    I have a question in the FROM clause how just by aliasing the food table AS node,
    and food again AS parent, does the statement knows the first is referring to childrens and the other for the parents. Because I have changed the names food AS colo and parent AS refre, and it still has the same results as it it were named node and parent. how does the sql when to refer for parent and when to refer as node.

  2. #27
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    node and parent are not keywords; they have no meaning to mySQL. They are names that were chosen so that someone reading the query could understand what i meant. I could have said "FROM food AS tiger, food AS purple" and gotten the same results.

    The relationship is established by telling the database what we're interested in.

    If I wanted the children of Banana,
    I would want all the nodes WHERE child.lft BETWEEN banana.lft AND banana.rgt.
    If I wanted the parents of Banana,
    I would want all the nodes WHERE banana.lft BETWEEN parent.lft AND parent.rgt

  3. #28
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    Ok the relationship by telling SQL what we are interested in ok, I thought it was done at the FROM clause but I can see the relationship start taking form in the WHERE and BETWEEN clause in this case.

    If I wanted the children of Banana,
    I would want all the nodes WHERE child.lft BETWEEN banana.lft AND banana.rgt.
    To make an example one relationship you mention in the previous post, WHERE child.lft. In this case we have Banana, a child of Yellow unfurtunally Banana doesn't have child but instead of banana we can use Yellow which in deed has Banana as a child. So if supposing that we have this

    If I wanted the children of Yellow,
    I would want all the nodes WHERE child.lft BETWEEN Yellow.lft AND Yellow.rgt.

    What do you mean by Child.lft? you mean number 8 of banana child? and Yellow lft 7 and yellow.rgt 10. What is in between 7 and 10 in this case the statement will display banana, 8 and 9 are in between 7 and 10? am I right?


    For banana parent would be whatever is next to 8 and 9 which are the left and right of Banana.

    I would want all the nodes WHERE banana.lft BETWEEN parent.lft AND parent.rgt

  4. #29
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    You're correct. Yellow has a LFT of 7, and a RGT of 10.

    What nodes have a LFT between 7 and 10? 2 of them. Yellow and Banana. (BETWEEN is inclusive)

    In the examples i'm using, there's always one "Fixed" pointer (the 'Yellow', in this example), and one "General" pointer (The child/parent/whatever). We define the fixed pointer in the second half of the WHERE. (WHERE .... AND Yellow.name = 'Yellow') so that mySQL has some definate values (Yellow.lft and Yellow.rgt). The 'General' pointer then looks at all the nodes in the table, and finds those that match the other half of the WHERE (child.lft BETWEEN Yellow.lft AND Yellow.rgt)

  5. #30
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WHERE (child.lft BETWEEN Yellow.lft AND Yellow.rgt)
    in other words The SQL give the meaning to child.lft after it picks up all the nodes between Yellow.lft AND Yellow.right. I understand that's the function right there whatever is inside the fixed pointers Yellow.lft AND Yellow.rgt then contain it within the child.lft general pointer or called child.lft? hope i am right.


    Yes I just realized it will include even the sub-parent node here called Yellow which contain Banana inside itself. And as you Said BETWEEN is inclusive I realized that it will include it in there, which in the case I only want its child or Yellow child to display. Well in the statement above which only display local nodes meaning only banana.
    Code MySQL:
    SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth FROM categories AS node, categories AS parent, categories AS sub_parent,
        (
        SELECT node.name, (COUNT(parent.name) - 1) AS depth
        FROM categories AS node,
        categories AS parent
        WHERE node.left_node BETWEEN parent.left_node AND parent.right_node
        AND node.name = :node_name
        GROUP BY node.name
        ORDER BY node.left_node
        )AS sub_tree
    WHERE node.left_node BETWEEN parent.left_node AND parent.right_node
        AND node.left_node BETWEEN sub_parent.left_node AND sub_parent.right_node
        AND sub_parent.name = sub_tree.name
    GROUP BY node.name
    HAVING depth = 1
    ORDER BY node.left_node

    In the directions it say to take < symbol out if I want the root node out, in this case Yellow 0 is out and it only display Banana but with an annoying 1 next to it, I was wondering how can I take out the Level number 1. Lol.

    with the symbol of < at the HAVING clause display like with the parent Yellow with a death of 0

    [QUOTE]
    name -- Yellow
    depth -- 0
    name -- Banana
    depth -- 1

    [/QOUTE]

    without the symbol of < at the HAVING clause display without the parent Yellow with a death of 0

    [QUOTE]
    name -- Banana
    depth -- 1
    [/QOUTE]

    wondering how to take out that depth number.


    It seems like the SELECT within first SELECT is selecting a node within another node. am I correct, WEll that very interesting, it means it can go infinite.

    If it possible to go through three SELECT containing eachother to obtain a node a third depth level and after digging to a third level only display the third level? as in the query above where it goes one level down with the options of displaying depth 0 YELLOW and depth 1 BANANA and also displaying only Banana the 1 depth level and omitting the depth 0?

    Just want to make if the same theory with of digging into one depth can apply to digging into a two or three depth query.

  6. #31
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    dont request the depth number? Why do you want the depth number anyway?

    If you dont want the node, add another AND to your WHERE.
    AND child.name != 'Yellow' (or AND child.lft != Yellow.lft works fine too)

  7. #32
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    or for that matter "WHERE child.lft BETWEEN (Yellow.lft + 1) AND Yellow.rgt" works fine too.

  8. #33
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still the number is in there I don't know what is putting that number in there
    check the COUNT function with no avail.

    Those number don't want to miss the show.

  9. #34
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth

    is what puts the number in the results.... but if you're not going to check the depth, you dont need 90% of the query you just posted.

    EDIT: What structure is the table you're trying to use this query on? It looks like it's an Adjacency Model instead of a Nested Set.

  10. #35
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you are right I have an Adjacency model which for the next step wasn't quite going to work.

    is this Structure an Adjacency model? that's the one I am using

    CREATE TABLE IF NOT EXISTS `categories` (
    `category_id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) NOT NULL,
    `left_node` int(11) NOT NULL,
    `right_node` int(11) NOT NULL,
    PRIMARY KEY (`category_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

  11. #36
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not my bad I am using a nested set model

  12. #37
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    okay you're using 'left_node' and 'right_node' as indexes. Alright. So.. what exactly are you trying to do?

  13. #38
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are right if I take the
    (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
    and the
    HAVING depth = 1
    then number disappear, but a vital function will disappear as well which is accessing the nodes by depth. For instance.

    with the function
    (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
    and the HAVING clause, the query is able to only access nodes by level, for instance it can access RED and YELLOW nodes only without the parent FRUIT and if
    sub_tree.depth + 2
    instead of
    sub_tree.depth + 1
    then it will only access the next level of NODES after RED and YELLOW which are CHERRY and BANANA only without displaying FRUIT node and RED and YELLOW node levels in this case only CHERRY and BANANA. and I imagine that if you keep adding + 3 it will keep displaying the subsequent nodes.

    Is there would be a way to get rid of that number and leaving that precious function or taking out the functions and approaching desire results with a different query structure to be display desired level alone in relation to the parent called.

  14. #39
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    even thought we were referring as Node.lft in the forum I always changed it to the indexes in the table structure here at the local server database. so that was not an issue. I always changed to the local structure.

  15. #40
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    I'm still... trying to understand what it is you want your query to do.

  16. #41
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I want to display by level according to the parent

    it is easy if looking for FRUIT levels then

    Fruit PARENT

    RED and YELLOW level 1

    CHERRY and BANANA level 2

    so we have three levels.

    If I call banana through the hierachy class
    PHP Code:
    $hierachy = new hierachy;
    $iterator = new RecursiveIteratorIterator(new recursiveArrayIterator($hierachy->getLocalSubNodes('FRUIT')));
    try {
        foreach(
    $iterator as $key=>$value)
            {
            echo 
    $value.'<br />';
            }
        }
    catch(
    Exception $e)
        {
        echo 
    $e->getMessage();
        } 
    ?> 
    The it goes to the fuction getLocalSubNodes call the query
    PHP Code:
    public function getLocalSubNodes($node_name){
    $stmt conn::getInstance()->prepare(" SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth FROM categories AS node, categories AS parent, categories AS sub_parent,
        (
        SELECT node.name, (COUNT(parent.name) - 1) AS depth
        FROM categories AS node,
        categories AS parent
        WHERE node.left_node BETWEEN parent.left_node  AND parent.right_node
        AND node.name = :node_name
        GROUP BY node.name
        ORDER BY node.left_node 
        )AS sub_tree
    WHERE node.left_node BETWEEN parent.left_node  AND parent.right_node
        AND node.left_node BETWEEN sub_parent.left_node  AND sub_parent.right_node
        AND sub_parent.name = sub_tree.name
    GROUP BY node.name
    HAVING depth = 1
    ORDER BY node.left_node  "
    );
     
    $stmt->bindParam(':node_name'$node_namePDO::PARAM_STR);
    $stmt->execute();
    return 
    $stmt->fetchALL(PDO::FETCH_ASSOC);


    The query above with the +1 will only display level two RED and YELLOW nodes or level 1, so the query is doing a fantastic job in that sense by only accessing the parent FRUIT's nodes RED and YELLOW. In other words the query access the depth by level indicated in the COUNT functions +1 mean display access level 1, + 2 access level 2 after FRUIT.

    If we were to access or put RED in the getLocalSubNodes Instead of FRUIT then RED becomes the PARENT and CHERRY becomes level 1. With +1 at the query COUNT function then it will only access the word CHERRY because it means level 1 if we have +2 then it wouldn't display anything since CHERRY doesn't have a child or there is not level2 after the parent RED.

    in other words what I want is to display the information by levels.

  17. #42
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    I really understood... none of that. You want to display information by levels...but you're complaining about the level information being present. I'm confused.

  18. #43
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol, to be more simple

    Instead of
    [QUOTE]
    Banana
    1
    [/QOUTE]

    which in part is ok becuase it is displayin by level but it including the depth 1 number which it is not need.



    Instead we are looking to display as below only the name not the depth number.
    [QUOTE]
    Banana
    [/QOUTE]

  19. #44
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think what is needed is while loop instead of a for each loop which is printing the the number is not the sql at least it seem like from the debuging done.

  20. #45
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    Use PHP to retrieve both pieces of information, then display the name only. Just because you query something doesnt mean you have to output it to the screen :P

  21. #46
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well very quick on that, a good point you have done, I thought the same thing, I thought 1 or the number depth was the #key but not it isn't the key of the depth level is "depth, php is interpreting that number as a name, and it is also interpreting the Banana as the name. So in one call is printing Banana and 1 as name.

    PHP Code:
    <?php 
    $key
    =>$value

    echo $key.'--'.$value;
    ?>
    Results
    name -- Banana
    depth -- 1

    That's only in one echo that it happens. So I don't know what is going on here.

    in other words if I do the following

    PHP Code:
    <?php 

    echo $value.'<br/>';

    ?>
    the results is

    Banana
    1
    That 1 came to stay man doesn't want to go away

  22. #47
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey it was a simple if statement inside the for each loop to get rid of the depth field.

    if($key <> "depth")

    that solved the problem of the annoying 1

  23. #48
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    while($row mysql_fetch_array($result)) {
     echo 
    $row['name'];


  24. #49
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok I thought about a while loop, because for each loop are kind of really strict about picking up everything they find, While loops are more I am going to get what I was told kind of.

  25. #50
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Most important thing that can be learned from this thread is how not to represent a problem.

    Took me (and everyone, I think) about 20 posts and 15 minutes of reading to figure out what I was even dealing with.

    When presenting a problem, it's best to be SPECIFIC SPECIFIC SPECIFIC about what you're doing, and what you would like to accomplish. Abstracting the type of data you're working with to "1.G" doesn't help matters at all.

    I still can't figure out what "BF" stands for, though I'm sure it relates to a Department at Buffalo University in some way.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •