displaying multiple fields?

dataquake · January 4, 2011, 10:02pm

Thanks in advance, I’m new to php and mysql.
I’m trying to create a quiz with multiple choice answers…
Each question might have 5 answers, I have a database with 2 tables
Questions, Answers

I can’t sort out how to associate the answers and questions here’s my code

[b]$theQuestions = mysql_query("SELECT
questions.id,
questions.question,
answers.id,
answers.answer,
answers.questionId
FROM
questions ,
answers

WHERE
questions.id = answers.questionId[/b]

while($row = mysql_fetch_array($theQuestions))
{
echo $row[‘question’];
echo “—
”;
echo $row[‘answer’];
echo $row[‘answer’];
echo $row[‘answer’];
echo $row[‘answer’];
echo $row[‘answer’];

}

I keep getting the same “answer” and can’t sort out how to get different answers for each question

Any help would be great, thanks!

smokeys_13 · January 4, 2011, 10:44pm

Hi there,

Try the following query:

SELECT
q.`id`
,q.`question`
,a.`id`
,a.`answer`
,a.`questionId`
FROM
`questions` AS q
INNER JOIN
`answers` AS a
ON
q.`id` = a.`questionId`

dataquake · January 6, 2011, 7:14pm

thank you so much, that has the records showing up! I can’t for the life of me figure out how to display them now…

I would like it to look like this…

Question
Answer 1
Answer 2
Answer 3
Answer 4

now the code displays
question 1
Answer1

question 1
Answer 2

question 1
Answer 3

question 1
Answer 4

my code is below, thank you so much for your time and help, YOU ROCK!

$theQuestions = mysql_query("

SELECT

q.id
,q.question
,a.answer
,a.questionId
FROM
questions AS q
INNER JOIN
answers AS a
ON
q.id = a.questionId

");

while($row = mysql_fetch_array($theQuestions))
{

echo “Question:” . $row[‘question’] . “
” ;
echo “Answer:” . $row[‘answer’] . “

” ;

;
}

smokeys_13 · January 6, 2011, 10:14pm

Not a problem.

Try the following:
[php]

$theQuestions = mysql_query("
SELECT
q.id
,q.question
,a.answer
,a.questionId
FROM
questions AS q
INNER JOIN
answers AS a
ON
q.id = a.questionId ");

$questions = array();
while(($row = mysql_fetch_array($theQuestions)) !== false)
{
$questions[$row[‘questionId’]][‘question’] = $row[‘question’];
$questions[$row[‘questionId’]][‘answers’][] = $row[‘answer’];
}

foreach($questions as $qid => $info)
{
echo “

”.$info[‘question’]."

";
foreach($info[‘answers’] as $answer)
{
echo $answer."
";
}
}
[/php]

I haven’t actually tested this, I made it up as I went along so apologies if this doesn’t work - just let me know what errors you get and I’ll look into it.

dataquake · January 10, 2011, 9:52pm

You are simply AWESOME! thank you so much!

smokeys_13 · January 10, 2011, 10:13pm

;D no problem. Glad to have helped out.