MySql using a while loop inside a function

Question

Given a number, how can I loop through a set of numbers based on that number, inside a MySql function?

To get a clearer picture, pls see my question where I asked how something like this could be done in php. Question here.

I'll be doing select myfunction(thenumber). Based on that number, I got to loop.

As of now,

If thenumber = 1 then loop backward thru 1,4,7 only
If thenumber = 2 then loop thru 3
If thenumber = 3 then loop thru 10

Here's the function I've been fiddling with.

CREATE DEFINER=`root`@`localhost` FUNCTION `whileloop`(`danum` VARCHAR(2050))
    RETURNS varchar(1500)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
DECLARE x  INT;
DECLARE str  VARCHAR(255);
SET x = danum;
SET str =  '';
#If x = 1, while = 1, 4, 7////////
WHILE x  >= 1 DO //I'm stuck here
SET  str = CONCAT(str,x,',');
SET  x = x - 1; 
END WHILE;
RETURN str;
END

How can I do inside a MySql function, what I did php. In php, we stored things in an array. How can we do this here?

Edit

Result

What I'm trying to get out in the end are links.

So if `thenumber = 1`
`<a href=foo.php?id=1> <a href=foo.php?id=4> <a href=foo.php?id=7>` //3 Links Output in a single row
If `thenumber = 2`
`<a href=foo.php?id=3>` //Same as above. Output in a single row.
If `thenumber = 10`
`<a href=foo.php?id=10>`

Mysql functions return only a scalar value. Therefore you can't do exactly what you've done in php. Explain more clearly what you expect to get from your function? If you pass 1 as an argument do you simply expecting a string '1, 4, 7' to be returned? — peterm
– peterm, Commented Jun 6, 2013 at 7:23
No. I want the while loop to loop through numbers 1, 4, 7. The result is a link like this (example for 1): <a href=id=1> <a href=id=4> <a href=id=7> — Norman
– Norman, Commented Jun 6, 2013 at 7:28
You want these links as one varchar value or you expect them to be rows? — peterm
– peterm, Commented Jun 6, 2013 at 7:31
I've edited my question. For 1 it's going be 3 links in one single row output. For the rest, it's only a single link. — Norman
– Norman, Commented Jun 6, 2013 at 7:34

peterm · Accepted Answer · 2013-06-06 07:53:57Z

1

Are you looking for this?

CREATE FUNCTION mylink(n INT)
RETURNS VARCHAR(512) DETERMINISTIC
RETURN CONCAT('<a href=foo.php?id=',n,'>');

CREATE FUNCTION myfunction(n INT)
RETURNS VARCHAR(512) DETERMINISTIC
RETURN CASE n
    WHEN 1 THEN CONCAT(mylink(1), mylink(2), mylink(7))
    WHEN 2 THEN mylink(3)
    WHEN 3 THEN mylink(10)
END;

To use it

SELECT myfunction(n) links
FROM 
(
  SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3
) a

Output:

|                                                           LINKS |
-------------------------------------------------------------------
| <a href=foo.php?id=1><a href=foo.php?id=2><a href=foo.php?id=7> |
|                                           <a href=foo.php?id=3> |
|                                          <a href=foo.php?id=10> |

Here is SQLFiddle demo

edited Jun 6, 2013 at 7:53

answered Jun 6, 2013 at 7:38

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Norman Over a year ago

Close, very close. I'm fiddling why even 3 and 10 return 3 links.

peterm Over a year ago

@Norman Sorry it was a typo in both functions. Updated the answer and sqlfiddle.

Norman Over a year ago

Works very well. I'll add all the other cases in there :)

peterm Over a year ago

@Norman I'm glad it helped :) If there are lots of those id combinations you might consider using a simple table for that and then use GROUP_CONCAT() and GROUP BY to build your value.

Collectives™ on Stack Overflow

MySql using a while loop inside a function

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related