Hi everyone....
I have a table in my database comprising of 'yesnoquestion_id', 'student_id', 'student_answer', 'answer'.
I need to get some information out into my webpage interface using MySQL. I need to output the number of student whose answers answers are correct to the right answer into a table that will show on the interface..... the write answer is 'answer' while the students' answer is 'student_answer'.

Here is what i have done so far:

PHP Code:
<?php

global $connection;
$query = "SELECT yesnoquestion_id AS Question_No, if (answer = 1, 'TRUE', 'FALSE') AS Answer, Count(student_id) AS num1
FROM yesnoanswers
WHERE student_answer = answer
GROUP BY yesnoquestion_id
ORDER BY yesnoquestion_id ASC ";
$collate_set = mysql_query($query, $connection);

//Drawing table and inserting data into it
echo "<table border='5'>
<tr>
<th>Question Number</th>
<th>Question Answer </th>
<th>No. of Student correct</th>
</tr>";
while ($row = mysql_fetch_array($collate_set))
{
echo "<tr>";
echo "<td>" . $row ['Question_No'] . "</td> ";
echo "<td>" . $row ['Answer'] . "</td> ";
echo "<td>" . $row ['num1'] . "</td> ";
echo "</tr>";
}
echo "</table>";
?>
This is outputting just the 'YESNOQUESTION_ID', 'NUMBER OF STUDENTS', 'STUDENT_ANSWER = ANSWER'.

How can i get it to also output the 'STUDENT_ANSWER != ANSWER' i.e where 'student_answer' IS NOT 'answer' on the same table ?


Thank you

First: MySQL is NOT php so this is the wrong place for the question.

Second: This

(answer = 1, 'TRUE', 'FALSE') AS Answer,

will throw an error if the server is running on a case insensitive OS such as Windows

Assuming that 'answer' is a column name, you CANNOT have a column AND an alias with the same identifier.

Originally Posted by olaad

How can i get it to also output the 'STUDENT_ANSWER != ANSWER' i.e where 'student_answer' IS NOT 'answer' on the same table ?


Thank you

Removing WHERE student_answer = answer might help

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("my_db", $con);

mysql_query("INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Peter', 'Griffin', '35')");

mysql_query("INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Glenn', 'Quagmire', '33')");

mysql_close($con);
?>

might help you