Re: Recursive SQL Function / Drill down in relational MySQL Tables

[Andrew Stilliard]

Ok, fixed that issue by modifying my mysql_connect function in the php.
So now im using this generic tree function, which is a little long winded i gues but does the job.
Well, it will when i convert it over to my categories scenario, but for now im foloing the steps on the site.
I now need to apply this to be easily used in a SELECT Statement, so to do this i created a function to call it.
The problem is, when i run the function i get:
Error (#1336): Dynamic SQL is not allowed in stored function or trigger

* SELECT statement *

SELECT AutoCountGenericTree(12);

* Simple function for selecting quickly *

CREATE FUNCTION AutoCountGenericTree(ancestorID INT(20))
RETURNS BOOLEAN 
BEGIN 
  DECLARE countResults INT;
  CALL GenericTree('familytree','childID','parentID', ancestorID);
  SET countResults = (SELECT COUNT(*) FROM subtree s);
  RETURN countResults;
END ;

* Generic tree function: *

CREATE PROCEDURE GenericTree(
  edgeTable CHAR(64), edgeIDcol CHAR(64), edgeParentIDcol CHAR(64), ancestorID INT
)
BEGIN
  DECLARE r INT DEFAULT 0;
  DROP TABLE IF EXISTS subtree;
  SET @sql = Concat( 'CREATE TABLE subtree ENGINE=MyISAM SELECT ',
                     edgeIDcol,' AS childID, ',
                     edgeParentIDcol, ' AS parentID,',
                     '0 AS level FROM ',
                     edgeTable, ' WHERE ', edgeParentIDcol, '=', ancestorID );
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DROP PREPARE stmt;
  ALTER TABLE subtree ADD PRIMARY KEY(childID,parentID);
  REPEAT
    SET @sql = Concat( 'INSERT IGNORE INTO subtree SELECT a.', edgeIDcol,
                       ',a.',edgeparentIDcol, ',b.level+1 FROM ',
                       edgeTable, ' AS a JOIN subtree AS b ON a.',edgeParentIDcol, '=b.childID' );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    SET r=Row_Count();  -- save row_count() result before DROP PREPARE loses the value
    DROP PREPARE stmt;
  UNTIL r < 1 END REPEAT;
END ;

Edited 2 time(s). Last edit at 06/15/2010 07:14AM by Andrew Stilliard.