Saving Javascript sorting results to the database.

I’ve been a little lazy on the technical posts as of late, so I figured it was about time I threw another one out there. Tonight’s goodness is a simple example of using scriptaculous and prototype to sort a list of items and save the sorted order into your database. So let’s dive in. The first file is the meat of our action. We have to make sure that we include the prototype (so we can do the Ajax call) and the scriptactulous (so we can do the sorting actions) libraries. Next we need to set up our list that will be sortable. We do this by grabbing the items out of our database and since we have a unique key with each record (in this case ideas_id) we use that as part of our unique id. The important thing to remember here is that the scriptaculous library expects each of our items to have names like ‘something_id’ with the id bit being some unique number. Finally having our initial list set up, we just need to do some final javascript bits to actually make the list sortable and tell it what to do when someone resorts the list. It doesn’t matter what we name this file, so we’ll just save it as ideas.php (because it seems a logical enough name)

<html> <head> <script src=“/javascripts/prototype.js” type=“text/javascript”></script> <script src=“/javascripts/scriptaculous.js” type=“text/javascript”></script> </head> <body> <h1>Sortable Ideas</h1> <div id=“sortable_ideas”> <?   $conn = mysql_connect(YOURDBHOST, YOURDBUSER, YOURDBPASSWORD) or die ('Error connecting to mysql’);   mysql_select_db(YOURDBNAME);   $sql = “select ideas_id, ideas_title, ideas_order from ideas order by ideas_order”;   $rs = mysql_query($sql) or die(mysql_error());   $counter = 0;   while($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {     $counter++;     ?>     <div id=“myidea_<?= $row['ideas_id’] ?>” class=“ideas”>       <?= $counter ?>. <?= $row['ideas_title’] ?>     </div>     <?   } ?> </div> <script type=“text/javascript”>   function updateOrder() {     new Ajax.Request('update_idea_order.php’, {method : 'post’, parameters : Sortable.serialize('sortable_ideas’)});   }   Sortable.create('sortable_ideas’, {ghosting : true, tag: “div”, onUpdate : updateOrder }); </script>

Now all that’s left is to define the code that our Ajax is going to call…we’ll name this file 'update_idea_order.php’ because that’s what we just referenced with our Ajax call…

<? $conn = mysql_connect(YOURDBHOST, YOURDBUSER, YOURDBPASSWORD) or die ('Error connecting to mysql’); mysql_select_db(YOURDBNAME); $sortable_ideas = array(); if (isset($_REQUEST['sortable_ideas’])) { $sortable_ideas = $_REQUEST['sortable_ideas’]; } $counter = 0; foreach($sortable_ideas as $ideas_id) {   $counter++;   $sql = “update ideas set order = ” . $counter . “ where ideas_id = ” . $ideas_id;   mysql_query($sql); } ?>

And that’s about it! Of course there are a few things I think are worth noting here: 1. The sorting in this example will update your database but there’s nothing alerting you if it actually is working or not (you’ll have to check your database to really see the updates or reload the page to see that the sorting stuck)…it all depends on what you want to do, but there’s a good chance you’ll want to inform your users that an update actually is 'sticking’…so you might want to look into the 'onSuccess’ option for Sortable.create 2. In this example I used PHP and MySQL, but there is nothing about this example that required that…you could do the above with any scripting language you prefer (and you could cut the DB out entirely if you didn’t have a need for that to store your sorting information)… Now I think that’s cool stuff.

This post has received 40 loves.


ARCHIVE OF POSTS



This is the personal blog of Kevin Marshall (a.k.a Falicon) where he often digs into side projects he's working on for digdownlabs.com and other random thoughts he's got on his mind.

Kevin has a day job as CTO of Veritonic and is spending nights & weekends hacking on Share Game Tape. You can also check out some of his open source code on GitHub or connect with him on Twitter @falicon or via email at kevin at falicon.com.

If you have comments, thoughts, or want to respond to something you see here I would encourage you to respond via a post on your own blog (and then let me know about the link via one of the routes mentioned above).