I have following two MySQL queries and i want to execute it inside one procedure

Question

Following query returns 10 rows of recipe id's i wants to pass first query's output which is of 10 rows to the second query and display the output of both the queries inside a procedure.

Query 1:

SELECT 
    p.ID as recipe_ID,
    p.post_title as recipe_title,
    p.post_date as modified_date,
    p.post_name as recipe_url,
    CASE WHEN s.total_count THEN s.total_count ELSE 0 END as total_like_count,
    CASE WHEN c.bookmark=1 THEN 1 ELSE 0 END as bookmark_status,
    CASE WHEN c.like_status=1 THEN 1 ELSE 0 END as like_status,
    CASE WHEN pm.meta_value=1 THEN 1 ELSE 0 END as unlock_status

FROM
    posts p
LEFT JOIN social_val s on(p.ID=s.post_id)
LEFT JOIN social_status_connection as c on(p.ID=c.post_id AND c.user_id=1)
LEFT JOIN postmeta pm on(p.ID=pm.post_id AND pm.meta_value=1 AND pm.meta_key='recipe_lock'),
term_relationships as t, term_taxonomy tt
WHERE
  p.post_type = 'recipe'
    AND p.post_status = 'publish'   
    and p.ID = t.object_id
    and tt.term_id = 1279
    and t.term_taxonomy_id=tt.term_taxonomy_id     

ORDER BY modified_date DESC
LIMIT 0 , 10

Query 2 Below query is self join which generates image of above particular recipe id.

SELECT 
    p.guid as recipeImageURL
FROM
    posts p,
    posts m
where
    p.post_type = 'attachment'
        AND p.post_parent = m.ID
        AND m.ID = ID ORDER BY p.post_modified DESC
LIMIT 1;

Answer 1

If you need to loop over the output of one query to another you can use cursors.

For eg, in this stored routine to find in all tables databases we have looped over:

DECLARE table_cur CURSOR FOR SELECT ...
OPEN table_cur;
table_loop:LOOP
...code...
END LOOP;
CLOSE table_cur;

So in your case declare cursor for first query and loop over each recipeid.

Answer 2

I have tried with CURSOR inside single procedure:

First Procedure: Display recipe details

BEGIN SELECT p.ID as recipe_ID, p.post_title as recipe_title, p.post_date as modified_date, p.post_name as recipe_url, CASE WHEN s.total_count THEN s.total_count ELSE 0 END as total_like_count, CASE WHEN c.bookmark=1 THEN 1 ELSE 0 END as bookmark_status, CASE WHEN c.like_status=1 THEN 1 ELSE 0 END as like_status, CASE WHEN pm.meta_value=1 THEN 1 ELSE 0 END as unlock_status

              FROM
                  posts p
              LEFT JOIN social_val s on(p.ID=s.post_id)
              LEFT JOIN social_status_connection as c on(p.ID=c.post_id AND c.user_id=userid)
              LEFT JOIN postmeta pm on(p.ID=pm.post_id AND pm.meta_value=1 AND pm.meta_key='recipe_lock'),
              term_relationships as t, term_taxonomy tt
              WHERE
                p.post_type = 'recipe'
                  AND p.post_status = 'publish'   
                  and p.ID = t.object_id
                  and tt.term_id =termid
                  and t.term_taxonomy_id=tt.term_taxonomy_id        
               ORDER BY modified_date DESC
                LIMIT offset , 5;

CALL recipe_list(userid,termid,offset); //calling second procedure for image END

Second Procedure: (For recipe images) BEGIN DECLARE post_id int DEFAULT 0;

-- declare cursor for recipe_image_cursor DEClARE recipe_image_cursor CURSOR FOR SELECT p.ID as recipe_ID FROM posts p LEFT JOIN social_share_counts s on(p.ID=s.post_id) LEFT JOIN social_status_connection as c on(p.ID=c.post_id AND c.user_id=1) LEFT JOIN postmeta pm on(p.ID=pm.post_id AND pm.meta_value=1 AND pm.meta_key='recipe_lock'), term_relationships as t, term_taxonomy tt WHERE p.post_type = 'recipe' AND p.post_status = 'publish'
and p.ID = t.object_id and tt.term_id = 1279 and t.term_taxonomy_id=tt.term_taxonomy_id LIMIT 0 , 10;

OPEN recipe_image_cursor;

get_image: LOOP

FETCH recipe_image_cursor INTO post_id;

--Display Results SELECT p.guid as recipeImageURL FROM posts p, posts m where p.post_type = 'attachment' AND p.post_parent = m.ID AND m.ID = post_id ORDER BY p.post_modified DESC LIMIT 1; END LOOP get_image; CLOSE recipe_image_cursor; END

How can i display result of cursor in column format..is it possible? or which is the better way?..i am new with procedures and cursor