SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to write if statement in MySQL

    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
    Last edited by SpacePhoenix; Aug 30, 2011 at 23:08. Reason: placed php tags around the php code

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Location
    South Africa
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Location
    South Africa
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •