[RESOLVED] Displaying Query Results

obrienkev

Hi all,

I am working on developing a multiple choice quiz.

Questions are stored in question table.
Each answer is stored in answer table.
Multiple answers are linked to each relevant question via question_ID

I want to display each question with its relevant answer. At the moment my query displays the question six times.
How do I fix the query?

$query = "SELECT question_text, answer_text ".
                  "FROM question c, answer a ".
                 "WHERE c.question_ID = a.question_ID ".
                 "ORDER BY c.question_ID";
$result = mssql_query($query) or die('Select Query Error');
$num_results = mssql_num_rows($result);
echo '<p>Results: '.$num_results.'</p>';

// Display Question Results
while($row = mssql_fetch_array($result))
{
             echo '<p>'.$row['question_text'].'</p>';
}

// Display Answer Results
while($row = mssql_fetch_array($result))
{       

             echo '<label for="a1">'.$row['answer_text'].'</label>';
            echo '<input type="radio" name="a1" id="a1" value="" />';
}

Thanks.

justsomeone

I'm not sure that I fully understand your problem. Your query is looking for question/answer pairs. If six answers have the same question, then that question will appear six times in the pairs.

You have two options.

Use two queries. First use a query to return all the questions:

 $query1 = "SELECT question_ID, question_text ".
                  "FROM question ".
                 "ORDER BY question_ID";
$result1 = mssql_query($query1) or die('Select Query Error');

Then when you are looping through the results of that query, nest in a second query to get the answers for that question:


// Display Question Results
while($row = mssql_fetch_array($result1))
{
    $query2 = "SELECT answer_text ".
                  "FROM answer ".
                 "WHERE question_ID = " .$row['Question_ID'];
    $result2 = mssql_query($query2) or die('Select Query Error'); 
}

Now you have the answers for this qurstion and you can display them any way you like.

The second option is just to not print the question if it's the same as the previous one you printed, and control the output in the PHP

obrienkev

yeah thanks. it works.

// 1) Perform Question Query
$query = "SELECT TOP 1 question_ID, question_text ".
			 	 "FROM questions";
$result = mssql_query($query) or die('Select Query Error');

// 1b) Display Results
while($row = mssql_fetch_array($result))
{
 				// set variable
        $question_ID = $row['question_ID'];
 				echo '<h2>'.$row['question_text'].'</h2>';
}

// 2) Perform Answer Query
$query2 = "SELECT answer_text ".
			 	  "FROM questions q, answers a ".
				  "WHERE q.question_ID = a.question_ID ".
				  "AND q.question_ID = '$question_ID'";
$result2 = mssql_query($query2) or die('Select Query Error');
$num_results2 = mssql_num_rows($result2);

// 2b) Display Results
while($row2 = mssql_fetch_array($result2))
{
				echo '<p>'.$row2['answer_text'].'</p>';
}

justsomeone

Whoohoo! Welcome