Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

mysql loop only returns one set of rows from table

Posted on 2014-02-17
9
Medium Priority
?
87 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 44

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 44

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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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 44

Accepted Solution

by:
Chris Stanyon earned 2000 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 44

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

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

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

688 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