阅读excel数据并以下面的格式插入

Question

I have data in excel sheet which looks like below.

I have to read the data(i'll try csv format, please suggest otherwise). Using PHP to read data...

Create two tables, hopefully like below.

1.

2.

1. I am expecting ExtJs tree like this.

I am having trouble in step 1. How shall I read my excel sheet, csv file so that database is updated like table 1

Answer 1

Here's a solution, though I did not maintain the same Data_Id's as you did (you seem to increment the id by depth and node, but I just used the row number).

<?php
$data = <<<EOT
Eatables,,
,Fruits,
,,Apple
,,Mango
,Vegetables,
,,Tomatoes
,,Potatoes
EOT;

$mysqli = new mysqli("localhost", "username", "password", "test");

$dataInsertStatement = $mysqli->prepare("INSERT INTO Data (Data_Id, Data_Value) VALUES (?, ?)");
$treeInsertStatement = $mysqli->prepare("INSERT INTO Tree (parent_id, child_id) VALUES (?, ?)");

$lines = explode("
", $data);

$path = array();

foreach ($lines as $rowNum => $line) {
    // convert line of csv to array
    $row = str_getcsv($line);
    // loop through the row's fields and find the one that's not empty
    foreach ($row as $column => $value) {
        if (!empty($value)) {
            // use row number + 1 as $Data_Id
            $Data_Id = $rowNum + 1;

            // track our depth in the tree
            $path[$column] = $Data_Id;

            // insert the data
            $dataInsertStatement->bind_param('is', $Data_Id, $value);
            $dataInsertStatement->execute();

            // check if this node has a parent
            if (isset($path[$column - 1])) {
                // connect this node to its parent in the tree
                $treeInsertStatement->bind_param('ii', $path[$column - 1], $Data_Id);
                $treeInsertStatement->execute();
            }

            continue;
        }
    }
}

$mysqli->close();