MySQL stored function - append string

Question

I have searched but been unable to find anything that does what I need. I would like to create a stored function that will gather data from a secondary field and return a comma separated list of items as the return string. I can't seem to find any way to take a variable I create in the function and iterate through a record set and append each result to the variable so I can return it .. see below:

BEGIN
    DECLARE searchCat INT;
    DECLARE searchProd INT;
    DECLARE metas CHAR;
    SET searchCat = cat;
    SET searchProd = prod;
    SELECT * FROM offer_metas WHERE category = searchCat AND offer_id = searchProd
        gatherMeta: LOOP
                metas = metas + "," + meta_option;
                ITERATE gatherMeta;
        END LOOP gatherMeta;
    RETURN metas;
END

The function won't save because my syntax on the "metas = metas + meta_option;". What I am looking for is the command to append the current field falue of "meta_option" to the current variable "metas" so I can return a full list at the end.

Any idea?

UPDATE - SOLUTION

BEGIN
    DECLARE metas VARCHAR (300);

    SELECT GROUP_CONCAT(CONCAT(mn.title,'=',offer_metas.meta_option) ORDER BY mo.cat_seq ASC) INTO metas
    FROM offer_metas
    LEFT JOIN meta_options as mo ON mo.id = offer_metas.meta_option
    LEFT JOIN meta_names AS mn ON mn.category = mo.category AND mn.seq = mo.cat_seq 
    WHERE offer_metas.category = searchCat
    AND offer_metas.offer_id = searchProd
    ORDER BY cat_seq ASC;

    RETURN metas;
END

And then I just updated my SQL query to be as follows (1 is the offer category I have in my PHPand populate into the query):

SELECT offers.*, s.short_name AS sponsorName, s.logo AS sponsorLogo, getMetas(1,offers.id) AS metas 
FROM offers 
LEFT JOIN sponsors AS s ON s.id=offers.carrier 
GROUP BY offers.id 
ORDER BY end_date ASC

eggyal · Accepted Answer · 2012-04-30 18:00:27Z

3

Why not just

SELECT GROUP_CONCAT(meta_option SEPARATOR ',')
FROM offer_metas
WHERE category = searchCat AND offer_id = searchProd;

answered Apr 30, 2012 at 18:00

eggyal

127k18 gold badges220 silver badges244 bronze badges

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

1 Comment

Silvertiger Over a year ago

I ended up using Group_Concat anyway .. seems to have worked within the function and all is well. My answer has been updated with my final query.

xQbert · Accepted Answer · 2012-04-30 18:00:58Z

0

Option 1) use Group_Concat

Option 2) use || instead of +

Option 3) use Concat()

answered Apr 30, 2012 at 18:00

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Collectives™ on Stack Overflow

MySQL stored function - append string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related