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=nextedgenum, 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(level)*2),ItemName(nodeid)) 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/[EMAIL PROTECTED]
