talkingCode

Drag and Drop table rows with AJAX, Scriptaculous and Prototype (Part II)

Last time, we saw how to render the UI for drag and drop table rows. In this post we’ll have a look at making the reordering that the user selects persistent, so that when they come back to the page (having cleared their cookies - this won’t be some session[] hack) they see the ordering they selected. What’s more, the Ajax stuff even works in Internet Explorer this time (assuming you’re using lists, not tables). If you’ve already worked it out, please stop reading now

I said last time that

new Ajax.Request('/some/action/url',
    { method:'post', parameters:Sortable.serialize('table1') })

was the answer. To explain why, we’ll need a bit of preamble. A good general strategy for writing software when you’re not sure what you’re doing is to assume that whatever you’re trying to write has already been written. In this case, we know that we need to have the order of the rows stored in the database, so lets assume I have a database table from which I’m generating my entries:

CREATE TABLE my_table (
  id INT AUTO_INCREMENT,
  column1_value TEXT,
  column2_value TEXT,
  order INT,
  PRIMARY KEY (id)
);

I’m going to be generating the table using the results from the following query:

SELECT id, column1_value, column2_value
FROM my_table
ORDER BY order;

We’ll use table rows for this next bit, but you could use <li>s or whatever works for your browser. In whatever your favourite language is (let’s pretend you like Ruby/Rails/RHTML):

<% for row in rows %>
  <tr id="row_<%= row.id %>">
    <td><%=h row.column1_value %></td>
    <td><%=h row.column2_value %></td>
  </tr>
<% end %>

For balance, let’s pretend you like PHP too (disclosure - I don’t, nor do I really know PHP):

<?php
while ($row = mysqli_fetch_array($result))
{
?>
  <tr id="row_<?php echo $row["id"]; ?>”>
    <td><?php echo $row["column1_value"]; ?></td>
    <td><?php echo $row["column2_value"]; ?></td>
  </tr>
<?php>
}
?>

So we’ve displayed the elements we want to be able reorder, sorted by the order specified in the database table. Making a call to Sortable.create as per the previous post will allow the user to reorder it, but what we really want to so is save the ordering. Sortable.create allows us to specify a callback function which will be executed every time the user changes the ordering (as soon as they release the mouse):

Sortable.create("table1", {tag:"tr", containment:["table1","table2"], onUpdate:sendUpdate})

So all we have to do is ensure that ’sendUpdate’ sends the new order back to the user. At this point, installing FireBug is a really good idea. If you visit, for example, the previous post and enable FireBug, you can use the console tab to execute JavaScript on the page. Try the following:

>>> Sortable.serialize("table1")
"table1[]=1&table1[]=2″
>>> Sortable.serialize(”table2″)
“table2[]=1&table2[]=2&table2[]=3″

The Sortable.serialize function turns our rows into a string suitable for form POST / GET. You’ll notice that I gave my table rows “id”s like “row_1″ and “row_2″. If you name the tags you want sorting in the same way, the result of the Sortable.serialize call will be a POST / GET array whose elements are the “id” numbers of your sortable elements and whose order is the order that the elements appear after the user has made their change.

Prototype provides the Ajax.Request function for making XmlHttpRequests. We can use this to make our ’sendUpdate’ function:

function sendUpdate(updatedElement)
{
  new Ajax.Request("/my/action/url.php", {
method:"post", parameters:Sortable.serialize(updatedElement.id)
})
}

Now, when the user changes the ordering, our “url.php” script will receive a POST from their browser containing an array whose name is the ID of the updated element and whose values are the rows. Back on the server side, then:

def action_function
  table1 = params[:table1]
  for i in (0..(table1.size - 1))
      t = TableRow.find(table1[i])
      t.update_attributes(:order => i)
  end
end

or, for devilment

function action_function()
{
  global $link;
  $table1 = $_POST["table1"]
  for ($i=0; $i<count($table1); $i++)
  {
    $link->query(”UPDATE my_table SET order=$i WHERE id=”
               . mysql_escape($link, $table1[$i]))
  }
}

Hopefully that’s all perfectly clear and mostly correct. Please let me know if not.