PHP从SQL查询构建包含子问题数组的问卷数组问题求助

Hey there! Let's work through this issue together—your goal is totally achievable, we just need to adjust how you're building the array to avoid duplicates and get the nested structure right.

What's Going Wrong in Your Current Code? #

Your SQL query is actually fine (it correctly joins questionnaires to their questions), but the PHP logic has two key issues:

1. You're mixing numeric indexes (when adding new questionnaires with []) and questionnaire ID indexes (when trying to add questions), which creates duplicate questionnaire entries.
2. When adding a new question to an existing questionnaire, you're appending a new element to the questionnaire array instead of pushing the question into the questionnaire's questions sub-array.

Corrected PHP Code #

Here's the revised code that will build the nested structure you want:

// Initialize the main questionnaires array
$data['questionnaires'] = [];

while ($row = $conn->fetch()) {
    $questionnaireId = $row['QuestionnaireId'];
    
    // Check if we've already added this questionnaire to our array
    if (!isset($data['questionnaires'][$questionnaireId])) {
        // If not, create the questionnaire entry with an empty questions array
        $data['questionnaires'][$questionnaireId] = [
            'id' => $questionnaireId,
            'title' => $row['QuestionnaireTitle'],
            'questions' => []
        ];
    }
    
    // Add the current question to the questionnaire's questions sub-array
    $data['questionnaires'][$questionnaireId]['questions'][] = [
        'id' => $row['QuestionId'],
        'text' => $row['Question']
    ];
}

// Optional: Convert the associative array (keyed by questionnaire ID) to a numeric indexed array
// This matches the exact structure you showed in your example
$data['questionnaires'] = array_values($data['questionnaires']);

How This Works #

1. Track Questionnaires by ID: We use the questionnaire's ID as a temporary key in the array. This lets us instantly check if we've already created an entry for that questionnaire, so we never duplicate it.
2. Initialize Empty Questions Array: When creating a new questionnaire entry, we start with an empty questions array to hold all its related questions.
3. Append Questions to the Right Place: Every time we loop through a row, we push the question directly into the corresponding questionnaire's questions array—no more weird extra elements in your structure.
4. Convert to Indexed Array (Optional): The array_values() call takes the associative array (keyed by ID) and turns it into a standard numeric indexed array, which matches the format you provided in your example.

Expected Output #

After running this code, your JSON will look exactly like you want:

{
  "questionnaires": [
    {
      "id": "1",
      "title": "Are you hungry?",
      "questions": [
        {"id": "1", "text": "How is your passion? "},
        {"id": "2", "text": "Do you drink?"}
      ]
    },
    {
      "id": "2",
      "title": "How are you feeling?",
      "questions": [
        {"id": "1", "text": "How is your passion? "},
        {"id": "3", "text": "Do you like fish?"}
      ]
    },
    {
      "id": "5",
      "title": "Is testing working?",
      "questions": [
        {"id": "4", "text": "How is the testing?"}
      ]
    }
  ]
}

Quick Tips for Future Reference #

• Always use unique identifiers (like IDs) as temporary keys when grouping related data from SQL joins—it's the easiest way to avoid duplicates.
• Double-check where you're appending elements in nested arrays: make sure you're targeting the sub-array you want, not the parent array.

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