Archive for the ‘Database’ Category





I always wondered how to write a single query, which can retrieve all parents (nested) of any particular category

Table structure

for example if I provide idCategory=8 which is  “black”, it should return some thing like

6,4,2,1 which is (Jeans->Trouser->Mens Wear-> Clothes)

but unfortunately I am unable to write such query, so I decided to write a stored procedure or a function that full fill the purpose. both options were open, so I started with stored procedure(as debugging is easy compared to function) and finally converted it to function to full fill my requirements.

Note: There are 2 ways to do it, first one is to use Recursive one, which is easy to code/write and second one is iterative way, so I decided the 2nd method.

This stored procedure takes 2 parameters
1st is the id of the category
2nd is the maximum depth level, there are chances that we might have a bug or some thing in our code and procedure get stuck in infinite loop or there is also possibility there is a circular loop of categories. (like A is parent of B, B is parent of C, and C is parent of A)  A->B->C->A

so 2nd parameter will check the maximum allowed iteration.

here is procedure

DROP PROCEDURE IF EXISTS getAllParentCategories;
CREATE PROCEDURE `getAllParentCategories`( IN idCat int, IN intMaxDepth int)
BEGIN
DECLARE chrProcessed TEXT;
DECLARE quit INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE Level INT DEFAULT 0;
DECLARE idFetchedCategory INT;
DECLARE chrSameLevelParents VARCHAR(255);
DECLARE chrFullReturn VARCHAR(255);
DECLARE cur1 CURSOR FOR SELECT idParentCategory FROM tbl_categories WHERE idCategory IN (@param);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET chrFullReturn = '';
SET @param = idCat;
set chrProcessed = concat('|',idCat, '|');
myloop:LOOP
      IF quit = 1 THEN
        leave myloop;
      END IF;

      OPEN cur1;

      SET chrSameLevelParents = '';
      FETCH cur1 INTO idFetchedCategory;
      while(not done) do
          SET Level = Level + 1;
          IF idFetchedCategory > 0 THEN

                if NOT INSTR(chrProcessed,concat('|',idFetchedCategory, '|')) > 0 THEN
                      if CHAR_LENGTH(chrSameLevelParents) > 0 then
                        set chrSameLevelParents = concat( idFetchedCategory, ',', chrSameLevelParents );
                      else
                        set chrSameLevelParents = idFetchedCategory;
                      end if;

                      set chrProcessed = concat('|',idFetchedCategory, '|', chrProcessed );
                 end if;

           END IF;
           FETCH cur1 INTO idFetchedCategory;

      end while;
      CLOSE cur1;

      IF Level > intMaxDepth THEN SET done =1; SET quit = 1; END IF;

      if CHAR_LENGTH(chrSameLevelParents) > 0 THEN
        if CHAR_LENGTH(chrFullReturn) > 0 THEN
            set chrFullReturn = concat( chrFullReturn, ',', chrSameLevelParents );
        ELSE
            set chrFullReturn = chrSameLevelParents;
        END IF;

        SET @param = chrSameLevelParents;
        SET chrSameLevelParents = '';
        SET done = 0;

      ELSE
        SET quit = 1;
      END IF;
END LOOP;

SELECT Level,chrFullReturn;

END;

Now we will call this function as follows

call getAllParentCategories(8 ,100);

8 is idCategory of “Black” and 100 is max depth.
result set will look like as follows

Level chrFullReturn
5 6,4,2,1

Here problem is chrFullReturn is text value and will not produce any use full result however if this procedure is called from any programming language then we can use this result in another query to get categories name.

or we can replace following query with prepare statement to retrieve proper result set.

SELECT Level,chrFullReturn;

replace with

SET @strQuery = concat('SELECT idCategory, CategoryName FROM tbl_categories WHERE idCategory IN (',chrFullReturn,')'); PREPARE stmt1 FROM @strQuery;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

Now it will return proper and useful result set.

idCategory CategoryName
1 Clothes
2 Mens Wear
4 Trouser
6 Jeans




I always ask my friend(Faisal Mahmood) about this query and next time I forget the query, so he advised me to write it down and do not ask him again, so I decided to write it for everyone ;).
In order to list all stored procedure/functions in any database use following MySQL command

select * from information_schema.ROUTINES
where routine_schema = 'your_db_name'

For only names use following command.

select routine_name from information_schema.ROUTINES
where routine_schema = 'your_db_name'