MySQL ODBC Connector - display a hierarchic tree

Author

display a hierarchic tree

Jochen Kaechelin

2006-01-29, 8:23 pm

I have the following table:

mysql> select * from link_categories;
+----+-------+-------------+---------------+-----------+---------+
| id | level | category_id | category | parent_id | deleted |
+----+-------+-------------+---------------+-----------+---------+
| 1 | 1 | 1000 | Software | 0 | 0 |
| 2 | 1 | 2000 | Harware | 0 | 0 |
| 3 | 2 | 1001 | Virenscanner | 1000 | 0 |
| 4 | 2 | 1003 | Packprogramme | 1000 | 0 |
| 5 | 3 | 1004 | Linux | 1001 | 0 |
| 6 | 3 | 1005 | Windows | 1001 | 0 |
| 7 | 4 | 1006 | Windows XP | 1005 | 0 |
| 8 | 2 | 1007 | Sniffer | 1000 | 0 |
| 9 | 4 | 1008 | Debian Woody | 1004 | 0 |
| 10 | 1 | 100000 | Vermischtes | 0 | 0 |
+----+-------+-------------+---------------+-----------+---------+
10 rows in set (0.24 sec)

and I want to display a tree like:

Software
Virenscanner
Linux
Debian Woody
Windows
Windowsd XP
Packprogramm
Sniffer
Hardware
Vermischtes
....

Can someone give me hint how to build a query?

I run MySQL 4.1.x and 5.0.x and I use PHP.

Thanx.

--
fvgi242ss | wlanhacking.de

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Peter Brawley

2006-01-29, 8:23 pm

Jochen,

>...I want to display a tree like:
>
> Software
> Virenscanner
> Linux
> Debian Woody
> Windows
> Windowsd XP
> Packprogramm
> Sniffer
> Hardware
> Vermischtes
> ....

It looks more like a parts explosion than a tree (ie it seems to have
more than one root node). Perhaps you can hack this parts explosion
example into the shape you need ...

DROP TABLE IF EXISTS bom;
CREATE TABLE bom (
level SMALLINT,
nodeID SMALLINT,
parentID SMALLINT,
qty DECIMAL(10,2),
cost DECIMAL(10,2),
leftedge SMALLINT,
rightedge SMALLINT
);
DROP TABLE IF EXISTS edges;
CREATE TABLE edges LIKE tree;
DROP PROCEDURE IF EXISTS ShowComponents;
DELIMITER |

CREATE PROCEDURE ShowComponents( IN root INT )
BEGIN
DECLARE thischild, thisparent, rows, maxrightedge INT DEFAULT 0;
DECLARE thislevel, nextedgenum INT DEFAULT 1;
DECLARE thisqty, thiscost DECIMAL(10,2);
TRUNCATE edges;
TRUNCATE bom;
INSERT INTO edges
SELECT childID,parentID FROM assemblies WHERE assemblyRoot = root;
SET maxrightedge = 2 * (1 + (SELECT COUNT(*) FROM edges));
INSERT INTO bom
VALUES( thislevel, root, 0, 0, 0, nextedgenum, maxrightedge );
SET nextedgenum = nextedgenum + 1;
WHILE nextedgenum < maxrightedge DO
-- How many children of this node remain in the edges table?
SET rows = (
SELECT COUNT(*)
FROM bom AS s
INNER JOIN edges AS t ON s.nodeID=t.parentID AND s.level=thislevel
);
IF rows > 0 THEN
-- There is at least one child edge.
-- Compute qty and cost, insert into bom, delete from edges.
BEGIN
-- Alas MySQL nulls MIN(t.childid) when we combine the next two
queries
SET thischild = (
SELECT MIN(t.childID)
FROM bom AS s
INNER JOIN edges AS t ON s.nodeID=t.parentID AND s.level=thislevel
);
SET thisparent = (
SELECT DISTINCT t.parentID
FROM bom AS s
INNER JOIN edges AS t ON s.nodeID=t.parentID AND s.level=thislevel
);
SET thisqty = (
SELECT quantity FROM assemblies
WHERE assemblyroot = root
AND childID = thischild
AND parentID = thisparent
);
SET thiscost = (
SELECT a.assemblycost + (thisqty * (i.purchasecost +
i.assemblycost ))
FROM assemblies AS a
INNER JOIN items AS i ON a.childID = i.itemID
WHERE assemblyroot = root
AND a.parentID = thisparent
AND a.childID = thischild
);
INSERT INTO bom
VALUES(thislevel+1, thischild, thisparent, thisqty, thiscost,
nextedgenum, NULL);
DELETE FROM edges
WHERE childID = thischild AND parentID=thisparent;

SET thislevel = thislevel + 1;
SET nextedgenum = nextedgenum + 1;
END;
ELSE
BEGIN
-- Set rightedge, remove item from edges
UPDATE bom
SET rightedge=nextedgenu
m, level = -level
WHERE level = thislevel;
SET thislevel = thislevel - 1;
SET nextedgenum = nextedgenum + 1;
END;
END IF;
END WHILE;
SET rows := ( SELECT COUNT(*) FROM edges );
IF rows > 0 THEN
SELECT 'Orphaned rows remain';
ELSE
-- Percolate qty values up the graph
UPDATE bom AS c
INNER JOIN bom AS p
ON p.leftedge < c.leftedge
AND p.rightedge > c.rightedge
AND p.level = c.level + 1
AND p.qty <> 1
SET c.qty = c.qty * p.qty,
c.cost = c.cost * p.qty;
-- Total
SET thiscost = (SELECT SUM(qty) FROM bom);
UPDATE bom
SET qty = 1, cost = thiscost
WHERE nodeID = root;
-- Show the result
SELECT
CONCAT(Space(Abs(lev
el)*2),ItemName(node
id)) AS Item,
ROUND(qty,2) AS Qty,
ROUND(cost, 2) AS Cost
FROM bom
ORDER BY leftedge;
END IF;
END;
|
DELIMITER ;

PB

-----

Jochen Kaechelin wrote:
> I have the following table:
>
> mysql> select * from link_categories;
> +----+-------+-------------+---------------+-----------+---------+
> | id | level | category_id | category | parent_id | deleted |
> +----+-------+-------------+---------------+-----------+---------+
> | 1 | 1 | 1000 | Software | 0 | 0 |
> | 2 | 1 | 2000 | Harware | 0 | 0 |
> | 3 | 2 | 1001 | Virenscanner | 1000 | 0 |
> | 4 | 2 | 1003 | Packprogramme | 1000 | 0 |
> | 5 | 3 | 1004 | Linux | 1001 | 0 |
> | 6 | 3 | 1005 | Windows | 1001 | 0 |
> | 7 | 4 | 1006 | Windows XP | 1005 | 0 |
> | 8 | 2 | 1007 | Sniffer | 1000 | 0 |
> | 9 | 4 | 1008 | Debian Woody | 1004 | 0 |
> | 10 | 1 | 100000 | Vermischtes | 0 | 0 |
> +----+-------+-------------+---------------+-----------+---------+
> 10 rows in set (0.24 sec)
>
> and I want to display a tree like:
>
> Software
> Virenscanner
> Linux
> Debian Woody
> Windows
> Windowsd XP
> Packprogramm
> Sniffer
> Hardware
> Vermischtes
> ....
>
> Can someone give me hint how to build a query?
>
> I run MySQL 4.1.x and 5.0.x and I use PHP.
>
> Thanx.
>
>

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 1/27/2006

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

mysql@karsites.net

2006-01-29, 8:23 pm

Hi Jochen.

An alternative approach could be to pull all the values out
of the database using select * from ..., and then build the
tree-structure in your application logic. Insert the
relevant values returned from mysql in the appropriate
places of the tree-structure in the app code.

Keith

In theory, theory and practice are the same;
In practice they are not.

On Sun, 29 Jan 2006, Jochen Kaechelin wrote:

> To: mysql@lists.mysql.com
> From: Jochen Kaechelin < fvgi242ss@wlanhackin
g.de>
> Subject: display a hierarchic tree
>
> I have the following table:
>
> mysql> select * from link_categories;
> +----+-------+-------------+---------------+-----------+---------+
> | id | level | category_id | category | parent_id | deleted |
> +----+-------+-------------+---------------+-----------+---------+
> | 1 | 1 | 1000 | Software | 0 | 0 |
> | 2 | 1 | 2000 | Harware | 0 | 0 |
> | 3 | 2 | 1001 | Virenscanner | 1000 | 0 |
> | 4 | 2 | 1003 | Packprogramme | 1000 | 0 |
> | 5 | 3 | 1004 | Linux | 1001 | 0 |
> | 6 | 3 | 1005 | Windows | 1001 | 0 |
> | 7 | 4 | 1006 | Windows XP | 1005 | 0 |
> | 8 | 2 | 1007 | Sniffer | 1000 | 0 |
> | 9 | 4 | 1008 | Debian Woody | 1004 | 0 |
> | 10 | 1 | 100000 | Vermischtes | 0 | 0 |
> +----+-------+-------------+---------------+-----------+---------+
> 10 rows in set (0.24 sec)
>
> and I want to display a tree like:
>
> Software
> Virenscanner
> Linux
> Debian Woody
> Windows
> Windowsd XP
> Packprogramm
> Sniffer
> Hardware
> Vermischtes
> ....
>
> Can someone give me hint how to build a query?
>
> I run MySQL 4.1.x and 5.0.x and I use PHP.
>
> Thanx.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Gleb Paharenko

2006-01-30, 7:23 am

Hello.

This is not an exact answer on your question, however it could be
interesting for you:
http://dev.mysql.com/tech-resources...hical-data.html

Jochen Kaechelin wrote:
> I have the following table:
>
> mysql> select * from link_categories;
> +----+-------+-------------+---------------+-----------+---------+
> | id | level | category_id | category | parent_id | deleted |
> +----+-------+-------------+---------------+-----------+---------+
> | 1 | 1 | 1000 | Software | 0 | 0 |
> | 2 | 1 | 2000 | Harware | 0 | 0 |
> | 3 | 2 | 1001 | Virenscanner | 1000 | 0 |
> | 4 | 2 | 1003 | Packprogramme | 1000 | 0 |
> | 5 | 3 | 1004 | Linux | 1001 | 0 |
> | 6 | 3 | 1005 | Windows | 1001 | 0 |
> | 7 | 4 | 1006 | Windows XP | 1005 | 0 |
> | 8 | 2 | 1007 | Sniffer | 1000 | 0 |
> | 9 | 4 | 1008 | Debian Woody | 1004 | 0 |
> | 10 | 1 | 100000 | Vermischtes | 0 | 0 |
> +----+-------+-------------+---------------+-----------+---------+
> 10 rows in set (0.24 sec)
>
> and I want to display a tree like:
>
> Software
> Virenscanner
> Linux
> Debian Woody
> Windows
> Windowsd XP
> Packprogramm
> Sniffer
> Hardware
> Vermischtes
> ....
>
> Can someone give me hint how to build a query?
>
> I run MySQL 4.1.x and 5.0.x and I use PHP.
>
> Thanx.
>

--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Pete Harlan

2006-01-30, 1:23 pm

On Mon, Jan 30, 2006 at 03:04:20PM +0200, Gleb Paharenko wrote:
> Hello.
>
> This is not an exact answer on your question, however it could be
> interesting for you:
> http://dev.mysql.com/tech-resources...hical-data.html

This is a good article. One thing it leaves out of the discussion of
the adjacency-lists model is the use of an auxilliary transitive-
closure table that makes querying the adjacency-list data efficient
and straightforward. (The Celko treatment of the subject referred to
in the article covers this.) For some applications where the data set
is small and static enough, this can be a good solution too in the
right situation, and one that avoids the mathematical hackery of
nested sets.

--Pete

> Jochen Kaechelin wrote:
>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Gleb Paharenko
> / /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
> /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
> <___/ www.mysql.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql? unsub...le
ct.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links