I have a slight problem. I have 2 tables in the same database:
The database is for a customer service/support questionaire that was used. Everything is fine up to the point where I want to print the form information to the screen.
The first table is the 'questions' table, with the fields:
- id, identifier, instructions, question, type, islast
The second table is the 'answers' table, where I provide the answer options. It's fields are:
- id, identifer, answer
Breakdown:
I always add the 'id' field as a 'just in case' for indexing. The identifier is where the magic happens.
Some of the questions I wanted to be select (drop down box), radio buttons, and others were checkboxes (for multiple choice).
The 'identifier' is what maps the answer options to the question. It's simply a naming convention. For example, question 1 has an identifer of 'a', and all of the anser options for that question also as the idenfier 'a'. The next question will be b, and c, and d, etc., etc.
What I want to do is dynamically print the compiled form to the page for my clients to fill out.
In order to do this, I'm joining the 'answers' table to the 'questions' table. I'm using a 'while' loop array to print the question, and all of the answer options with the matching 'identifier' for that question, just below the question itself.
An example would be:
question 1 has the identifier 'a' and so do all of the answer options..
1. What do you think is our weak point in support?
- Live Help
- Not enough answers in the self help
- Slow response time
The problem I'm getting is that the question is being printed multiple times for each answer option, so what I'm getting is this...
1. What do you think is our weak point in support?
- Live Help
1. What do you think is our weak point in support?
- Not enough ansers in the self help
1. What do you think is our weak point in support?
- Slow response time
The first example is the result that I want. Here is my code:
(I use the STRICT);
my($dbh, $sth, $rv, $f);
$dbh = DBI->connect("DBI:mysql:database=questionaire;host=localhost", "username", "password") or dienice("Can't connect: ",$dbh->errstr);
$sth = $dbh->prepare("select q.*, a.id as ansid, a.identifier as ansident,a.answer as ansas from answers a, questions q") or &dbdie;
$rv = $sth->execute;
# Print form header.
print qq(<form action="pollanswer.cgi" method="POST">);
while (my($id,$identifier,$instructions,$question,$type,$islast,$ansid,$ansident,$ansas) = $sth->fetchrow_array) {
# Display each question.
print qq(<p><b><small>$id. $question</small></b> );
# Display select header.
if ($type eq "select") {
print qq(<br> <select name="answer">);
}
if ($type eq "radio") {
print qq(<br> <input type="radio" name="answer" value="$ansas"><small>$ansas</small></input>);
}
if ($type eq "checkbox") {
print qq(<br> <input type="checkbox" name="answer" value="$ansas"><small>$ansas</small></input>);
}
if ($type eq "select") {
print qq(<option value="$ansas"><small>$ansas</small></option>);
}
# Display select footer.
if ($type eq "select") {
print qq(</select>);
}
}
As you can see, it's grouping wrong. It is printing multiple questions for each answer insteand of printing multiple answers for each question.
What am I doing wrong?