SitePoint Sponsor

User Tag List

Page 1 of 4 1234 LastLast
Results 1 to 25 of 81
  1. #1
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What type of relation is this scenario?

    1 is the profile


    and there is a list in the right where 1 is related to all the elements in box 1, box 2, box3, box4, box5

    Also all the elementes in box1 are related to 1, box2,box3,box4,box5

    Also all the elements in box 2 are related to 1 all the elements inside box1, box3,box4,box5

    Also all t he elements in box3 are related to 1 all the elements inside box4, box1,box2,box5

    also all the elements in box5 are related to 1 and all the elements inside box4,box1,box2,box3

    with the escenario above How can I reach such a SQL stament and tables structure to acomplisht this relationship?

    What type of relational database or approach I should build to get this.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by co.ador View Post
    1 is the profile
    you lost me right there


    Quote Originally Posted by co.ador View Post
    and there is a list in the right
    list? right? right of what?

    sorry, none of that makes any sense to me

    are you talking about a web page? what are boxes? what are elements? are you talking about the Document Object Model?

    whatever it is, it sounds like you are talking about many-to-many relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry for a slow reply I wanted to picture an scenario so it can be clearer.




    It does seem like a many-to-may relationship but not idea yet where to start.

    the list appears once user click on either box1-box2-or box3, Each of those boxes are related to 1.G element on profile or Element1.G another element on profile in snippet representation I have done above to easy the understanding of this relation. Those elements on profile has its values that describe them, 1.G and element is describe by 1.G value1, 1.G value2, 1.G value3. then the three boxes I as said before they have a unique kind of relation to 1.G and all the elements inside them has the G gene that relates it to 1.G. 1 and element1 are the name and G is like unique value that relate all of them. Different nick but they all share the same gene Called G. G can be another letter or name, but is the element that relate them together. Also the three boxes contain different names which carry another array of relation beside the G that all of them carry. Once one of the names with G name in this case is click them they pass to be part of the profile div and all of the elements inside of each of the boxes will change according to what relationship they have to the element on profile.. One thing that will never change is that they will all have the relationship on the G, The box relationship will change depending on the relation they have to the boxes.


    I don't have an idea on how to make a database structure or skeleton for this type of scenario. Do have an idea on how to build such a database relationship here?

    If you still don't get the picture let me know please.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, i don't get it

    the text in your diagram is too small to read, and the text in your post is cut off by the forum's css (it's wider than my page because of your image, but there's no horizontal scrollbar)

    i'm sorry, i don't think i can help you

    maybe someone else
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

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


    this image has the letter inside the image a little bit bigger, just in case someone get an idea on how to structure this scenario.

    Thanks.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i still don't get a horizontal scrollbar and can't see the whole image, but that's not your fault, that's the forum css

    at least i can right-click-save the picture and open it up in my graphics program to look at it

    unfortunatley it still makes no sense to me at all

    maybe someone else
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am going to make another representation of the scenario later to clear that up. Thanks r937, It is hard to make a representation becuase it is indeed complicated.

  8. #8
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    okay, i'm gonna take a stab at this one, r937, cause i THINK i get it.

    What you're describing is a combination of AJAX calls to a mySQL database, based on... what looks like a dropdown select box. You need 1 table to associate this.

    Table1
    BoxID (INT)
    ElementName (VARCHAR)

    Natural Composite Key: (BoxID,ElementName) PRIMARY

    Then you need to learn the basics of AJAX to make the webpage call a page that pulls the relevant data such as:

    PHP Code:
    <?php
    $db 
    = new mysqli('your','database','stuff','here');
    $res $db->query("SELECT ElementName FROM Table1 WHERE BoxID = ".$_GET['boxid']);
    echo 
    "<div>";
    while(list(
    $row) = $res->fetch_row()) {
      echo 
    $row."<br />";
    }
    echo 
    "</div>";
    ?>
    then have your AJAX call that page passing it the value attribute of the dropdown, and use Javascript's DOM editor to replace the dropdown with the results of the AJAX call.

  9. #9
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    (If you wanted to map it ALL into database, btw, it would be 3 tables - 1 table to hold the elements of "1.G", 1 to hold the "Elements", and 1 to join the two in a many-to-many format.)

  10. #10
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought it was going to be more complicated, WEll I am going to take at look a this tonight, meanwhile I have make other representations to help understand the subject a little bit more. I think the images below are clearer and better draw. I was looking at pre-order, post order,recursive, nonrecursive DFT and BFT algorithms. Where the cursor will go from the leaf through the nodes until the root or the opposite from the root to the leaf. But in this algorithms it seems like the paths will make stops in some of the nodes, get selective information in each level, instead the other method will itarate through all of the nodes in the tree.

    what I want to do is the next picture is a profile which is going to display information hightlighted RED in the last picture representing the tree



    Tree path of the cursor according to what the profile is asking to display through the links, The links only want to display that specific Data path in the picture below highlighted in RED



    References to DFT and BFT algorithms explained here youtube video

  11. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    I'm assuming when you define a person in your personnel table, you're assigning them to a department. We are now, then, discussing the construction of the departments table and how to search it.

    I think.. possibly what you're trying to describe is covered by an NSM-based solution.

    A table of structure:

    Name, Lft, Rgt, departmentID, <plus whatever extra fields you want to store>

    Where Lft and Rgt are UNIQUE, such that:
    Root Node has LFT 1.
    First Child of any node has LFT <parentLFT>+1
    Subsequent Children of that node have LFT <previousChildRGT>+1
    A Leaf has RGT <LFT>+1.
    A Hub has RGT <lastChildRGT>+1.

    NSM is an inorder numbering scheme (Number the LFT, move through all possible children, number the RGT).

    Retrieving a pathway from Root to any given node is then defined by the query:
    Code MySQL:
    SELECT parent.name
    FROM department AS node,
    department AS parent
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND departmentID = <Fill In Value From Previous query here>
    ORDER BY parent.lft;

    (And obviously ordering by parent.rgt will give you the Node-To-Root form of the path, instead of Root-To-Node)

    Managing Hierarchical Data in MySQL shows the models of this system.

  12. #12
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great information Starlion, and thank you for the source in Mysql is a very detail information, After you have sent me this information I started researching on this type of database I realized sitepoint blog also talks about this type of relationship I thought It would be great to share too,

    Storing Hierarchical Data in a Database Article SitePoint

  13. #13
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    (And obviously ordering by parent.rgt will give you the Node-To-Root form of the path, instead of Root-To-Node)
    In this part you mean that if a user access a node ORDER BY parent.rgt will give user access to root? Ok I understand becuase of the number in the left. In this particular case it require the cursor to find Node-To-Root and Node-To Children, and not just the Root Node but particular NODE I will choose and Particular Children. I know that just by ORDERING BY parent.rgt, parent.lft at the ORDER clause will just give me all the ROOT and Children NODES. Where can I specify which nodes and children out of the tree I want to display? that will be done in the dynamic language or in the SQL statement?

    Thank

  14. #14
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Okay think about it this way.

    If I have a tree (Let's take the tree from page 2 of the Sitepoint Blog as our example), and i run the query, looking for "name = 'Banana'", the query returns the following results:

    Code MySQL:
    SELECT parent.name,parent.lft,parent.rgt
    FROM food AS node,
    food AS parent
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.name = "Banana"
    ORDER BY parent.lft;

    Code:
    Name,lft,rgt
    Food,1,18
    Fruit,2,11
    Yellow,7,10
    Banana,8,9
    Now if I sort by LFT, then i get them in Root-To-Node form: Food,Fruit,Yellow, Banana.
    If I sort by RGT, I get them in Node-To-Root form: Banana,Yellow,Fruit,Food.

    Changing your ORDER BY will not alter the number of rows you get, just the order that those rows are presented in.

  15. #15
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good, from your explanation what I don't understand why you have two diferrent aliases for the same table, and I also a little bit confused in the title and parent fields in the sitepoint page 2 table structures after the tree. Because they called food two rows how is that possible? to call a row the same name. I don't know how is that working in there.

  16. #16
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    There are two aliases for the table in the query because I need to compare them. "WHERE node.lft BETWEEN parent.lft AND parent.rgt".

    node is used to define what i'm interested in. (The node named Banana). parent is used to find all the information related to that node.

  17. #17
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's exactly what I am looking for, point to an x node, and display information ORDER BY lft or rgt depending on the directions. Now I didn't know you could aliases one table twice. It is new for me.


    In post #14 in the SELECT statement you didn't select, node.lft and you are using it in the WHERE clause. will it pull that information anyway?

    Also what table structure are you using? the one in second page of the sitepoint blog?

  18. #18
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    In the sitepoint blog you linked above, on page #2 there's a tree displayed. That's what i was using for the example.

    I dont need to select node.lft in order to use it in the WHERE clause; what you pull is up to you, of course. Anything in the parent table you SELECT will be returned for all nodes in the chain (which includes the node you are looking for, which was "Banana" in the example above.)

    If I had selected node.lft, what i would have gotten would be:
    Code MySQL:
    SELECT parent.name,node.lft
    FROM food AS node,
    food AS parent
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.name = "Banana"
    ORDER BY parent.lft;

    Code:
    name,lft
    --------
    Food,8
    Fruit,8
    Yellow,8
    Banana,8
    which is not what i think you want.

  19. #19
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i am doing this in the editor
    PHP Code:
    <?php
    include "scripts/connect_to_mysql.php"


    $sql2mysql_query("SELECT fruit.parent, fruit.lft, fruit.rgt
    FROM  fruit AS node,
    fruit AS parent1 WHERE node.lft BETWEEN parent1.lft AND parent1.rgt
    AND node.parent = 'Banana' 
    ORDER BY parent1.lft;"
    );
    $categoryCount mysql_num_rows($sql2);
    if (
    $categoryCount>)
         {
              while(
    $row2 mysql_fetch_array($sql2))
                      {
                            
                            
    $name$row2["node.parent"];
                            
                        
                        }
            
            echo 
    '<div>'.$name.'</div>'
             
         }

       
    ?>
    Before getting to the while loop there is a error in mysql_num_rows it seems like the SQL statement is supplying a none valid argument as the error says. hopefully there is a mistake int he statement I am not able to see it yet.

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home3/nyhungry/public_html/parent/cacofamily.php on line 10
    Table structure is identical

    Code SQL:
    --
    -- Table structure for table `fruit`
    --
     
    CREATE TABLE IF NOT EXISTS `fruit` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `parent` VARCHAR(255) NOT NULL,
      `title` VARCHAR(255) NOT NULL,
      `lft` INT(11) NOT NULL,
      `rgt` INT(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;
     
    --
    -- Dumping data for table `fruit`
    --
     
    INSERT INTO `fruit` (`id`, `parent`, `title`, `lft`, `rgt`) VALUES
    (1, '', 'Food', 1, 18),
    (2, 'Food', 'Fruit', 2, 11),
    (3, 'Fruit', 'Red', 3, 6),
    (4, 'Red', 'Cherry', 4, 5),
    (5, 'Fruit', 'Yellow', 7, 10),
    (6, 'Yellow', 'Banana', 8, 9),
    (7, 'Food', 'Meat', 12, 17),
    (8, 'Meat', 'Beef', 13, 14),
    (9, 'Meat', 'Pork', 15, 16);


    Please can you take a look at the statement?

  20. #20
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    O...kay. We've got a whole buncha issues here. (Now I want a banana... should have chosen a non-food example...)

    Line by line, then.
    Code MySQL:
    SELECT fruit.parent, fruit.lft, fruit.rgt

    "fruit.parent" shouldnt exist. the Parent field is useless in a NSM table. You also will have no table known as 'fruit' as far as the query is concerned. Why? Lets look at the next line.

    Code MySQL:
    FROM  fruit AS node,

    So now the query knows about a table called "node". It's a 'copy' of the fruit table, but it's called node.

    Code MySQL:
    fruit AS parent1

    Same thing, except this time it's parent1. so the query knows about 2 tables: node, and parent1.

    Code MySQL:
     WHERE node.lft BETWEEN parent1.lft AND parent1.rgt

    Perfect. This establishes the relationship between the chain of nodes connecting root to our target node.

    Code MySQL:
    AND node.parent = 'Banana'

    Uh... no. again, parent shouldnt exist. You should have put node.title here instead.

    Code MySQL:
    ORDER BY parent1.lft;

    Fine. This says we're going to be retrieving records from Root To Node.

    Now look back at your first line. See how the query doesnt know what table 'fruit' is? You should have used parent1 in that line instead of fruit.

    Perhaps it would make more sense if i said "replace every instance of 'parent1' with 'chain'"?

  21. #21
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That makes sense, It means that the fruit table changed to node even if it is aliased after the SELECT statement. I was thinking about never changed, but it does makes sense that's why there is an error because it should be
    Code SQL:
    SELECT node.parent, node.lft, node.rgt


    It means that the SQL statement first look for the FROM clause before looking at what is inside the SELECT statement.

    Now I am curious to know what the order the Cursor takes when reading an sql statement. Now I know it looks for the FROM clause before the SELECT clause Where does the sequence follow after SELECT?

    great information I will test after I change the SELECT statement aliases.

  22. #22
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    the 'node' alias table only ever looks at 1 entry - the one you're targeting (In our example, the "Banana" node). The parent/chain alias table looks at the different parts of the chain that make up the path from the top of the tree to our target node.

  23. #23
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    As far as the 'cursor' in reading an SQL statement, i believe the appropriate response is "It depends."

    It will always start at the first word, but where it goes from there varies. ('does x table in the select clause exist? we need to look at our FROM clause for that, then go back to the select clause to check the next thing, etc etc.', or 'Theres a function here, so i need to go evaluate that first - but that function contains a function, so i need to go evaluate THAT first, then put it into the other function....') so the answer is rather convuluted there.

  24. #24
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with you to change to something else instead of fruits, we are going to convert this into a fruit market. Lol

    I have tested most of the sql statements and they return wonderful results but most of them seem to run from root to leaf, or from node to leaf, no the other way around going up. For instance taking the example of Electronics, what about if I search for Plasma which is under televisions. Well once I get plasma i would like to know what its parent category only, which is televisions. but in most of the examples and sql statements in mysql page you sent me a couple of post above they all refer relationships from parent to children, or node to leafs no leafs to nodes or root or children to parent. How can I know which category is Plasma ? and being able to display it, will the cursor or parser will have to read backwards ? plasma, televisions, Electronics instead of the normal current way Electronics, Televisions, plasma.

  25. #25
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Going Node-To-Root, the items are in order.

    SELECT parent.name,parent.lft,parent.rgt
    FROM food AS node,
    food AS parent
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.name = "Banana"
    ORDER BY parent.rgt;


    Code:
    Name,lft,rgt
    Banana,8,9 <-- This is what we looked for.
    Yellow,7,10  <-- This is Banana's parent.
    Fruit,2,11 <-- This is Yellow's parent.
    Food,1,18 <-- This is Fruit's parent. It doesnt have a parent (because we've run out of results), so it must be the root.
    I cant really give you the result for your electronics example because i dont know the structure of the tree. But it would be an equivilant set of data.


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
  •