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;
result set into table from stored proc
Moderator: General Moderators
Re: result set into table from stored proc
do you need a store procedure?