Hi all!
Given a set of checkbox values that are submitted through an html form,
what's the best way to loop through the submitted values to update more
than one row in a table?
Imagine a table called 'message_table':
mid | message | status
----+---------+-------
1 | Text1 | H
2 | Text2 | H
3 | Text3 | H
4 | Text4 | H
A web page presents the user with all messages flagged with 'H'. User
checks messages 1,3 and 4 and submits form.
(i.e. approved=1&approved=3&approved=4)
After performing postgreSQL update, rows 1, 3 and 4 would be updated to:
mid | message | status
----+---------+-------
1 | Text1 | A
2 | Text2 | H
3 | Text3 | A
4 | Text4 | A
Since I'm using postgreSQL, I tried writing:
CREATE OR REPLACE FUNCTION update_messages(approved integer) RETURNS
integer AS
$body$
DECLARE
new_status varchar;
new_sample record;
BEGIN
new_status := 'A';
FOR new_sample IN SELECT * FROM message_table WHERE status='H' ORDER BY
mid LOOP
UPDATE message_table SET status = new_status
WHERE mid = approved;
END LOOP;
RETURN 1;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
and calling the function in XSP with:
SELECT update_messages(<xsp-request:get-parameter name="approved"/>);
Unfortunately, my function as written only updates the first value
submitted (mid 1), and doesn't loop through the other two values submitted.
Can someone help me from getting ulcers? What method would you use to
update all rows in one shot?
Thanks!
Daniel
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
