Mac Taylor
Reputation: 5148
Selecting Array from multidimensional array and save into db table
I am trying to list and then insert into a SQL table from this array :
SQL table :
id time average_position search_visibility_index click_potential
data is :
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
)
[7] => Array
(
[0] => 2020-03-25
[1] => 36.08
)
[8] => Array
(
[0] => 2020-03-26
[1] => 35.87
)
[9] => Array
(
[0] => 2020-03-27
[1] => 35.53
)
[10] => Array
(
[0] => 2020-03-28
[1] => 34.6
)
[11] => Array
(
[0] => 2020-03-29
[1] => 34.19
)
[12] => Array
(
[0] => 2020-03-30
[1] => 34.11
)
[13] => Array
(
[0] => 2020-03-31
[1] => 34.08
)
[14] => Array
(
[0] => 2020-04-01
[1] => 33.09
)
[15] => Array
(
[0] => 2020-04-02
[1] => 33.03
)
[16] => Array
(
[0] => 2020-04-03
[1] => 32.96
)
[17] => Array
(
[0] => 2020-04-04
[1] => 32.64
)
[18] => Array
(
[0] => 2020-04-05
[1] => 33.08
)
[19] => Array
(
[0] => 2020-04-06
[1] => 33.03
)
[20] => Array
(
[0] => 2020-04-07
[1] => 32.83
)
[21] => Array
(
[0] => 2020-04-08
[1] => 33.02
)
[22] => Array
(
[0] => 2020-04-09
[1] => 32.75
)
[23] => Array
(
[0] => 2020-04-10
[1] => 32.27
)
[24] => Array
(
[0] => 2020-04-11
[1] => 32.18
)
[25] => Array
(
[0] => 2020-04-12
[1] => 30.64
)
[26] => Array
(
[0] => 2020-04-13
[1] => 30.6
)
[27] => Array
(
[0] => 2020-04-14
[1] => 30.56
)
[28] => Array
(
[0] => 2020-04-15
[1] => 30.53
)
[29] => Array
(
[0] => 2020-04-16
[1] => 30.1
)
[30] => Array
(
[0] => 2020-04-17
[1] => 29.86
)
)
)
Array
(
[name] => search_visibility_index
[series] => Array
(
[0] => Array
(
[0] => 2020-03-18
[1] => 5.14
)
[1] => Array
(
[0] => 2020-03-19
[1] => 5.14
)
[2] => Array
(
[0] => 2020-03-20
[1] => 5.36
)
[3] => Array
(
[0] => 2020-03-21
[1] => 5.44
)
[4] => Array
(
[0] => 2020-03-22
[1] => 5.01
)
[5] => Array
(
[0] => 2020-03-23
[1] => 5
)
[6] => Array
(
[0] => 2020-03-24
[1] => 5.01
)
[7] => Array
(
[0] => 2020-03-25
[1] => 5.03
)
[8] => Array
(
[0] => 2020-03-26
[1] => 4.07
)
[9] => Array
(
[0] => 2020-03-27
[1] => 5.23
)
[10] => Array
(
[0] => 2020-03-28
[1] => 4.48
)
[11] => Array
(
[0] => 2020-03-29
[1] => 4.57
)
[12] => Array
(
[0] => 2020-03-30
[1] => 5.72
)
[13] => Array
(
[0] => 2020-03-31
[1] => 5.66
)
[14] => Array
(
[0] => 2020-04-01
[1] => 5.9
)
[15] => Array
(
[0] => 2020-04-02
[1] => 5.99
)
[16] => Array
(
[0] => 2020-04-03
[1] => 6.1
)
[17] => Array
(
[0] => 2020-04-04
[1] => 6.01
)
[18] => Array
(
[0] => 2020-04-05
[1] => 6.09
)
[19] => Array
(
[0] => 2020-04-06
[1] => 5.95
)
[20] => Array
(
[0] => 2020-04-07
[1] => 5.99
)
[21] => Array
(
[0] => 2020-04-08
[1] => 6.04
)
[22] => Array
(
[0] => 2020-04-09
[1] => 5.96
)
[23] => Array
(
[0] => 2020-04-10
[1] => 6.15
)
[24] => Array
(
[0] => 2020-04-11
[1] => 5.29
)
[25] => Array
(
[0] => 2020-04-12
[1] => 5.6
)
[26] => Array
(
[0] => 2020-04-13
[1] => 6.64
)
[27] => Array
(
[0] => 2020-04-14
[1] => 6.62
)
[28] => Array
(
[0] => 2020-04-15
[1] => 6.61
)
[29] => Array
(
[0] => 2020-04-16
[1] => 6.66
)
[30] => Array
(
[0] => 2020-04-17
[1] => 5.53
)
)
)
Array
(
[name] => click_potential
[series] => Array
(
[0] => Array
(
[0] => 2020-03-18
[1] => 70.73
)
[1] => Array
(
[0] => 2020-03-19
[1] => 70.74
)
[2] => Array
(
[0] => 2020-03-20
[1] => 73.77
)
[3] => Array
(
[0] => 2020-03-21
[1] => 74.89
)
[4] => Array
(
[0] => 2020-03-22
[1] => 74.35
)
[5] => Array
(
[0] => 2020-03-23
[1] => 74.19
)
[6] => Array
(
[0] => 2020-03-24
[1] => 74.34
)
[7] => Array
(
[0] => 2020-03-25
[1] => 74.71
)
[8] => Array
(
[0] => 2020-03-26
[1] => 60.46
)
[9] => Array
(
[0] => 2020-03-27
[1] => 77.58
)
[10] => Array
(
[0] => 2020-03-28
[1] => 66.51
)
[11] => Array
…
Question is how to save these data with single mysql query as there are multiple arrays. But all of them are based on fixed date time queries.
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]]
);
Upvotes: 0
Views: 54
Answers (1)
Daniel Protopopov
Reputation: 7236
You will need to bring the array to the format in the example. If the dates present and are the same for all arrays, the task is quite trivial - each entry for the date must contain three entries - 'average_position', 'search_visibility_index' and 'click_potential' with corresponding values.
This means that you have to go through each array, take the date from element #0, use it as a key, and add the corresponding array key with its value under element 1.
<?php
$yourFirstArray = ['name' => 'average_position', 'series' => [['2020-03-18', 36.27], ['2020-03-19', 36.23]];
$yourSecondArray = ['name' => 'search_visibility_index', 'series' => [['2020-03-18', 5.14], ['2020-03-19', 5.14]];
$yourThirdArray = ['name' => 'click_potential', 'series' => [['2020-03-18', 70.73], ['2020-03-19', 70.74]];
$average_position_series = $yourFirstArray['series'];
$search_visibility_index_series = $yourSecondArray['series'];
$click_potential_series = $yourThirdArray['series'];
$data = [];
foreach($average_position_series as list($date, $value)) {
$data[$date]['average_position'] = $value;
}
foreach($search_visibility_index_series as list($date, $value)) {
$data[$date]['search_visibility_index'] = $value;
}
foreach($click_potential_series as list($date, $value)) {
$data[$date]['click_potential'] = $value;
}
DB::table('mytable')->insert($data);
Upvotes: 1
Related Questions
- Saving array of data in multiple rows in laravel
- Laravel, save data from array to database
- Save data from an Associative Array to the database
- Laravel - How to save an Array to Database
- Laravel eloquent how to save multidimensional array
- how to save the array values as each record in mysql database
- saving array of array into database laravel
- PHP How to save data from array to mysql using laravel 5
- PHP / Laravel: How to save a multidimensional array into the DB
- Laravel Multidimensional array from database