[Resolved] Displaying Results from two Tables

obrienkev

Hi all,

I want to display results from two tables.

In table one I have a list of questions with an id.

In table two I have a list of answers with three relevant answers for each question. The answers are linked to each relevant question by a question number field.

I want to display each question with its relevant answers.

How do I do that?

Here is my database query:

$query2 = "select answer_entry.answer from answer_entry, question_entry where answer_entry.questionnum = question_entry.id";

$result2 = mysql_query($query2);

And my whilw loop to display: This displays each question fine but it doesn't match the relevant answers. i.e. it displays all the answers after the first question! Why is this??

while($row = mysql_fetch_array($result))
{	
	echo '<p>'.($i+1).'. Question: </p>';
	echo $row['question']. '<br />';
	$i++;
	while($row2 = mysql_fetch_array($result2))
	{
	echo $row2['answer']. '<br />';	
	}
}

Thanks.

Kevin.

thorpe

your only selecting answers from your answer_entry table. so questions shouldn't be showing up at all.

try this;

$query2 = "SELECT question_entry.question, answer_entry.answer FROM question_entry, answer_entry WHERE question_entry.id = answer_entry.questionnum";

obrienkev

Thanks. But my while loop still wont give the desired results.

It displays just one question with all the answers.

How can it be fixed so as to display al questions with the relevant answers??

Thanks.

obrienkev

Think I got it. Here it is for anyone else:

2 tables: answer_entry, question_entry

MySQL Database Query:

// Perform Query
mysql_select_db('quiz') or die('Could not select Database');
$query = "select question_entry.question as entryquestion,
	           answer_entry.answer as entryanswer
	 from answer_entry
	   inner
	   join question_entry
	   on answer_entry.questionnum = question_entry.id	 order
	   by question_entry.question,
	        answer_entry.answer";

Rest of Code: Create form, Display results from both databases where answers match the question.

$result = mysql_query($query);
$qs = '';

// Retrieve results
$num_results = mysql_num_rows($result); /* Gives num of rows returned by query */

echo '<strong>Total of '.$num_results.' Questions to answer:</strong> ';

echo '<form id="Form1" onsubmit="return confirm(\'Are you Sure?\');" action="../user_entry/user1.php" method="post" name="Form1">';

while($row = mysql_fetch_array($result))
{
	$q = $row['entryquestion'];
	$ans = $row['entryanswer'];

if($q != $qs)
{
	$qs = $q;
	echo '<strong>'.$qs.'</strong><br />'."\n";
}	
echo '&nbsp;&nbsp;'.$ans.'<br />'."\n";	
}

Hope this will be of some help to people.

Kevin.