I have a table with 3 columns: ID, PARENT_ID, NAME PARENT_ID has a foreign key relationship with ID in the same table. This table is modeling a hierarchy. Sometimes the ID of a record will change. I want to be able to update a record's ID, then update the dependent records' PARENT_ID to point to the new ID. The problem is, when I ...
I am storing hierarchical data in mysql in the form of a nested set. myTable id, title, lft, rgt I use the following series of sql statements to insert a new node: SELECT @myLeft := lft FROM myTable WHERE ID = $id; UPDATE myTable SET rgt = rgt + 2 WHERE rgt > @myLeft; UPDATE myTable SET lft = lft + 2 WHERE lft > @myL...
I'm constantly running into issues with having hierarchical data stored in an RDBMS. To me, it seems like an indicator that it's the wrong tool for the job. The "job" is to store a hierarchical survey and the results from multiple respondents for a given date range. The survey questions may slightly vary from one date range to the next. ...
For example, I have this table: CREATE TABLE perarea ( id_area INT primary key, nombre VARCHAR2(200), id_areapadre INT references perarea(id_area) ); Instead of showing: 1 IT null 2 Recursos Humanos null 3 Contabilidad 2 4 Legal 2 I want: 1 IT 2 Recursos Humanos 3 Contabilidad Recursos Humanos 4 Leg...
Hi I am stuck with a seemingly simple problem. I want to read a text hierarchy written somewhat like c# code eg; Common { MyClass1 { Method1 { "Helloworld"; "GoodBye"; } Method2 { "SayGoodMorning"; } } MyClass2 { Method3 { "M3"; } } } Consider common a names...
Our solution needs us to work in hierarchies of regions which are as follows. STATE | DISTRICT | TALUK / \ / \ HOBLI PANCHAYAT \ / \ / ...
Hi, I have to store a tree in a database, so what is the best way to do this? Show the method you use and name its pros and cons. (I am using SQL Server 2005) ...
Let's say we have a table with user comments. First-level comments have a reference to an article they are attached to. Deeper-level comments do not have this reference by design but they have a reference to it's parent comment. For this database structure - what would be the most efficient way to fetch all comments for a given article...
I have an array with tree data (by parent id). I want to convert it to multidimensional array. What is the best way to achieve that? Is there any short function for that? Source array: $source = array( '0' => array( 'Menu' => array( 'id' => 45 'name' => 'Home' ...
Hi! I need to query the opengeodb to figure out the 'Bundesland' to a given location. You can find information to the DB schema at DB Schema. Sorry there is only German documentation text, but the graphical schema on this page is in english. To make it possible that the opengeodb is extended later the data is stored in a special way, w...
hello i hoping to create a recursive function which i don't have an idea yet this is my code to fetch category from database <?php $sql = mysql_query("SELECT * FROM categories WHERE category_parent = '1' ORDER BY lft ASC"); while($row = mysql_fetch_array($sql)) { echo "<li><a href='/{$row['category_safe_name']}/'>{$row['catego...
Please do not point me to an article on how to create tree structures, or CTEs in SQL I've read plenty!!! I think this may not be so tough for the t-sql at heart but it is definitely tough for me :). Here is the situation, I have to create a report that looks like this: This works great when the parameter to my stored procedure (SQL...
Hello guys, I would like to write a recursive PHP function to retrive all the children for the specified category. I tried the one described here but it didn't output what I have expected. My categories table looks like this: CREATE TABLE `categories` ( `category_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `category_name` var...
I have a MySQL singleton class written in PHP. Its code is listed below: class Database { private $_result = NULL; private $_link = NULL; private $_config = array(); private static $_instance = NULL; // Return singleton instance of MySQL class public static function getInstance(array $config = array()) { ...
i am using MySQL with PHP & Doctrine 2. my question is assuming i am using Modified Preorder Tree Traversal is there a way i can retrieve only immediate children? ...
As it stands now, the CLR UDTs including HierarchyID aren't supported in Entity Framework 4. HierarchyID.ToString() is useful, but breaks down once any item has 10+ siblings (the basic structure is /3/4/12/ or /3/4/2/ so the 12th node will sort before the 2nd node). A little more about potential options: Bring back hierarchyID as ...
Hi I have an "Employee" table with an "EmployeeID" column and a column representing Employee's Boss (BossID) which in turn is an employee in the "Employee" table. How can I trace the hierarchy from a given "EmployeeID" to the top most Boss. I do not want a self join approach in this, also I am using SQL Server 2005. Thank you Man...
This question is a follow-up of this older one, and it's more of a confirmation than an open question. My ViewModel instance has a private instance of the Model, _modelInst. The ViewModel has exclusive access to the Model's data during editing (so the Model doesn't need to implement INotifyPropertyChanged). Now there are three ways I c...
Hi I have a simple table with persons, but there is a additional field witch holds information (person id) who is a father/mother of that person, so the 2 dimensional table can hold a familly tree the table is id first_name last_name salary spouse_id father_id mother_id sex 100 Steven King 26400 101 (null) (null) m 101 Neena Koc...
I am looking for a datagrid which can show hierarchical information. I don't need it to be ajax driven ... can simply even be from static data embedded in HTML. ...