mysql - php excel import Import three-level classification How should the table be designed to get the desired data format?

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

Data printed on

<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

Data printed on

<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.