Solved

mysql loop only returns one set of rows from table

Posted on 2014-02-17
9
52 Views
Last Modified: 2016-05-18
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 -
R


DELIMITER $$
USE `bedrooms_tst`$$
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))
BEGIN
DECLARE X INT DEFAULT 0;
SET X=1;
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;
END WHILE;
    END$$
DELIMITER ;

procedure call is

call test_loop(6654435546,13,3)

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"
0
Comment
Question by:rwinnick
  • 4
  • 4
9 Comments
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39865848
It doesn't matter what you pass to your procedure - you're running the same query over and over again, so you're always going to get the same result.

If your table only contains 4 rows, then you're never going to retrieve more that 4 rows from it.

Your select query is selecting from the table with the following WHERE clause:

WHERE unit_id=xxx AND qr_form_id=xxx

but nowhere in your question have you mentioned unit_id or qr_form_id as columns in your table, so where do they fit it.

Maybe you need to show us your CREATE TABLE statement, your data, and then explain what you're trying to achieve.
0
 

Author Comment

by:rwinnick
ID: 39865975
Hi Chris -

Thanks for the quick reply -
the full table has a few more columns which are not relevant to what I am trying to extract with the select so I didn't include them - sorry here are the additional table columns -

company_id int(5), unit_id varchar(15), qr_form_id int(2), bedrooms int(2), q_id int(10)

I want to use the table to create a form with 4 questions for each room in the house -

Rather than having to create all possible iterations of the 4 questions by all possible room configurations as I  have several thousands of houses in my database -

I was hoping I could simply 'repeat' the select query and somehow combine the results (4 rows + 4 rows +4 rows ……) and use this result to create a form with the appropriate number of rows for the number of rooms in the house hence the unit_id and form_id columns -

Hope this helps -
R
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39866041
OK. Just trying to get this clear in my head. You have 4 questions, that will always be the same. Now if a house has 2 bedrooms, you want a form that shows those same 4 questions twice (8 questions). If a house has 4 bedrooms, then you have a form that shows the same 4 questions 4 times (16 questions in total)

Now, I'm assuming that you somehow want to save the answers to those questions tp your database, so for a 2 bedroom house, you will save 8 answers, and for the 4 bedroom house, you will save 16 answers.

If that's the case, then you would select the 4 questions from your DB table, and output them however many times you needed to create you form (twice for 2 bedroom, 4 times for 4 bedroom etc.) When the form is submitted, you would then save the answers to a different table - 1 database row per answer

Is that the sort of thing you mean?
0
 

Author Comment

by:rwinnick
ID: 39866062
Hi Chris -

yep you've got it :-)

I have the whole thing (making the form and saving the results) working with the four rows -

It's getting the repeated (additive) rows working that's making me crazy -

The reason I am hoping to do this in the most efficient way possible is that there may be several thousand 'forms' being created and filled in at any point in time -

R
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39866117
Without seeing all of your code, and your full data structure, I can't be specific, but the fact that the 4 questions are the same everytime, means that you only need to grab those 4 questions once. Using PHP, you then just repeat the output of the 4 questions in a for loop. For the answers, I'm guessing you'd use form fields, so name them according. Here's the general idea (not actual code!)

//select your 4 questions from the database
$query = "select questions from your table";
$questions = $query->fetchAll();

//create an HTML form
echo "<form action='saveAnswers.php' method="POST">

//we need to output the set of questions several times (number of bedrooms)
for ($i = 1; $i <= numOfBedrooms; $i++):

   //output each question, along with a form field to store the answer
   foreach ($questions as $question):
      echo $question;
      echo "<input name="answers[$i][$question->num]" type="text">
   endforeach;   

endfor;
echo "</form>';

Open in new window

You'd obviously need some way to link the answer to a specific house (houseID), and I'm guessing that the record that stores the house info, also stores the number of bedrooms.
0
 

Author Comment

by:rwinnick
ID: 39866155
Hi Chris -

Can we do it entirely as a mysql stored procedure rather than with php -

The reason is that I am using an IDE to create the actual form and can 'feed' it the results of a stored procedure fairly easily -

This will keep the form's look/feel the same as the other forms I am using -
Rather than having to reinvent the wheel :-)

Hope I'm not making this too crazy -

R
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39866186
I've never heard of it done that way before. Using an IDE as a user interface seems a very odd way of doing things, and doing it all with stored procedures sounds overly complicated - it's not something I'm familiar with, so I'd have no idea where to start - sorry :(
0
 

Author Comment

by:rwinnick
ID: 39866201
OK -
don't mean to make you crazy :-)
I'll try fooling with the php code and see what I can make happen -
Will also continue to investigate the stored procedure approach -
Thanks
will keep you posted -
R
0

Featured Post

Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now