CODEWITHSUNDEEP
mysql
troy
troy

Reputation: 1027

only single record is returned by mysql loop query

I am using mysql loop concept and making my query to execute multiple times expecting a result of multiple rows..

My query is:

DELIMITER $$    

CREATE FUNCTION getActiveLife(endTime INT) RETURNS INT
BEGIN
    DECLARE x INT;
    DECLARE y INT;
    DECLARE c1 INT;
    SET x = endTime;
    SET y = 4;
    sloop:LOOP
       SELECT count(*) INTO c1 FROM `accounts` where (`life` != 'EXPIRED') and (`lifeEnd` <= x );
       IF y > 0 THEN
          SET x = x-3600;
          SET y = y - 1;
       ELSE
           LEAVE sloop;
       END IF;  
    END LOOP;
    RETURN c1;
END  $$
DELIMITER ;

I am actually trying to get the results from now to last 4 hours.... It has executed successfully but when i called it as below

SELECT getActiveLife(1368193391)...

It gives me one record with value as 1... Doesnt know what that means ....

Because when i execute 

SELECT count(*) INTO c1 FROM `accounts` where (`life` != 'EXPIRED') and (`lifeEnd` <= 1368193391 );

It gives me count as 4...

so i am expecting something like 4 for loop1 ,2 for loop2 and so on in a table format.

If I am going wrong or missing something just correct me....

I am expecting something like:

  Result:
    4  - row1
    2  - row2
    1  - row3

In any form

Thank You.

Upvotes: 4

Views: 710

Answers (2)

Mike Brant
Mike Brant

Reputation: 71422

I believe this query would do what you are looking for:

SELECT
  SUM(1) AS `endtime_count`,
  SUM(IF(`lifeEnd` <= (? - (1 * 3600)), 1, 0)) AS `endtime_minus_1hour_count`,
  SUM(IF(`lifeEnd` <= (? - (2 * 3600)), 1, 0)) AS `endtime_minus_2hour_count`,
  SUM(IF(`lifeEnd` <= (? - (3 * 3600)), 1, 0)) AS `endtime_minus_3hour_count`,
  SUM(IF(`lifeEnd` <= (? - (4 * 3600)), 1, 0)) AS `endtime_minus_4hour_count`
FROM `accounts`
WHERE
  `life` <> 'EXPIRED'
  AND `lifeEnd` <= ?

Here we just use IF statements to sum up the counts you are looking for. Note that ? represents the end time value you are querying against.

This would return a single row with all your values.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271171

Your function is only returning one value (return c1). It is returning the last value from the loop.

In MySQL, functions do not returns tables. If you want four rows, then you need to execute a query. Or, you could concatenate all the values together into a string, and return that. What do you want this function to do?

Upvotes: 1

Related Questions