Scripts

MySQL - Recursieve stored function

Door kees Schepers -02-04-2009 00:00 -8.260 views

Ik moest dit voor een vriend van mij maken, en toen bedacht ik me dit is ook wellicht handig voor mezelf, en misschien ook leuk voor de mensen op PHPhulp om een voorbeeld te zien met wat je kunt met stored routines in MySQL (en andere DBMS). Het is dus geen compleet werkend script, ik zelf heb hier weleens wat kritiek op dus mochten jullie vinden dat het niets toevoegt aan de libary dan mag hij verwijderd worden of doe ik dat zelf wel. Maargoed, deze functie zorgt ervoor dat een categorienaam streepjes als prefix krijgt aan de hand van de hoeveelheid parents hij heeft. Stel je hebt bijvoorbeeld een CMS (Content Management Systeem) en bij het pagina toevoegen wil je aangeven onder welk content item het huidige gaat vallen. Dan bouw je een dropdown menu en om de boomstructuur te visualiseren wil je streepjes voor elke categorie die een indicatie geeft van de niveau diepte. Normaal haal je wellicht alle categorieën op, en maak je een recursieve functie in PHP etc. Nu kun je MySQL al het werk laten doen ;-) Naslag werk wat ik erbij gebruikt heb: http://www.learn-mysql-tutorial.com/Programming.cfm http://dev.mysql.com/doc/refman/5.1/en/stored-routines.html Zie voorbeeld voor aanroep.

Download alle bestanden als ZIP

mysql-recursieve-stored-function

De tabel:

[code]
CREATE TABLE  categories (
  `categoryid` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  `parentid` int(10) unsigned default NULL,
  PRIMARY KEY  (`categoryid`),
  KEY `FK_categories_1` (`parentid`),
  CONSTRAINT `FK_categories_1` FOREIGN KEY (`parentid`) REFERENCES `categories` (`categoryid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[/code]

De stored function:
[code]
DELIMITER $$

DROP FUNCTION IF EXISTS catname $$
CREATE FUNCTION catname(arg_categoryid INT) RETURNS varchar(100) CHARSET utf8
BEGIN
  DECLARE currentCategoryId INT;
  DECLARE categoryname VARCHAR(100);
  DECLARE padder VARCHAR(20) DEFAULT '';

  SET currentCategoryId = arg_categoryid;
  SELECT name INTO categoryname FROM categories WHERE categoryid = arg_categoryid;

  catloop : LOOP
    SELECT parentid INTO currentCategoryid FROM categories WHERE categoryid = currentCategoryId;

    IF currentCategoryId IS NULL THEN LEAVE catloop; END IF;

    SET padder = CONCAT(padder,'-');
  END LOOP catloop;


  RETURN CONCAT(padder,' ',categoryname);
END $$

DELIMITER ;
[/code]

Voorbeeld data
[code]
INSERT INTO `categories` (`categoryid`, `name`, `parentid`) VALUES (3, 'level 1 - test', NULL),
(4, 'level 1 - test 2', NULL),
(5, 'level 1 - test 3', NULL),
(6, 'level 2 - test', 4),
(7, 'level 2 - test', 4),
(8, 'level 3 - test', 6),
(9, 'level 3 - test 2', 6);
[/code]