如何用PHP从数据库获取问题时将答案数组编码到JSON对象中

Got it, let's tweak your code to output the exact JSON format you're targeting. The core issue right now is that GROUP_CONCAT returns a comma-separated string of answers, but we need to turn that into a proper PHP array before encoding to JSON.

Here's the revised, working code with breakdowns:

require_once("connect.php");

// Switch to explicit JOIN syntax for better readability (best practice)
$sql = "SELECT 
            questions.question, 
            GROUP_CONCAT(answers.answer) AS answers, 
            questions.correct 
        FROM questions
        JOIN answers ON answers.questionID = questions.questionID 
        GROUP BY questions.questionID, questions.question, questions.correct";

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

$output = [];

if (mysqli_num_rows($result) > 0) {
    // Loop through each result row
    while ($row = mysqli_fetch_assoc($result)) {
        // Split the comma-separated answers string into a PHP array
        $answersArray = explode(',', $row['answers']);
        
        // Build the entry matching your desired JSON structure
        $output[] = [
            'question' => $row['question'],
            'answers' => $answersArray,
            'correct' => (int)$row['correct'] // Ensure correct is a numeric value, not string
        ];
    }
}

// Encode the array to JSON and print it
echo json_encode($output);

// Clean up the database connection
mysqli_close($conn);

Key Adjustments Explained: #

• SQL JOIN Improvement: Swapped the comma-separated table list for an explicit JOIN to make the query logic clearer. Also updated the GROUP BY clause to include all non-aggregated columns—this avoids errors in strict SQL modes.
• String-to-Array Conversion: Used explode(',', $row['answers']) to split the concatenated answer string into a proper array, which will translate directly to a JSON array.
• Type Casting: Cast $row['correct'] to an integer to ensure the JSON output has a numeric value instead of a string (matching your expected result).
• Structured Output: We build an array of associative arrays that mirrors your target JSON structure, then use json_encode to convert it to valid JSON.

Quick Notes: #

• If your answers could ever contain commas (unlikely, but possible), change the GROUP_CONCAT separator to something unique like GROUP_CONCAT(answers.answer SEPARATOR '|') and explode on '|' instead.
• Double-check that your correct database column is an integer type—this ensures the type casting works smoothly.

内容的提问来源于stack exchange，提问作者Bradley