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.

you lost me right there

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

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.

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

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.


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

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.

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.

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:

$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.

(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.)

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


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:

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.

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

(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?


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:

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;

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.

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.

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.

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?

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:

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;


which is not what i think you want.

i am doing this in the editor

include "scripts/connect_to_mysql.php"; 

$sql2= mysql_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>0 )
		  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

-- Table structure for table `fruit`

  `parent` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `lft` int(11) NOT NULL,
  `rgt` int(11) NOT NULL,
  PRIMARY KEY (`id`)

-- 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?

O…kay. We’ve got a whole buncha issues here. (Now I want a banana… should have chosen a non-food example…:lol:)

Line by line, then.

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.

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.

fruit AS parent1

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

 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.

AND node.parent = 'Banana'  

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

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’”?