Page 1 of 1

result set into table from stored proc

Posted: Sat Jan 22, 2011 1:46 pm
by ksbrace
I have a stored proc that I am trying to build. Currently I have a nested loop, then it populates a table (2 rows). I then do a select on the table. I would LOVE to be able to be use a date selector to select the date range and then get the results into an hTML table. Below is my sp that I have been working on and right now I have to hard code the dates into it. Any and all help is greatly appreciated. I'm very new to PHP. Thanks in advance!


DROP PROCEDURE IF EXISTS collectionsmax.monthlyGauge;
CREATE PROCEDURE collectionsmax.`monthlyGauge`()
BEGIN

##--declare local vars
Declare done boolean default 0;
declare donep boolean default 0;
Declare c varchar(50);
declare pAmt decimal (8,2);
declare payDate date;

##--declare collectors cursor
declare collectors cursor
for
select distinct assignedto from payments;

##payment cursor
declare paymnts cursor
for
select sum(paymentamount) from payments where assignedto=c and paymentdate between '2011-01-01 00:00:00' and '2011-01-21 23:59:59' and paymentstatus='POSTED';
# -- Declare continue handler
DECLARE CONTINUE HANDLER FOR not found set done:=true;

create table if not exists collectionsmax.monthGauge(collector varchar(50), payAmt decimal(8,2));
# -- Open the cursor
OPEN collectors;

# --Loop through all rows

Loop1: loop

## --Get Collectors
Fetch collectors into c;

if done then
close collectors;
leave loop1;
end if;


open paymnts;
# -- Create a table to store the results


Loop2: loop

fetch paymnts into pAmt;
##--Insert into monthguage table
Insert into monthGauge(collector, payAmt)
values(c, pAmt);
if done then set done:=false;
close paymnts;
leave loop2;
end if;
end loop Loop2;
end loop Loop1;

## --end of loop

## -- Close the cursor

END;

Re: result set into table from stored proc

Posted: Sun Jan 23, 2011 3:49 pm
by yacahuma
do you need a store procedure?