Sorting / placing by moving items via Drag and Drop

I have some difficulties to implement a tree with the bility to sort / place elements manualy via drag and drop.

For instance take a book with chapters and pages.

Chapter 1
L Page 1
L Page 2
L Page 3
L Page 4

Now I like to reorganize the pages via drag and drop.

I like to drag Page 4 and drop it between Page 1 and Page 2 so that the new order is:

Chapter 1
L Page 1
L Page 4
L Page 2
L Page 3

It’s no problem to do this on client side.

My problem is, to make the tree (or treegrid) to store this in the database. (On server side)

Is there anybody who can help me with a solution for this?

You may use dataprocessor - the js extension that listens to changes in tree and sends them to server. The request contains parent id, item id, item index and item label and also the type of request: inserted, updated ir deleted.

Please have a look at the sample in tree package:


Moreover, there are server-side connector: … ndex.shtml

Or you may use any other approach. Item index can be got by getIndexById method:

tree.attachEvent("onDrop",function(sourceId,targetId){ var ind = this.getIndexById(sourceId); ... })

Ok. - I got it working now.

As there are so many views to my question here, I like to post the example with that I did it now:

Table Export with Structure and Sample Data:

  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(20) NOT NULL DEFAULT '0',
  `sorting` bigint(20) NOT NULL DEFAULT '0',
  `name` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)

INSERT INTO `test` (`id`, `parent_id`, `sorting`, `name`) VALUES
(1, 0, 0, 'Page 1'),
(2, 0, 1, 'Page 2'),
(3, 0, 2, 'Page 3'),
(4, 0, 3, 'Page 4'),
(5, 0, 4, 'Page 5'),
(6, 0, 5, 'Page 6');

Client side code (test.html):

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <meta http-equiv="content-type" content="text/html; charset=utf-8">
    <script src="common/dhtmlx.js"></script>
    <script src="codebase/dhtmlxdataprocessor_debug.js"></script>
    <script src="codebase/connector.js"></script>
    <link rel="stylesheet" href="common/dhtmlx.css" type="text/css" charset="utf-8">


  <div id="treeboxbox_tree" style="width:250px; height:218px;background-color:#f5f5f5;border :1px solid Silver;; overflow:auto;"></div>

    tree = new dhtmlXTreeObject("treeboxbox_tree", "100%", "100%", 0);

    myDataProcessor = new dataProcessor("tree/test_connector.php");


Server side code (test_connector.php):


  $tree = new TreeConnector($res, "MySQL");

  function custom_sort($sorted_by)

  function my_update($action)
     $sql = "SELECT sorting FROM test WHERE id = {$action->get_id()};";
     $query =mysql_query($sql);     
     $row = mysql_fetch_row($query);

     if($row[0] < $action->get_value('tr_order'))
          $sql = "UPDATE test SET sorting = sorting + 1 WHERE parent_id = {$action->get_value('parent_id')} AND sorting > {$action->get_value('tr_order')};";
          $sql = "UPDATE test SET sorting = sorting + 1 WHERE parent_id = {$action->get_value('parent_id')} AND sorting >= {$action->get_value('tr_order')};";
     if($row[0] < $action->get_value('tr_order'))
       $sql = "UPDATE test SET sorting = {$action->get_value('tr_order')} + 1 WHERE id = {$action->get_id()};";
       $sql = "UPDATE test SET sorting = {$action->get_value('tr_order')} WHERE id = {$action->get_id()};";
     $sql = "SET @new_sorting := -1;";
     $sql = "UPDATE test SET sorting = (@new_sorting := @new_sorting + 1) WHERE parent_id = {$action->get_value('parent_id')} ORDER BY sorting;";

  $tree->event->attach("beforeSort",   "custom_sort");     
    $tree->event->attach("beforeUpdate", "my_update");
  $tree->render_sql("SELECT * FROM test", "id", "name", "", "parent_id");

And of course (config.php) - Just to make it complete:

    $mysql_user = "root";
    $mysql_pass = "";
    $mysql_db = "test";

This is just and only the implematation for the root level and without any other things to handle. Furthermore reorganizing the ‘sorting’ field may could be done more elegant. Comments are highly welcome.

Thanks to Alexandra! Even I did a different implementation you brought me back to investigate my Problem once more. :unamused:

I want to rewrite this code for coldfusion and mysql.

can you explain what this does:

 $sql = "SET @new_sorting := -1;";
 $sql = "UPDATE test SET sorting = (@new_sorting := @new_sorting + 1) WHERE parent_id = {$action->get_value('parent_id')} ORDER BY sorting;";

I see you need to get the original record from the DB in the first call so you can get it’s sequence number in the group to determine if the move was up or down amongst the siblings. Is there no way to get the original tr_order value of the node and send that back as well?

eg :

tree.setUserData(id,“tr_order_move”,1); --signify to the dataprocessor that this was a node move and not simply a name update
tree.setUserData(id,“tr_order_original”, [SOME VERY SPECIAL CODE HERE] ); --set the value of the original tr_order before the move was done

then in the server side code

string newItemOrder = WebUtilities.GetQueryStringValue(“tr_order”, string.Empty);
string oldItemOrder = WebUtilities.GetQueryStringValue(“tr_order_original”, string.Empty);
string parentId = WebUtilities.GetQueryStringValue(“tr_pid”, string.Empty);
string primaryKeyValue = WebUtilities.GetQueryStringValue(“tr_id”, string.Empty);

and from that you can follow the above sql statements without doing the original record lookup.

Is this possible?