Databases
StackOverflow2013
Postsdbo
POPHP/mySQL: Import data and store in hierarchical nested set for use with jsTree
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP/mySQL: Import data and store in hierarchical nested set for use with jsTree
    text
    copied!<p>I'm using jsTree to view hierarchical data that is stored in a mySQL database as a nested set (left, right, level, etc.). This is working fine, but I need to allow users to import data by uploading a CSV file. When they do so, any existing data in the table will be removed so I don't have to worry about updating the left/right fields.</p> <p>The data they will be uploading will be in this format:</p> <pre><code>"Code","Title" "100","Unit 100" "200","Unit 200" "101","Task 101: This is a task" "102","Task 102: Another task" "201","Task 201: Yet another" "300","Unit 300" "301","Task 301: Another one" </code></pre> <p>Everything will be a child of a main "Group" that is a level 1 node. All of the "codes" divisible by 100 (ie. 100, 200, 300) will be level 2 (parent nodes.. children of "Group"). All others will be level 3 (child) nodes of their respective parent nodes (ie. 101 and 102 are children of 100, 201 is a child of 200, etc.)</p> <p>The resulting table in mySQL should look like this:</p> <pre><code>id parent_id position left right level title 1 0 0 1 18 0 ROOT 2 1 0 2 17 1 Group 3 2 0 3 8 2 Unit 100 4 2 1 9 12 2 Unit 200 5 3 0 4 5 3 Task 101: This is a task 6 3 1 6 7 3 Task 102: Another task 7 4 0 10 11 3 Task 201: Yet another 8 2 2 13 16 2 Unit 300 9 8 0 14 15 3 Task 301: Another one </code></pre> <p>The tree would then look like this:</p> <p><img src="https://i.stack.imgur.com/RvmV8.png" alt="tree"></p> <p>My question is: using PHP, what is the best method to accomplish this? I already have code in place that pulls the data contained in the uploaded CSV file and stores it in an array, but I'm not sure what the logic to convert this to a nested set should look like. </p> <p>Right now, the data is stored in a 2-dimensional array called $data (in the format $data[$col][$row]):</p> <pre><code>$data[0][0] = "Code"; $data[0][1] = "100"; $data[0][2] = "200"; $data[0][3] = "101"; $data[0][4] = "102"; $data[0][5] = "201"; $data[0][6] = "300"; $data[0][7] = "301"; $data[1][0] = "Title"; $data[1][1] = "Unit 100"; $data[1][2] = "Unit 200"; $data[1][3] = "Task 101: This is a task"; $data[1][4] = "Task 102: Another task"; $data[1][5] = "Task 201: Yet another"; $data[1][6] = "Unit 300"; $data[1][7] = "Task 301: Another one"; Array ( [0] =&gt; Array ( [0] =&gt; Code [1] =&gt; 100 [2] =&gt; 200 [3] =&gt; 101 [4] =&gt; 102 [5] =&gt; 201 [6] =&gt; 300 [7] =&gt; 301 ) [1] =&gt; Array ( [0] =&gt; Title [1] =&gt; Unit 100 [2] =&gt; Unit 200 [3] =&gt; Task 101: This is a task [4] =&gt; Task 102: Another task [5] =&gt; Task 201: Yet another [6] =&gt; Unit 300 [7] =&gt; Task 301: Another one ) ) </code></pre> <p>Any help would be very much appreciated. I now have the parent_id, position, and level being calculated correctly... I just need to figure out the left/right part. Here is the code I'm currently using (thanks for getting me started Matteo):</p> <pre><code>$rows = array(); // insert ROOT row $rows[] = array( 'id' =&gt; 1, 'parent_id' =&gt; 0, 'position' =&gt; 0, 'left' =&gt; 1, 'right' =&gt; 10000, // just a guess, will need updated later 'level' =&gt; 0, 'title' =&gt; 'ROOT', ); echo "&lt;br&gt;"; print_r($rows[0]); // insert group row $rows[] = array( 'id' =&gt; 2, 'parent_id' =&gt; 1, 'position' =&gt; 0, 'left' =&gt; 2, 'right' =&gt; 9999, // just a guess, will need updated later 'level' =&gt; 1, 'title' =&gt; 'Group', ); echo "&lt;br&gt;"; print_r($rows[1]); // next ID to be used $id = 3; // keep track of code =&gt; ID correspondence $map = array(); // parse data for ($i = 1, $c = count($data[0]); $i &lt; $c; ++$i) { // save ID in the map $map[$data[0][$i]] = $id; // initialize the current row $row = array( 'id' =&gt; $id, 'parent_id' =&gt; 1, 'position' =&gt; 0, 'left' =&gt; 0, 'right' =&gt; 0, 'level' =&gt; 1, 'title' =&gt; $data[1][$i], ); // if the code is multiple of 100 if ($data[0][$i] % 100 == 0) { $row['parent_id'] = 2; $row['level'] = 2; $row['position'] = (floor($data[0][$i] / 100)) - 1; } else { // get parent id from map $row['parent_id'] = $map[floor($data[0][$i] / 100) * 100]; $row['level'] = 3; $row['position'] = $data[0][$i] % 100; } // add the row $rows[] = $row; ++$id; echo "&lt;br&gt;"; print_r($row); } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload