StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Given your <code>$data</code> array, you could parse it like this:</p> <pre><code>// this will contain all the rows to be inserted in your DB $rows = array(); // insert ROOT row $rows[0] = array( 'id' => 1, 'parent_id' => 0, 'position' => 0, 'level' => 0, 'left' => 1, 'right' => 10000, 'title' => 'ROOT', ); // insert group row $rows[1] = array( 'id' => 2, 'parent_id' => 1, 'position' => 0, 'level' => 1, 'left' => 2, 'right' => 9999, 'title' => 'Group', ); // keep trace of code => ID correspondence $map = array(); // next ID to be used $id = 3; // keep father => sons relationship $tree = array(); // keep trace of code => row index correspondence $indexes = array(); // next row index $index = 2; // parse your data for ($i = 1, $c = count($data[0]); $i < $c; ++$i) { // current code $code = $data[0][$i]; // save ID in the map $map[$code] = $id; // update the indexes map $indexes[$code] = $index; // prepare the current row $row = array( 'id' => $id, 'title' => $data[1][$i], ) // get the value of code mod 100 $mod = $code % 100; // if the code is multiple of 100 if ($mod == 0) { // the parent_id is 2 $row['parent_id'] = 2; // it is level two $row['level'] = 2; // compute position $row['position'] = floor($code / 100) - 1; } else { // get the parent code $parent = floor($code / 100) * 100; // get parent id from map using parent code $row['parent_id'] = $map[$parent]; // it is level three $row['level'] = 3; // save position $row['position'] = $mod; // save in relationship tree $tree[$parent][] = $code; } // add the row $rows[$index] = $row; // prepare next id ++$id; // update row index ++$index; } // sort the relationship tree base on the parent code (key) ksort($tree, SORT_NUMERIC); // next left value $left = 3; // now, using the relationship tree, assign left and right foreach ($tree as $parent => $sons) { // calculate parent left value $parentLeft = $left; // prepare next left value ++$left; // to be sure that the sons are in order sort($sons, SORT_NUMERIC); // assign values to sons foreach ($sons as $son) { // index in the rows array $index = $indexes[$son]; // set left value $rows[$index]['left'] = $left; // set right value $rows[$index]['right'] = $left + 1; // increment left value $left += 2; } // calculate parent right value $parentRight = $left; // prepare next left value ++$left; // index of parent in the rows array $index = $indexes[$parent]; // set the values $rows[$index]['left'] = $parentLeft; $rows[$index]['right'] = $parentRight; } // update group row right value $rows[1]['right'] = $left; // prepare next left value ++$left; // update root row right value $rows[0]['right'] = $left; </code></pre> <p>At this point, you can insert all the rows one at a time.</p> <p>EDIT: now the script should handle all the required values correctly.</p>

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