Home > Article > Backend Development > mysql - php excel import Import three-level classification How should the table be designed to get the desired data format?
mysql - php excel import Import three-level classification How should the table be designed to get the desired data format?
- WBOYOriginal
- 2016-08-04 09:19:231338browse
There are multiple first-level classifications with uncertain second-level classifications. Below the second-level classifications, there are uncertain third-level classifications. I want to get relevant data combinations and write them into the database, but I don’t know how to arrange this format to get the right data.
If you have time, please take a look and help. Thank you
Is there a way to get the value of the entire column and then distinguish 2 columns and then 3 columns based on the length
The data format obtained now is 1 2 3 by row. . .
Picture above
<code>array (size=16)
0 =>
array (size=7)
0 => float 1
1 => string '销售部' (length=9)
2 => string '销售1区' (length=10)
3 => string '销售1科室' (length=13)
4 => null
5 => null
6 => null
1 =>
array (size=7)
0 => float 2
1 => null
2 => null
3 => string '销售2科室' (length=13)
4 => null
5 => null
6 => null
2 =>
array (size=7)
0 => float 3
1 => null
2 => null
3 => string '销售3科室' (length=13)
4 => null
5 => null
6 => null
3 =>
array (size=7)
0 => float 4
1 => null
2 => null
3 => string '销售4科室' (length=13)
4 => null
5 => null
6 => null
4 =>
array (size=7)
0 => float 5
1 => null
2 => string '销售2区' (length=10)
3 => string '销售1科室' (length=13)
4 => null
5 => null
6 => null
5 =>
array (size=7)
0 => float 6
1 => null
2 => null
3 => string '销售2科室' (length=13)
4 => null
5 => null
6 => null
6 =>
array (size=7)
0 => float 7
1 => null
2 => null
3 => string '销售3科室' (length=13)
4 => null
5 => null
6 => null
7 =>
array (size=7)
0 => float 8
1 => null
2 => null
3 => string '销售4科室' (length=13)
4 => null
5 => null
6 => null
8 =>
array (size=7)
0 => float 9
1 => string '市场部' (length=9)
2 => string '市场1区' (length=10)
3 => string '市场1科室' (length=13)
4 => null
5 => null
6 => null
9 =>
array (size=7)
0 => float 10
1 => null
2 => null
3 => string '市场2科室' (length=13)
4 => null
5 => null
6 => null
10 =>
array (size=7)
0 => float 11
1 => null
2 => null
3 => string '市场3科室' (length=13)
4 => null
5 => null
6 => null
11 =>
array (size=7)
0 => float 12
1 => null
2 => null
3 => string '市场4科室' (length=13)
4 => null
5 => null
6 => null
12 =>
array (size=7)
0 => float 13
1 => null
2 => string '市场2区' (length=10)
3 => string '市场1科室' (length=13)
4 => null
5 => null
6 => null
13 =>
array (size=7)
0 => float 14
1 => null
2 => null
3 => string '市场2科室' (length=13)
4 => null
5 => null
6 => null
14 =>
array (size=7)
0 => float 15
1 => null
2 => null
3 => string '市场3科室' (length=13)
4 => null
5 => null
6 => null
15 =>
array (size=7)
0 => float 16
1 => null
2 => null
3 => string '市场4科室' (length=13)
4 => null
5 => null
6 => null</code>
Reply content:
There are multiple first-level classifications with uncertain second-level classifications. Below the second-level classifications, there are uncertain third-level classifications. I want to get relevant data combinations and write them into the database, but I don’t know how to arrange this format to get the right data.
If you have time, please take a look and help. Thank you
Is there a way to get the value of the entire column and then distinguish 2 columns and then 3 columns based on the length
The data format obtained now is 1 2 3 by row. . .
Picture above
<code>array (size=16)
0 =>
array (size=7)
0 => float 1
1 => string '销售部' (length=9)
2 => string '销售1区' (length=10)
3 => string '销售1科室' (length=13)
4 => null
5 => null
6 => null
1 =>
array (size=7)
0 => float 2
1 => null
2 => null
3 => string '销售2科室' (length=13)
4 => null
5 => null
6 => null
2 =>
array (size=7)
0 => float 3
1 => null
2 => null
3 => string '销售3科室' (length=13)
4 => null
5 => null
6 => null
3 =>
array (size=7)
0 => float 4
1 => null
2 => null
3 => string '销售4科室' (length=13)
4 => null
5 => null
6 => null
4 =>
array (size=7)
0 => float 5
1 => null
2 => string '销售2区' (length=10)
3 => string '销售1科室' (length=13)
4 => null
5 => null
6 => null
5 =>
array (size=7)
0 => float 6
1 => null
2 => null
3 => string '销售2科室' (length=13)
4 => null
5 => null
6 => null
6 =>
array (size=7)
0 => float 7
1 => null
2 => null
3 => string '销售3科室' (length=13)
4 => null
5 => null
6 => null
7 =>
array (size=7)
0 => float 8
1 => null
2 => null
3 => string '销售4科室' (length=13)
4 => null
5 => null
6 => null
8 =>
array (size=7)
0 => float 9
1 => string '市场部' (length=9)
2 => string '市场1区' (length=10)
3 => string '市场1科室' (length=13)
4 => null
5 => null
6 => null
9 =>
array (size=7)
0 => float 10
1 => null
2 => null
3 => string '市场2科室' (length=13)
4 => null
5 => null
6 => null
10 =>
array (size=7)
0 => float 11
1 => null
2 => null
3 => string '市场3科室' (length=13)
4 => null
5 => null
6 => null
11 =>
array (size=7)
0 => float 12
1 => null
2 => null
3 => string '市场4科室' (length=13)
4 => null
5 => null
6 => null
12 =>
array (size=7)
0 => float 13
1 => null
2 => string '市场2区' (length=10)
3 => string '市场1科室' (length=13)
4 => null
5 => null
6 => null
13 =>
array (size=7)
0 => float 14
1 => null
2 => null
3 => string '市场2科室' (length=13)
4 => null
5 => null
6 => null
14 =>
array (size=7)
0 => float 15
1 => null
2 => null
3 => string '市场3科室' (length=13)
4 => null
5 => null
6 => null
15 =>
array (size=7)
0 => float 16
1 => null
2 => null
3 => string '市场4科室' (length=13)
4 => null
5 => null
6 => null</code>
You don’t need me to teach you how to read the data in Excel into a two-dimensional array. Let’s go directly to the code.
<code><?php
$lines = [
['销售部', '销售1区', '销售1科室'],
['', '', '销售2科室'],
['', '', '销售3科室'],
['', '', '销售4科室'],
['', '销售2区', '销售1科室'],
['', '', '销售2科室'],
['', '', '销售3科室'],
['', '', '销售4科室'],
['市场部', '市场1区', '市场1科室'],
['', '', '市场2科室'],
['', '', '市场3科室'],
['', '', '市场4科室'],
['', '市场2区', '市场1科室'],
['', '', '市场2科室'],
['', '', '市场3科室'],
['', '', '市场4科室'],
];
$formatedData = [];
foreach ($lines as $line) {
if (!empty($line[0])) {
$level1 = $line[0];
$formatedData[$level1] = [];
}
if (!empty($line[1])) {
$level2 = $line[1];
$formatedData[$level1][$level2] = [];
}
if (!empty($line[2])) {
$level3 = $line[2];
$formatedData[$level1][$level2][] = $level3;
}
}
print_r($formatedData);</code>
The result of running the program is:
Array
(
<code>[销售部] => Array
(
[销售1区] => Array
(
[0] => 销售1科室
[1] => 销售2科室
[2] => 销售3科室
[3] => 销售4科室
)
[销售2区] => Array
(
[0] => 销售1科室
[1] => 销售2科室
[2] => 销售3科室
[3] => 销售4科室
)
)
[市场部] => Array
(
[市场1区] => Array
(
[0] => 市场1科室
[1] => 市场2科室
[2] => 市场3科室
[3] => 市场4科室
)
[市场2区] => Array
(
[0] => 市场1科室
[1] => 市场2科室
[2] => 市场3科室
[3] => 市场4科室
)
)
</code>
)
My method starts from the classification column and marks cat1, cat2, cat3, and then uses a for loop in the background to come out
It is correct to obtain the data in the desired format according to the adopted method. However, it is still troublesome to judge some of the data being operated, or it may not be possible to judge. For example, it is impossible to judge whether its parent exists in a three-level classification. I can only judge whether there is a subset, then add a new one. Overall, I feel that the format of this requirement still needs to be adjusted. There are some problems. I may not be able to use this format because the internal use requirements are not so strict.