Solved

mysql loop only returns one set of rows from table

Posted on 2014-02-17
9
72 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 43

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 43

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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
 
LVL 43

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 43

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

621 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