mysql loop only returns one set of rows from table
Posted on 2014-02-17
I have a table that has four rows with the following information:
q_id_bed int(10), q_question_bed text, q_format int(1)
This information represents four questions relating to a single bedroom in a house -
I would like to repeat these four questions for each bedroom in a multi bedroom house, i.e., repeat the same four questions twice for a two bedroom house (total eight questions), repeat three times for three bedroom house (total twelve questions), etc.
I have managed to cobble together a procedure that repeats a select from the table which returns the four rows and then repeats the select based on the number of bedrooms in the house (I think :-)
However, it only returns the four rows regardless of the number of bedrooms, i.e., repeats -
I need some help getting it to add the rows to the result for each repeat so that I get 8, 12, 16, …. rows that I need for all the bedrooms -
The code I have so far is shown below -
Thanks in advance for any help -
Examples appreciated -
DROP PROCEDURE IF EXISTS `test_loop`$$
CREATE DEFINER=`rbilljones`@`%` PROCEDURE `test_loop`(IN s_unit_id VARCHAR(15), IN s_qr_form_id INT(2), IN s_bedrooms INT(2))
DECLARE X INT DEFAULT 0;
WHILE X <= s_bedrooms DO
SELECT q_id_bed, q_question_bed, q_format FROM qr_inspect_questions_bedrooms WHERE unit_id=s_unit_id AND qr_form_id=s_qr_form_id;
SET X= X+1;
procedure call is
result is always the four rows shown below regardless of s_bedrooms value in the call
"q_id_bed" "q_question_bed" "q_format"
"1" "test first first question" "5"
"2" "test second bedroom question" "5"
"3" "test third bedroom question" "5"
"4" "test fourth bedroom question" "5"