在PHP MySQL中创建多维关联数组

Question

我在Php MySql方面还是新手，虽然这个问题有很多答案，但我仍然遇到了错误。所以我想做的是循环我的数组并创建一个多维关联数组。

我有evaluation_details、category、segment和question表。在evaluation_details中，我有category、subcategory、segment和question表的id。

而在category中，我有一个名为is_cat的列，其数据类型为tiny int，值为0或1，其中1表示给定类别是主类别，0表示它是子类别，因此我需要创建如下的数组。

在Category中有多个子类别，每个子类别都有一个问题。每个类别可以按段分组。

array:2 [
  0 => array:4 [
    "id" => 2
    "title" => "second evaluation form"
    "emp_position" => "System Architecture"
    "first_segment_name" => array:2 [
      0 => {
        +"category_name" => array 2 [
            0 => {
                +"subcategory" => sub_1
                +"question" => question_1
            },

            1 => {
                +"subcategory" => sub_2
                +"question" => question_2
            }
        ]
      }

      1 => {
        +"category_name" => array 2 [
            0 => {
                +"subcategory" => sub_3
                +"question" => question_3
            },

            1 => {
                +"subcategory" => sub_4
                +"question" => question_4
            }
        ]
      }

    ]

    "second_segment_name" => array:2 [
      0 => {
        +"category_name" => array 3 [
            0 => {
                +"subcategory" => sub_5
                +"question" => question_5
            },

            1 => {
                +"subcategory" => sub_6
                +"question" => question_6
            }

            2 => {
                +"subcategory" => sub_7
                +"question" => question_7
            }
        ]
      }

    ]
  ]

]

我正在尝试做的事情

public function getEvaluation($data){

    $query = DB::table(
        DB::raw("(

                SELECT 
                e.id, 
                e.title,
                p.name position,
                s.name segment
                FROM 
                evaluation_details e_d
                JOIN evaluation e ON e_d.evaluation_id = e.id
                JOIN segment s ON e_d.segment_id = s.id
                JOIN position p ON e.position_id = p.id
                WHERE e.position_id = 2
                ORDER BY segment

        ) As `a`
        ")
    )->get()->toArray();

    $query = json_decode(json_encode($query), true);


    foreach ($query as $index => $data) {
       $first_array[$index]['id'] = $data['id'];
       $first_array[$index]['title'] = $data['title'];
       $first_array[$index]['emp_position'] = $data['position'];

       $first_array[$index]['segments'] = DB::table('evaluation_details AS e_d')
            ->select('c.name AS category')
            ->join('category AS c', 'e_d.category_id', 'c.id')
            ->join('evaluation As e', 'e_d.evaluation_id', '=', 'e.id')
            ->join('segment AS s', 'e_d.segment_id', 's.id')
            ->where('e.position_id', '=', '2')
            ->get()->toArray();   

    }
    echo '<pre>';
    dd($first_array);exit;


}

* 但是它给了我不同的结果，我需要在类别内再有一个数组 *

    array:2 [
  0 => array:4 [
    "id" => 2
    "title" => "first evaluation form"
    "emp_position" => "System Architecture"
    "segments" => array:2 [
      0 => {#497
        +"category": "test"
      }
      1 => {#498
        +"category": "Quality & Dependibility"
      }
    ]
  ]

]

我希望有人能够帮助我解决这个问题。

- user7719185

---

请检查@Bluetree提出的问题。 - Mayank Pandeyz

@MayankPandeyz 我只需要循环我提到的表格，以创建一个类似的数组。 - user7719185

请提供一个 MCVE（最小化、完整的可重现例子），即使对你来说这似乎是一个非常简单的 SQL 查询，也应该这样做。 - Strawberry

这些表的结果包括类别、子类别、段和问题。vardump它们并将其发布在此处。 - Bluetree

请尝试我的答案。并告诉我它是否有效。 - Bluetree

Answer 1

为此，我们需要删除“category”键，并用其“类别名称”替换该键。

在您的foreach循环内部最后添加此内容。

foreach( $first_array[$index]['segments'] as $index2 => $segment){
    $category_name = $segment['category']; //This will output test and resplace the category key
    $first_array[$index]['segments'][$index2][$category_name] = DB::Table...;//Perform your subcategory query here just like what you did in segment.
    unset($first_array[$index]['segments'][$index2]['category']);//Delete the old
}

将您的代码从这个改为：

foreach ($query as $index => $data) {
       $first_array[$index]['id'] = $data['id'];
       $first_array[$index]['title'] = $data['title'];
       $first_array[$index]['emp_position'] = $data['position'];

       $first_array[$index]['segments'] = DB::table('evaluation_details AS e_d')
            ->select('c.name AS category')
            ->join('category AS c', 'e_d.category_id', 'c.id')
            ->join('evaluation As e', 'e_d.evaluation_id', '=', 'e.id')
            ->join('segment AS s', 'e_d.segment_id', 's.id')
            ->where('e.position_id', '=', '2')
            ->get()->toArray();   

    }

转换为：

foreach ($query as $index => $data) {
   $first_array[$index]['id'] = $data['id'];
   $first_array[$index]['title'] = $data['title'];
   $first_array[$index]['emp_position'] = $data['position'];

   $first_array[$index]['segments'] = DB::table('evaluation_details AS e_d')
        ->select('c.name AS category')
        ->join('category AS c', 'e_d.category_id', 'c.id')
        ->join('evaluation As e', 'e_d.evaluation_id', '=', 'e.id')
        ->join('segment AS s', 'e_d.segment_id', 's.id')
        ->where('e.position_id', '=', '2')
        ->get()->toArray();   
    foreach( $first_array[$index]['segments'] as $index2 => $segment){
        $category_name = $segment['category']; //This will output test and resplace the category key
        $first_array[$index]['segments'][$index2][$category_name] = DB::Table...;//Perform your subcategory query here just like what you did in segment.
        unset($first_array[$index]['segments'][$index2]['category']);//Delete the old
    }
}

- Bluetree

---

这是因为您帮助了我 :))) - user7719185