need php/mysql help

[ronnie482]

I've 2 tables poll_questions and poll_answers. Each question has more than one answers. like question: do you smoke ? in poll_answers table probable answers are like "Yes", "No", "No answer".

I wanna display all questions and answers from both in order like
1. do you smoke ?
ans:
a. yes
b. no
c. don't know

2. do you drink ?
ans:
a. yes
b. No
c. Don't Know

I use select statement like
SELECT * FROM poll_questions INNER JOIN poll_answers on poll_questions.id = poll_answer.question_id WHERE poll_options.poll_id=1;

I don't know how can I loop or use proper SELECT statement. Please help. Thanks in advance.

[eirche]

what are the structures of tables `question` and `answer`?

[ronnie482]

something like this

CREATE TABLE `poll_questions` (
`id` int(11) NOT NULL auto_increment,
`poll_id` int(11) NOT NULL,
`poll_question` varchar(255) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`)
)


CREATE TABLE `poll_answers` (
`id` int(11) NOT NULL auto_increment,
`question_id` int(11) NOT NULL,
`poll_id` int(11) NOT NULL,
`poll_answer` varchar(255) collate latin1_general_ci NOT NULL,
`poll_score` int(11) NOT NULL,
PRIMARY KEY (`id`)
)

[eirche]

don't know what each field is for. some seem redundant. can you give something more specific instead along the line "something like this"?


I don't know how can I loop or use proper SELECT statement.
i don't know exactly what you want to achieve. but this may be about to help you. http://ca.php.net/manual/en/function.mysql-query.php

[ronnie482]

so no one knows ?

[icio]

Try this. Note that i've commented what I think each field is for so if I'm wrong you'll need to take into consideration changes to be made.

Also, this is untested.


<form action="poll.php" method="post">
<?

/**
* CREATE TABLE `poll_questions` (
* `id` int(11) NOT NULL auto_increment, // The ID of the question
* `poll_id` int(11) NOT NULL, // The ID of the poll the question belongs to
* `poll_question` varchar(255) collate latin1_general_ci NOT NULL, // The question to be asked
* PRIMARY KEY (`id`)
* )
* CREATE TABLE `poll_answers` (
* `id` int(11) NOT NULL auto_increment, // The ID of the answer
* `question_id` int(11) NOT NULL, // The ID of the question the answer belongs to
* `poll_id` int(11) NOT NULL, // The ID of the poll the answer belongs to
* `poll_answer` varchar(255) collate latin1_general_ci NOT NULL, // The answer to the question
* `poll_score` int(11) NOT NULL, // The score for that answer
* PRIMARY KEY (`id`)
* )
*/

// We are going to get all of the questions from the database and store their information in the `questions` array
$questions = array();
$result = mysql_query("SELECT * FROM `poll_questions`;");
if ($result) while ($q = mysql_fetch_assoc($result)) $questions[] = $q;
mysql_free_result($result);

// Next we need to loop through each question, then within each question loop get each possible answer
foreach ($questions as $qnum => $question) {

// We are going to get all of the answers from the database for the relative question and store their information in the `answers` array
$answers = array();
$result = mysql_query("SELECT * FROM `poll_answers` WHERE `question_id`={$question['id']};");
if ($result) while ($a = mysql_fetch_assoc($result)) $answers[] = $a;
mysql_free_result($result);

// Now we need to output the question in some nice HTML
echo "<h2>Question $qnum</h2><p><strong>".htmlentities($question['poll_question'])."</strong></p>\n";
echo "<ol>\n";
foreach ($answers as $anum => $answer) {
echo "\t<li><input type=\"radio\" name=\"q{$question['id']}\" value=\"{$answer['id']}\" />".htmlentities($answer['poll_answer'])."</li>\n";
}
echo "</ol>\n\n";
}
?>
</form>

Hope that helps :thumb:

[ronnie482]

worked like a charm. thanks a lot. I really appreciate your help.

[icio]

Glad I could (-: