asked on

MySQL Function with loop and concat

Hi Experts,

I'm trying get all the product sizes into a column of a select concatenated by "/".
I'm trying to do this using a function that i called getproduct_sizes.
Something like:

SELECT tb.id,tb.ref,tb.description,getproduct_sizes(tb.id) sizes FROM product tb
Result:
id | ref | description | sizes
1 | ref1 | description1 | XS / XL
2 | ref1 | description1 | XL / XXL
3 | ref1 | description1 | XS / XXL / XXXL

Open in new window

I tried to create the function like this, but gives me allways NULL (The query of the table tb_productsize returns rows):

CREATE DEFINER = 'root'@'%'
FUNCTION getproduct_sizes(productid int)
  RETURNS text CHARSET utf8 COLLATE utf8_unicode_ci
  DETERMINISTIC
BEGIN
  DECLARE size_list varchar(4000);
  DECLARE v_productid int DEFAULT 0;
  DECLARE v_finished integer DEFAULT 0;
  DECLARE v_psize varchar(100) DEFAULT "";
  -- SET v_productid := productid;

  -- declare cursor for size product
  DECLARE psize_cursor CURSOR FOR
  SELECT
    tb.description
  FROM tb_productsize tb
  WHERE tb.id > productid;

  -- declare NOT FOUND handler
  DECLARE CONTINUE HANDLER
  FOR NOT FOUND SET v_finished = 1;

  OPEN psize_cursor;
    get_sizes:
    LOOP
    
      FETCH psize_cursor INTO v_psize;
    
      -- build email list
      SET size_list := CONCAT(v_psize, " / ", size_list);

      IF v_finished = 1 THEN
        CLOSE psize_cursor;
        LEAVE get_sizes;
      END IF;
    END LOOP get_sizes;
  RETURN size_list;
END

Open in new window

I Also tried this, but says "Result consisted of more than one row":

CREATE DEFINER = 'root'@'%'
FUNCTION getproduct_sizes(productid int)
  RETURNS text CHARSET utf8 COLLATE utf8_unicode_ci
  DETERMINISTIC 
BEGIN
    DECLARE x INT;
    DECLARE y INT;
    DECLARE size_list varchar(4000);
    SET x = productid;
    sloop:LOOP
        SET y = NULL;
        SELECT tb.description INTO y FROM tb_productsize tb WHERE id=x;
        SET size_list := CONCAT(y, " / ", size_list);
        ITERATE sloop;
    END LOOP;
    RETURN size_list;
END

Open in new window

Can anyone tell me what i'm doing wrong? Or maybe this is not possible :(

Thx in advanced,
Miguel

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

justaphase

ASKER

Portlet ! You're a life saver!
I didn't knew that MySQL function, it's mind blowing!!
I usually work with MsSQL, but i'm starting to like MySQL a bit more each day :D

So the query i wanted, and works, is this.
The table "tb_productproductsize" is the table that makes the connection between "tb_product" (the products) and "tb_productsize" (the sizes):

SELECT tp.id,tp.ref,tp.design,GROUP_CONCAT(DISTINCT ps.description ORDER BY ps.description SEPARATOR ' / ') sizes FROM tb_product tp
  LEFT JOIN tb_productproductsize tps ON tps.motherid=tp.id
  LEFT JOIN tb_productsize ps ON tps.childid=ps.id
GROUP BY tp.id

Open in new window

Thx!
Miguel