ERROR WHEN TRYING TO EXPORT DATA FROM MYSQL WAMPSERVER TO CSV

bbanda

i am trying to export user information about exam details ie all the question and answers the candidate has chosen but it is coming out as follows,
 
Notice: Undefined variable: total_score in C:\wamp64\www\online-exam-portal\code\manage-exam_export-to-csv.php on line 98 
QUESTION TYPE QUESTION CORRECT ANSWER CHOSEN OPTION ID FIRST NAME LAST NAME SCORE(out of )
Multi-choice where is the sun located D
Multi-choice why is earth round A
Multi-choice why is earth created A
UserName bledza banda 5

here is the export-csv.php file,

<?php
require_once '../connect.php';
require_once '../session.php';

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Check if the user is logged in
if (!isset($_SESSION['userID'])) {
    // Redirect or handle unauthorized access
    header('Location: ../session.php');
    exit();
}

// Set the content-type to CSV and specify the filename for download
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=user_responses.csv");
header("Pragma: no-cache");
header("Expires: 0");

// Get the user ID from the session
$userID = $_SESSION['userID'];
$examID = $_POST['examID'];

// Fetch user responses to questions
$sql = "
    SELECT
        'Multi-choice' AS exam_type,
        mcq.question AS question_text,
        mcq.correct_answer AS correct_answer,
        mcqr.response AS chosen_option,
        mcqr.score AS marks
    FROM
        multi_choice_question mcq
        LEFT JOIN multi_choice_response mcqr ON mcq.exam_id = mcqr.exam_id AND mcq.question_no = mcqr.question_no AND mcqr.assignee_id = '$userID'
    WHERE
        mcq.exam_id = $examID

    UNION

    SELECT
        'Fill in the blank' AS exam_type,
        fiq.question AS question_text,
        NULL AS correct_answer,
        fir.response AS chosen_option,
        fir.score AS marks
    FROM
        fill_in_question fiq
        LEFT JOIN fill_in_response fir ON fiq.exam_id = fir.exam_id AND fiq.question_no = fir.question_no AND fir.assignee_id = '$userID'
    WHERE
        fiq.exam_id = $examID

    UNION

    SELECT
        'Theory' AS exam_type,
        tq.question AS question_text,
        NULL AS correct_answer,
        tr.response AS chosen_option,
        tr.score AS marks
    FROM
        theory_question tq
        LEFT JOIN theory_response tr ON tq.exam_id = tr.exam_id AND tq.question_no = tr.question_no AND tr.assignee_id = '$userID'
    WHERE
        tq.exam_id = $examID";

// Add the query to retrieve user information and total score
$sql .= "
    UNION
    
    SELECT
        'UserName' AS exam_type,
        '' AS question_text,
        '' AS correct_answer,
        CONCAT(user.first_name, ' ', user.last_name) AS chosen_option,
        exam_assignment.total_score AS total_score
    FROM
        user
        INNER JOIN exam_assignment ON user.user_id = exam_assignment.assignee_id
    WHERE
        exam_assignment.status_id = 6
        AND exam_assignment.exam_id = $examID";

$result = mysqli_query($conn, $sql);

if (!$result) {
    echo mysqli_error($conn);
    exit();
}

// Create the output file (without saving it)
$dataFile = fopen('php://output', 'w');

if ($result->num_rows > 0) {
    // Output the header
    //fputcsv($dataFile, array("QUESTION TYPE", "QUESTION", "CORRECT ANSWER", "CHOSEN OPTION", "SCORE(out of $totalMark)"));
    fputcsv($dataFile, array("QUESTION TYPE", "QUESTION", "CORRECT ANSWER", "CHOSEN OPTION","ID", "FIRST NAME", "LAST NAME" , "SCORE(out of $total_score)"));




  // Output the header
 // fputcsv($dataFile, array("ID", "FIRST NAME", "LAST NAME" ,"SCORE (out of $totalMark)"));


    // Output the data
    while ($row = $result->fetch_assoc()) {
        fputcsv($dataFile, $row);
    }
} else {
    echo "0 results";
}

// Close the file handle
fclose($dataFile);

// Close the database connection
mysqli_close($conn);
?>

[Mod Edit: added ``` quotes to format the code]

Weedpacket

The obvious question to ask is: Where is $total_score defined?

At one point you're adding to your query ... as total_score, but you never use the result of that, and since it's one branch of a UNION the result set you get will only have the columns defined in the first branch:

     'Multi-choice' AS exam_type,
        mcq.question AS question_text,
        mcq.correct_answer AS correct_answer,
        mcqr.response AS chosen_option,
        mcqr.score AS marks

bbanda

Weedpacket

thank you very much, but my head is now ringing $signs ?, < > kikiki. if possible may you please assist by adjusting where necessary because i have been stuck for a couple of days now trying to figure out where i am getting it all wrong

sneakyimp

bbanda may you please assist by adjusting where necessary

You are far more likely to get help if you at least make an attempt to understand your own code. Your error message is quite informative:

Notice: Undefined variable: total_score in C:\wamp64\www\online-exam-portal\code\manage-exam_export-to-csv.php on line98

This tells you that you have a variable, $total_score, that you refer to on line 98 of you code that you have not defined. Before you attempt to access the value in a variable in your PHP script, you must first define it. You have not done so.

Then weedpacket points out some other problems with your code. We cannot simply fix your code because a) it is not clear what your intent is and b) the point of this forum is for you to learn how to write your own code, not for others to do your work for you.

NogDog

For this line where the variable not defined issue is...

fputcsv($dataFile, array("QUESTION TYPE", "QUESTION", "CORRECT ANSWER", "CHOSEN OPTION","ID", "FIRST NAME", "LAST NAME" , "SCORE(out of $total_score)"));

...since you're just outputting the column headers at that point, maybe you just want it to be "SCORE (out of total score)", i.e. not a calculated value in a variable?

bbanda

NogDog

ok from the query i am executing, the results that i want to get are supposed to show this or come out like this.I want to be able to print a hard copy of the exam and store it elsewhere ie showing the type of exam the candidate took, the questions asked, answered, their names and the score they got for that exam. this is the example.

``QUESTION TYPE Question CORRECT ANSWER CHOSEN-OPTION FIRSTNAME LASTNAME SCORE
Multiple-choice what is the full form of Https: D A BEN AFFLECK 80
Multiple-choice where do we find gold B B
Multiple-choice what is the full form of C D```



so i need help to adjust where necessary so that i produce typical results.thank you

pbismad

Your query is selecting five columns - exam_type, question_text, correct_answer, chosen_option, and marks. The currently uncommented csv header in the code has eight columns - "QUESTION TYPE", "QUESTION", "CORRECT ANSWER", "CHOSEN OPTION", "ID", "FIRST NAME", "LAST NAME" , and "SCORE(out of $total_score)". In the user part of the query, the chosen_option column is the concatenation of the first and last name, and the marks column is the total score. If you are going to literally loop over the result from the query and output it, the csv header must have only five columns.

If you want output that is different than this, you must define what you want, then design, write, test, and debug the code needed to produce that output. If you want the csv header to contain a value, which the $total_score php variable implies, you need to produce that value before you output the csv header. If you don't want this value in the header, i.e. this is a copy/paste mistake, you need to remove the variable from the header definition.

If you do want something like the name or total score, you would either need to query for this separately (the user data is not similar to the exam data), or make the UNION ... queries a sub-query and JOIN it with the user and exam_assignment tables to get the user data and total score.

bbanda

thank you very much, let me try that