Solved

MySQL Function with loop and concat

Posted on 2015-02-24
2
318 Views
Last Modified: 2015-02-25
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
0
Comment
Question by:justaphase
2 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40629558
Why can't you use GROUP_CONCAT() ?

To assist more with this I think we need some "sample & representative data"

i.e. per table involved, supply a few rows of data.
But please ensure that if NULLs are part of the problem that NULLS are represented in the data you provide.
0
 
LVL 1

Author Closing Comment

by:justaphase
ID: 40630218
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
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question