Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Displaying dynamic form elements from database

Posted on 2009-02-21
9
Medium Priority
?
183 Views
Last Modified: 2013-12-12
Hello all. So this is a bit of a complicated problem which i will attempt to explain (I have tried a couple of times but perhaps didn't phrase it correctly).

What I am creating is a dynamic feedback form, for rating lecturers who teach classes. What I need to do (see picture1) is for each class (when user selects it) a form needs to be dynamically created which displays a variable number of questions from a database. Here is an example:

--------------------------------
How did you rate the teaching?

Lecturer name 1                             (drop down box where a rating can be selected)
Lecturer name 2                                   "                                                    "
Lecturer name 3                                   "                                                    "

the amount of lecturers and questions can vary depending on the module, won't be more than six of wither though...So this will be looped through as in the picture
------------------------------------

What I understand is that this would be some sort of nested loop (see the code below and picture2). The code I have below displays only one question and lumps all the ratings into one box. It also doesn't display the lecturers (can't work out how to add this in) so is definitely wrong.

Here are the tables I have, I will try and explain how they relate to each other:

Survey  // this is essentially the feedback form
 SurveyID (PK)
 ModuleID (FK) // links to the modules table
 SurveyDate

SurveyQuestion // links the survey to question database
  qsID (PK)
  SurveyID (FK)
  QuestionID (FK)

Question
  QuestionID (PK)
  QuestionType // Two types, rating and text (only concerned by rating)
  QuestionText

QuestionRating // links questions to rating scale name (drop down lists)
  QuestionRating (PK)
  QuestionID (FK)
  ratingID (FK)

RatingName // this isn't actually needed in the design, the next table is the important one
  ratingID (PK)
  RatingName (FK)

RatingScale // this is what populates the drop down list
  rsID (PK)
  ratingID (FK)
  ratingValue // e.g 1, 2, 3, 4, 5 or Great, Good, Ok, Weak, Rubbish.

Modules
  moduleID (PK)
  module_name
  description

Modules_Lecturers
  mIID (PK)
  moduleID (FK)
  lecturerID (FK) // links to lecturers table

Lecturer
  lecturerID
  leader_fname
  leader_Sname

I realise this looks complicated but thought it was important to give the full table structure..

When the form is submitted the date is going to go into this table:

FeedbackRating
  FeedRating
  SurveyID
  LecturerID
  RatingValue

So does anyone know how to do this? I will be incredibly grateful for any help.
$query = "SELECT SurveyQuestion.QuestionID, QuestionText
                                FROM SurveyQuestion, Question
                                WHERE SurveyID = '$surveyID'
                                AND Question.QuestionID = SurveyQuestion.QuestionID
                                AND Question.QuestionType = 'Rating'";
                                $result = mysql_query($query);
                                while ($question_rec = mysql_fetch_assoc($result)) {
                                  
                                  print "Q: {$question_rec['QuestionText']}<br>\n";
                                  
                                  #guessed this to some degree - may need to revise:
                                  $query = "SELECT DISTINCT RatingScale.ratingid, ratingValue
                                FROM RatingScale, QuestionRating, SurveyQuestion
                                WHERE RatingScale.ratingID = QuestionRating.ratingID
                                                                AND SurveyQuestion.QuestionID = {$question_rec['QuestionID']}";  #have assumed questionid is a numeric data type, or there would be quotes around the {} characters
                                  $result2 = mysql_query($query);
                                  print "<select name=\"q{$question_rec['QuestionID']}\"\n";
                                  while ($rating_rec = mysql_fetch_assoc($result2)) {
                                        print "<OPTION VALUE=\"{$rating_rec['ratingID']}\">{$rating_rec['ratingValue']}</OPTION>\n";
                                  }
                                  mysql_free_result($result2);
                                  print "</SELECT\n";
                                }

Open in new window

example4.jpg
example.jpg
0
Comment
Question by:Chocula
  • 5
  • 4
9 Comments
 
LVL 16

Expert Comment

by:Robin
ID: 23704916
I revised your code a little. Does it show what you want?
<?php
//at this point $surveyID should be known!
 
$query ="SELECT SurveyQuestion.QuestionID, QuestionText";
$query.="FROM SurveyQuestion, Question";
$query.="WHERE SurveyID = '$surveyID'";
$query.="AND Question.QuestionID = SurveyQuestion.QuestionID";
$query.="AND Question.QuestionType = 'Rating'";
$result=mysql_query($query);
$qcnt=1;
while($question_rec=mysql_fetch_assoc($result)) {
  print "Q". $qcnt .": {$question_rec['QuestionText']}<br>\n";
  
  $query ="SELECT DISTINCT RatingScale.ratingID, RatingScale.ratingValue";
  $query.="FROM RatingScale, QuestionRating";
  $query.="WHERE RatingScale.ratingID = QuestionRating.ratingID";
  $query.="AND QuestionRating.QuestionID = {$question_rec['QuestionID']}";
  $result2 = mysql_query($query);
  print "<select name=\"q{$question_rec['QuestionID']}\"\n";
  while ($rating_rec = mysql_fetch_assoc($result2)) {
        print "<OPTION VALUE=\"{$rating_rec['ratingID']}\">{$rating_rec['ratingValue']}</OPTION>\n";
  }
  mysql_free_result($result2);
  print "</select>\n";
  print "<br><br>\n";
}
?>

Open in new window

0
 

Author Comment

by:Chocula
ID: 23705285
that code gives me this error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /Applications/MAMP/htdocs/project/feedback.php on line 137

How could this be expanded to include lecturers (as in the picture)?
0
 
LVL 16

Expert Comment

by:Robin
ID: 23705461
And yes, you wanted to rate the lecturers. I forgot that too ;-)

What does line 137 contain exactly?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 16

Accepted Solution

by:
Robin earned 2000 total points
ID: 23705519
The error came probably because of the way I appended the query, and not adding spaces.

Try this:
<?php
//at this point $surveyID should be known!
 
$query ="SELECT SurveyQuestion.QuestionID, QuestionText";
$query.=" FROM SurveyQuestion, Question";
$query.=" WHERE SurveyID = '$surveyID'";
$query.=" AND Question.QuestionID = SurveyQuestion.QuestionID";
$query.=" AND Question.QuestionType = 'Rating'";
$result=mysql_query($query);
$qcnt=1;
while($question_rec=mysql_fetch_assoc($result)) {
  print "Q". $qcnt .": {$question_rec['QuestionText']}<br>\n";
 
  $query ="SELECT Lecturer.lecturerID, leader_fname, leader_Sname";
  $query.=" FROM Survey, Modules_Lecturers, Lecturer";
  $query.=" WHERE Survey.SurveyID = '$surveyID'";
  $query.=" AND Survey.ModuleID = Modules_Lecturers.moduleID";
  $query.=" AND Modules_Lecturers.lecturerID = Lecturer.lecturerID";
  $resultL = mysql_query($query);
  while ($lect_rec = mysql_fetch_assoc($resultL)) {
    print "Lecturer ". $lect_rec['leader_fname'] ." ". $lect_rec['leader_Sname'];
  
    $query ="SELECT DISTINCT RatingScale.ratingID, RatingScale.ratingValue";
    $query.=" FROM RatingScale, QuestionRating";
    $query.=" WHERE RatingScale.ratingID = QuestionRating.ratingID";
    $query.=" AND QuestionRating.QuestionID = {$question_rec['QuestionID']}";
    $result2 = mysql_query($query);
    print "<select name=\"q{$question_rec['QuestionID']}\"\n";
    while ($rating_rec = mysql_fetch_assoc($result2)) {
      print "<OPTION VALUE=\"{$rating_rec['ratingID']}\">{$rating_rec['ratingValue']}</OPTION>\n";
    }
    mysql_free_result($result2);
    print "</select><br>\n";
  }
  print "<br><br>\n";
}
?>

Open in new window

0
 

Author Comment

by:Chocula
ID: 23705584
you are a genius! thank you. thank you. Works perfect. Got a question just so I understand, why is it necessary to have the $query =, $query. etc etc? I don't think I have every seen a query done this way. (although I haven't been coding very long!)
0
 

Author Comment

by:Chocula
ID: 23705615
*the only thing that doesn't work is the question count.. but I added in:

$qcnt++;

after

print "Q". $qcnt .": {$question_rec['QuestionText']}<br>\n";
$qcnt++;

and that works now. Again thank you
0
 

Author Closing Comment

by:Chocula
ID: 31549665
excellent, covered everything. Great solution
0
 
LVL 16

Expert Comment

by:Robin
ID: 23705887
My mistake. I forgot the $qcnt++.

Building the query that way is just my way of building it and keeping an overview.

$query.=" FROM Survey, Modules_Lecturers, Lecturer";

is just short hand for

$query=$query ." FROM Survey, Modules_Lecturers, Lecturer";

where . concatenates the strings.
0
 

Author Comment

by:Chocula
ID: 23706456
Ah ok. That makes sense. Now comes the task of how to post all this variable information..
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

577 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