doubledee Posted April 1, 2012 Share Posted April 1, 2012 As part of my User Profile, I have a series of open-ended questions that Members can answer, e.g... 1.) Why did you start your own business? 2.) If you could offer one piece of advice to other entrepreneurs, what would it be? : 10.) How do you compete against large corporations? I was leaning towards creating a many-to-many relationship like this... member -||---------0<- answer ->0---------||- question From a database standpoint this works great, HOWEVER, I just realized a big problem... If run this query... SELECT response FROM answer WHERE member_id = 1; ...then I would get TEN RECORDS back and I don't know of any way to maps those to the 10 Text Boxes on the "My Thoughts" page?! (It would be a real PITA to have to create 10 separate Prepared Queries in PHP - one for each Field - to fill out my page?!) So, is there a way to keep my table structure, but push the 10 records from above into 10 separate variables so I would have something like $response1, $response2,... $response10 ?? Thanks, Debbie Quote Link to comment Share on other sites More sharing options...
Drummin Posted April 1, 2012 Share Posted April 1, 2012 It would seem that you'd want to have the question or at least the question id (from another table) stored with each answer. Then you can just each out each question and answer as a set. Quote Link to comment Share on other sites More sharing options...
doubledee Posted April 1, 2012 Author Share Posted April 1, 2012 It would seem that you'd want to have the question or at least the question id (from another table) stored with each answer. Then you can just each out each question and answer as a set. I'm not understanding you... Here is the layout I am thinking abut implementing... member: - id - name answer: - member_id - question_id - response question: - id - question_copy So if I understood what you may have been saying, "Yes, I know the member_id and question_id for every response." The problem is that on my "my_thoughts.php" page, I have 10 Text Boxes I need to populate, and I do NOT want to have to build 10 separate Queries - one for each Text Box. Follow me?? Debbie P.S. There is also the issue of "How do I INSERT 10 Answers into the database?" Quote Link to comment Share on other sites More sharing options...
Drummin Posted April 1, 2012 Share Posted April 1, 2012 Here's a sample. Assuming you are using one DB table for this. Otherwise it will require having question ID's hidden in the form which you grab from your question table. <?php if (isset($_POST['submit_survey'])){ foreach($_POST['question'] as $k => $q){ $question = mysql_real_escape_string($q); $answer = mysql_real_escape_string($_POST['answer'][$k]); //insert into DB. Test echo echo "$question - $answer<br />"; } } ?> <html> <body> <form method="post" action=""> Why did you start your own business?<br /> <input type="hidden" name="question[]" value="Why did you start your own business?" /><input type="text" name="answer[]" /><br /> If you could offer one piece of advice to other entrepreneurs, what would it be?<br /> <input type="hidden" name="question[]" value="If you could offer one piece of advice to other entrepreneurs, what would it be?" /><input type="text" name="answer[]" /><br /> How do you compete against large corporations?<br /> <input type="hidden" name="question[]" value="How do you compete against large corporations?" /><input type="text" name="answer[]" /> <input type="submit" name="submit_survey" value="Submit" /> </form> </body> </html> Quote Link to comment Share on other sites More sharing options...
doubledee Posted April 1, 2012 Author Share Posted April 1, 2012 Drummin, Your example doesn't really address anything in my last post... For instance, how do I take 10 returned records from a query and either put them in an array or into 10 separate variables which I can then use to do something like this... <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Untitled Document</title> </style> </head> <body> <dl class="userinfo"> <?php echo "<dt>Q1: $question01</dt> <dd>$response01</dd> <dt>Q2: $question02</dt> <dd>$response02</dd>"; </dl> </body> </html> Debbie Quote Link to comment Share on other sites More sharing options...
Drummin Posted April 1, 2012 Share Posted April 1, 2012 Not tested but I imagine you would do something like this. <?php $survey_results ="<dl class=\"userinfo\">"; $sql="SELECT answer.response,question.question_copy WHERE answer.question_id=question.id AND member='$memberid'"; $result=mysql_query($sql); while($row = mysql_fetch_array($result)){ $survey_results .= "<dt>{$row['question_copy']}</dt> <dd>{$row['response']}</dd>\r"; } $survey_results .='</dl>'; ?> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Untitled Document</title> </style> </head> <body> <?php echo "$survey_results"; ?> </body> </html> Quote Link to comment Share on other sites More sharing options...
Drummin Posted April 1, 2012 Share Posted April 1, 2012 I've not worked much with JOIN LEFT but I think it might look like this. Sorry if not correct. <?php $survey_results ="<dl class=\"userinfo\">"; $sql = "SELECT answer.response, answer.question_id, question.question_copy , question.id ". "FROM answer LEFT JOIN question ". "ON answer.question_id=question.id AND member='$memberid'"; $result=mysql_query($sql); while($row = mysql_fetch_array($result)){ $survey_results .= "<dt>{$row['question_copy']}</dt> <dd>{$row['response']}</dd>\r"; } $survey_results .='</dl>'; ?> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Untitled Document</title> </style> </head> <body> <?php echo "$survey_results"; ?> </body> </html> Or if you want to list all questions available and the questions that were answered, you'd switch the table order. (I think) <?php $survey_results ="<dl class=\"userinfo\">"; $sql = "SELECT answer.response, answer.question_id, question.question_copy , question.id ". "FROM question LEFT JOIN answer ". "ON answer.question_id=question.id AND member='$memberid'"; $result=mysql_query($sql); while($row = mysql_fetch_array($result)){ $survey_results .= "<dt>{$row['question_copy']}</dt> <dd>{$row['response']}</dd>\r"; } $survey_results .='</dl>'; ?> Quote Link to comment Share on other sites More sharing options...
Drummin Posted April 1, 2012 Share Posted April 1, 2012 There is also the issue of "How do I INSERT 10 Answers into the database?" <?php if (isset($_POST['submit_survey'])){ foreach($_POST['question'] as $k => $q){ $question = mysql_real_escape_string($q); $answer = mysql_real_escape_string($_POST['answer'][$k]); //insert into DB. Test echo echo "$question - $answer<br />"; } } ?> <html> <body> <form method="post" action=""> Why did you start your own business?<br /> <input type="hidden" name="question[]" value="Why did you start your own business?" /><input type="text" name="answer[]" /><br /> If you could offer one piece of advice to other entrepreneurs, what would it be?<br /> <input type="hidden" name="question[]" value="If you could offer one piece of advice to other entrepreneurs, what would it be?" /><input type="text" name="answer[]" /><br /> How do you compete against large corporations?<br /> <input type="hidden" name="question[]" value="How do you compete against large corporations?" /><input type="text" name="answer[]" /> <input type="submit" name="submit_survey" value="Submit" /> </form> </body> </html> Your example doesn't really address anything in my last post... Reply: Maybe it was a P.S. but it WAS a question in your last post. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.