Mysql Procedure with cursor

Hi,
We have two different queries:

(1) Master query:

SELECT forum_id, forum_name, parent_id, left_id, right_id
FROM phpbb_forums
WHERE parent_id =0
ORDER BY forum_name

This query returns 187 records.

We have transaction query based on output value of "left_id" and "right_id".

(2) Transaction query is:

SELECT forum_name, count( * ) AS cat_count, SUM( forum_posts ) AS post_count, SUM( forum_topics ) AS topic_count
FROM `phpbb_forums`
WHERE `left_id` >= "747"
AND `right_id` <= "900"

for this we have to execute transaction query 187 times to get all forums records.

We need to write a procedure or query to get all records at once.

How to write a procedure for this wit use of cursor  to save master query result in cursor and pass it in procedure to return all records set.

Thanks,
Pradeep
pradeep_jmdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pradeep_jmdAuthor Commented:
Please find attached file as well.
master-query-output.xlsx
transaction-query-output.xlsx
0
Tomas Helgi JohannssonCommented:
Hi!

Maybe this will help you do this in one sql call :)
SELECT f.forum_name, count( * ) AS cat_count, SUM( f.forum_posts ) AS post_count, SUM( f.forum_topics ) AS topic_count
FROM `phpbb_forums`f, (SELECT forum_id, forum_name, parent_id, left_id, right_id
FROM phpbb_forums
WHERE parent_id =0 )x
WHERE f.`left_id` >= x.`left_id`
AND f.`right_id` <= x.`right_id`
[b]AND f.forum_name = x.forum_name[/b]
GROUP BY f.forum_name
ORDER BY f.forum_name

Open in new window


This query is taking into account that the parent  forum_name is in the child record for each topic as well (the bold line). If not then remove the bold line.

Regards,
     Tomas Helgi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pradeep_jmdAuthor Commented:
Hi  Tomas,
The query returns 187 records but count are not matched as desired:

my first query is:
SELECT forum_id, forum_name, parent_id, left_id, right_id
FROM phpbb_forums
WHERE parent_id =0
ORDER BY forum_name

That returns all 187 left_id and right_id for each 187 forums.

Then we next to pass each left_id and right_id for each forum to get each forum cat count with sum the values.

My  second query is:
SELECT forum_name, count( * ) AS cat_count, SUM( forum_posts ) AS post_count, SUM( forum_topics ) AS topic_count
FROM `phpbb_forums`
WHERE `left_id` >= "747"
AND `right_id` <= "900"

747 and 900 is example values.

I have right one procedure to do this but its have error:

My procedure is:
CREATE PROCEDURE forumtechno.`forum_cat_count_test_2`()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE p_left_id INTEGER;
DECLARE p_right_id INTEGER;
DECLARE curs1 CURSOR FOR SELECT left_id, right_id
FROM phpbb_forums
WHERE parent_id =0
ORDER BY forum_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN curs1;
read_loop: LOOP
FETCH curs1 INTO p_left_id,p_left_id;
IF done THEN
LEAVE read_loop;
END IF;
END LOOP;
CLOSE curs1;

 -- form the query
  set @s = concat('SELECT forum_name, count( * ) AS cat_count, SUM( forum_posts ) AS post_count, SUM( forum_topics ) AS topic_count ',
       ' FROM phpbb_forums ',
       ' where left_id >= ', p_left_id,
       ' and right_id <= ', p_right_id);
  -- the following is just to see if we have correctly formed the statement.  Remove this after troubleshooting/debugging
 -- select @s;
 
  -- now actually run the statement
  prepare stmt from @s;
  execute stmt;
END;

And call like this:

call forum_cat_count_test_2();

BUT ITS GOT SYNTAX ERROR.

My requirement is:

(1) to get all left_id and right_id for each forum as from my first query
(2) Then insert count fro each sum columns as from my second query and insert these count records into one new table have columns like forum_name, cat_count, post_count and topic_count.

Thanks,
Pradeep
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

pradeep_jmdAuthor Commented:
Hi Tomas,
I have also write one stored procedure to get the forum category count:

Procedure is as follows:
CREATE PROCEDURE forumtechno.`get_forum_category_count_new`(IN p_left_id integer, IN p_right_id integer)
BEGIN
  -- form the query
   SELECT (select forum_name from phpbb_forums where parent_id =0 and left_id = p_left_id) as forum_name,
   count( * ) AS cat_count, SUM( forum_posts ) AS post_count, SUM( forum_topics ) AS topic_count
   FROM phpbb_forums
   WHERE left_id >= p_left_id
   AND right_id <= p_right_id;
END;

This is working fine with call like:
call get_forum_category_count_new (14925, 14998);

But i need to pass these parameter automatically in procedure and insert all records 187 forum_id in one separate table and select from it.

Please help me out.

Thanks,
Pradeep
0
pradeep_jmdAuthor Commented:
Hi Tomas,
I have complete the procedure with using two different cursors on to hold output of parameters needs to pass and with second cursor i have pass the parameter value one by one using loop and get all desired record set.

Thanks,
Pradeep
0
pradeep_jmdAuthor Commented:
issue resolved
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.