从多维数组中选择数组并保存到数据库表中

Question

我试图列出然后从这个数组插入到 SQL 表中：

SQL表：

id  time    average_position    search_visibility_index click_potential  

数据是：

Array

(

    [name] => average_position

    [series] => Array

        (

            [0] => Array

                (

                    [0] => 2020-03-18

                    [1] => 36.27

                )

            [1] => Array

                (

                    [0] => 2020-03-19

                    [1] => 36.23

                )

            [2] => Array

                (

                    [0] => 2020-03-20

                    [1] => 36.18

                )

            [3] => Array

                (

                    [0] => 2020-03-21

                    [1] => 36.07

                )

            [4] => Array

                (

                    [0] => 2020-03-22

                    [1] => 36.53

                )

            [5] => Array

                (

                    [0] => 2020-03-23

                    [1] => 36.65

                )

            [6] => Array

                (

                    [0] => 2020-03-24

                    [1] => 36.21

                )

           

问题是如何使用单个 mysql 查询保存这些数据，因为有多个数组。但所有这些都是基于固定日期时间查询。

foreach($contents_series as $avg_array){

DB::table('data_series')->insert(

    ['time' =>$avg_array[0],'average_position' =>$avg_array[0]['url_series'][0]['series'][1]]

);

Answer 1

您需要将数组转换为示例中的格式。如果日期存在并且所有数组都相同，则任务非常简单 - 日期的每个条目必须包含三个条目 - 'average_position'、'search_visibility_index' 和 'click_potential' 以及相应的值。这意味着您必须遍历每个数组，从元素 #0 中获取日期，将其用作键，并在元素 1 下添加相应的数组键及其值。&nbsp;<?php&nbsp;$yourFirstArray = ['name' => 'average_position', 'series' => [['2020-03-18', 36.27], ['2020-03-19', 36.23]];&nbsp;$yourSecondArray = ['name' => 'search_visibility_index', 'series' => [['2020-03-18', 5.14], ['2020-03-19', 5.14]];&nbsp;$yourThirdArray = ['name' => 'click_potential', 'series' => [['2020-03-18', 70.73], ['2020-03-19', 70.74]];&nbsp;$average_position_series = $yourFirstArray['series'];&nbsp;$search_visibility_index_series = $yourSecondArray['series'];&nbsp;$click_potential_series = $yourThirdArray['series'];&nbsp;$data = [];&nbsp;foreach($average_position_series as list($date, $value)) {&nbsp; &nbsp; &nbsp;$data[$date]['average_position'] = $value;&nbsp;}&nbsp;foreach($search_visibility_index_series as list($date, $value)) {&nbsp; &nbsp; &nbsp;$data[$date]['search_visibility_index'] = $value;&nbsp;}&nbsp;foreach($click_potential_series as list($date, $value)) {&nbsp; &nbsp; &nbsp;$data[$date]['click_potential'] = $value;&nbsp;}&nbsp;DB::table('mytable')->insert($data);