#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    41
    Rep Power
    7

    Looping thru all the records inside a MySql event


    Can you please help me with looping inside a MySql event?

    I did this event in MySql, but I guess I'm missing something. It works if I supply a limit = 1, which means it isn't looping thru the thing. How can I do that?

    <b>This works. (For only one record)</b>
    Code:
        BEGIN
        
        DECLARE c VARCHAR(2);
        DECLARE vElm INT(10);
        DECLARE vType TINYINT(1);
        
        select distinct(country) into c from votes limit 1;
        
        select votedElm, voteType into vElm, vType from votes where country = c limit 1;
        
        IF vType = 0 THEN
        update likes set votes = votes+1 where id = vElm and country = c and type = 10;
        ELSE IF vType = 1
        update likes set votes = votes-1 where id = vElm and country = c and type = 10;
        END IF;  
        
        END
    <b>This will not. Note: The limit 1 has been removed</b>
    Code:
        BEGIN
        
        DECLARE c VARCHAR(2);
        DECLARE vElm INT(10);
        DECLARE vType TINYINT(1);
        
        select distinct(country) into c from votes;
        //Need to loop here. This returns multiple records.
        
        select votedElm, voteType into vElm, vType from votes where country = c; 
        //Need to loop here too. This too returns multiple records.
        
        IF vType = 0 THEN
        update likes set votes = votes+1 where id = vElm and country = c and type = 10;
        ELSE IF vType = 1
        update likes set votes = votes-1 where id = vElm and country = c and type = 10;
        END IF;  
        
        END
    How is looping done inside a MySql event?
    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    my advice is to think about what you want to achieve against all rows at once, because looping is not something you should be doing in sql

    also, selecting distinct countries into a variable is gonna fail if there is more than one country
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    41
    Rep Power
    7
    Originally Posted by r937
    my advice is to think about what you want to achieve against all rows at once, because looping is not something you should be doing in sql

    also, selecting distinct countries into a variable is gonna fail if there is more than one country
    It'll fail even when it loops? I thought that it'll change the next time it loops. It wont?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by Nordy
    It wont?
    that's right, it won't

    each sql statement inside the BEGIN...END block is executed just once

    like i said, think about what you want to accomplish (updating the likes table) by handling all the votes at once, instead of looping
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo