Stored procedure nested cursor loop in mysql not executing all results

Question

I am trying to use nested loop outer one to access all categories then using that taxID I am querying posts from that tax so that I can assign them ranking as per their category placement.

But I am facing problem that the inner loop "RankingLoop " is only accessing all post results of one taxID then terminate loop and don't loop for other category post if I use Repeat in place of RankingLoop then first record of each taxID loop become accessible. can any body please help to explain problem in my code?

Thank you for help in advance.

DELIMITER $$
CREATE PROCEDURE  Catranking__()
    Begin
        Declare txID INT; Declare txmaxcount INT; 
        Declare txCount CURSOR FOR SELECT max(term_taxonomy_id) FROM `wp_term_taxonomy` where taxonomy = 'category' and count > 0;
        Declare taxonomyIDS CURSOR FOR SELECT term_taxonomy_id FROM `wp_term_taxonomy` where taxonomy = 'category' and count > 0;
        open taxonomyIDS; open txCount;
        FETCH txCount into txmaxcount;
        loop_label: LOOP 
            FETCH taxonomyIDS into txID;
            if txID < (txmaxcount+1) then set @rank=0;
                categorywisePostSorting: begin
                    DECLARE Vranking INT;
                    DECLARE VPOSTID INT;             
                    Declare RankingArray CURSOR FOR select @rank:=@rank+1 as ranking,  POSTID FROM   RankingView WHERE  term_id = txID order by claimedProducts desc, commentcount desc, pageviews desc;                        
                    open RankingArray;
                    RankingLoop : Loop 
                        FETCH RankingArray into Vranking, VPOSTID;
                        select VPOSTID;
                    end Loop RankingLoop;
                    close RankingArray; 
                END categorywisePostSorting;
            end if;
        END LOOP loop_label;
        close taxonomyIDS; close txCount;
    END$$
DELIMITER ;

James · Accepted Answer · 2019-07-31 10:46:28Z

Since there are unknown logic, modified as below,

DELIMITER $$
CREATE PROCEDURE  Catranking__()
BEGIN
        DECLARE txmaxcount INT;
        DECLARE txID INT; 
        DECLARE done1 BOOLEAN DEFAULT FALSE;
        # assuming `count`  is column name so instead of cursor we can use a local var
        #Declare txCount CURSOR FOR SELECT max(term_taxonomy_id) FROM `wp_term_taxonomy` where taxonomy = 'category' and count > 0; -- "count" is illegal here(whether its column name or count(*))... so check from ur side
        DECLARE taxonomyIDS CURSOR FOR SELECT term_taxonomy_id FROM `wp_term_taxonomy` WHERE taxonomy = 'category' AND count > 0; -- same count issue

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1=TRUE;

SELECT 
    MAX(term_taxonomy_id)
INTO txmaxcount FROM
    `wp_term_taxonomy`
WHERE
    taxonomy = 'category' AND `count` > 0;

        OPEN taxonomyIDS;
        TAXONOMYIDS_LOOP: LOOP

            FETCH taxonomyIDS INTO txID;

            IF done1 THEN

                LEAVE TAXONOMYIDS_LOOP;

            END IF;

           IF txID < (txmaxcount+1) THEN 

                SET @rank=0;

                CATEGORYWISEPOSTSORTING: BEGIN

                    DECLARE Vranking INT; -- why this var? hasn't used in the code
                    DECLARE VPOSTID INT; 
                    DECLARE done2 BOOLEAN DEFAULT FALSE;

                    DECLARE RankingArray CURSOR FOR 
                    SELECT @rank:=@rank+1 AS ranking,  POSTID FROM  RankingView WHERE  term_id = txID ORDER BY claimedProducts DESC, commentcount DESC, pageviews DESC;                        

                    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2=TRUE;

                    OPEN RankingArray;
                    RANKINGLOOP : LOOP 

                        FETCH RankingArray INTO Vranking, VPOSTID;

                        IF done2 THEN

                            LEAVE RANKINGLOOP;

                        END IF;

SELECT VPOSTID; 

                    END LOOP RANKINGLOOP;
                    CLOSE RankingArray;
                    SET done2= FALSE;
                END CATEGORYWISEPOSTSORTING;

            END IF;


        END LOOP TAXONOMYIDS_LOOP;
        CLOSE taxonomyIDS;
        SET done=FALSE;

END$$

DELIMITER ;

Collectives™ on Stack Overflow

Stored procedure nested cursor loop in mysql not executing all results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related