How to write if statement in MySQL

Databases
1

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

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

2

What I can understand from your posts is:

eg: Results Table

Question_No | Answer | num1 | Student_Ans |

    1                |  TRUE   |  1       |    FALSE         |
    2                |  TRUE   |  1       |    FALSE         |
    3                |  FALSE  |  1       |    FALSE         |

your code altered:

SELECT
yesnoquestion_id AS Question_No,
if (answer = 1, ‘TRUE’, ‘FALSE’) AS Answer,
Count(student_id) AS num1,
if (student_answer = 1, ‘TRUE’, ‘FALSE’) AS Student_Ans
FROM
yesnoanswers
GROUP BY yesnoquestion_id
ORDER BY yesnoquestion_id ASC

SQL SERVER R2 2008:

SELECT
yesnoquestion_id AS Question_No,
CAST (
case
when isnull(answer,0) = 1 then ‘TRUE’
else ‘FALSE’
end
as varchar) Answer,
Count(student_id) AS num1,
CAST (
case
when isnull(student_answer,0) = 1 then ‘TRUE’
else ‘FALSE’
end
as varchar) Student_Ans,
FROM
yesnoanswers
GROUP BY yesnoquestion_id
ORDER BY yesnoquestion_id ASC

If possible could to provide an erd and a sample of the results you would like to retrieve.

regards
uli

3

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

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

–
do you mean?

SELECT
yesnoquestion_id AS Question_No,
if (answer = 1, ‘TRUE’, ‘FALSE’) AS Answer,
Count(student_id) AS num1
FROM
yesnoanswers
WHERE
student_answer = answer
OR
student_answer != answer
GROUP BY yesnoquestion_id
ORDER BY yesnoquestion_id ASC

4

no…it you do that it ganna give you the total number of students…

I have gotten student_answer = answer…
But i need to get student_answer != answer into another column but on the same table