Tech Tidbits - Ruby, Ruby On Rails, Merb, .Net, Javascript, jQuery, Ajax, CSS...and other random bits and pieces.

Wednesday, February 27, 2008

jQuery Sortable with Ajax, PHP, and MySQL

I needed to create a sortable list that would save the order position of data items. I've been using jQuery more and more and decided to go with the Interface Elements for jQuery.

I found a blog entry titled jQuery Sortable Ajax PHP Integration that was a good start. However, it didn't actually use MySQL to store the sorted data so I've gone ahead and added the database queries and updates.

First I created a simple table to store links (I'd like my client to order the links they enter through an admin interface).

id int(10) NOT NULL auto_increment,
link_url varchar(50) NOT NULL,
link_text varchar(50) NOT NULL,
order_position int(2),
primary key(id)

Next is the PHP page that reads the link data from the table and displays the links based on the order_position column of the table.


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
<script type="text/javascript" src="/js/jquery/jquery-1.2.3.min.js"></script>
<script type="text/javascript" src="interface.js"></script>

function() {
accept : 'sortitem',
onchange : function (sorted) {
serial = $.SortSerialize('sortme');

url: "sortdata.php",
type: "POST",
data: serial.hash,
// complete: function(){},
//success: function(feedback){ $('#data').html(feedback); }
// error: function(){}


<ul id="sortme">
// Connect to the database as necessary
$dbh = mysql_connect('','username','password')
or die ("Unaable to connnect to MySQL");

$selected = mysql_select_db("sort_test",$dbh)
or die("Could not select sort_test");

$result = mysql_query('SELECT * FROM links ORDER BY order_position');
while ($row = mysql_fetch_array($result)) {
print '<li id="' . $row{'id'} . '" class="sortitem">' . $row{'link_text'} . "\n";


This will make an Ajax call back to the server where PHP will update the database as you drag and sort the list data:



$sortme = $_POST['sortme'];
// Connect to the database as necessary
$dbh = mysql_connect('','username','password')
or die ("Unaable to connnect to MySQL");

$selected = mysql_select_db("sort_test",$dbh)
or die("Could not select sort_test");

for ($i = 0; $i < count($sortme); $i++) {
mysql_query("UPDATE links SET order_position=$i WHERE id=$sortme[$i]");

A little CSS certainly wouldn't hurt things. Here's some I found on another tutorial:

ul {
list-style: none;
li {
background: #727EA3;
color: #FFF;
width: 100px;
margin: 5px;
font-size: 10px;
font-family: Arial;
padding: 3px;

I wouldn't call this production ready code, but all the pieces are in place if you'd like to use jQuery with Ajax, PHP, and MySQL for a sortable list. Don't forget, you'll need to get jQuery itself and the interface.js file as well. Another thing to consider is alerting the user if the update fails or takes a long time.

Thursday, February 21, 2008

Ruby on Rails - Testing with Multiple Databases

I ran into errors when running tests for a Rails app using multiple databases. When testing, fixtures are automatically inserted into the primary database, and I was getting an error on an unused default fixture that was created when I built the model tied to the second database. Removing that fixture got my tests to pass...

More info here:

and on multiple databases in general:

Tuesday, February 19, 2008


I've been using the jQuery Javascript library a lot lately and it's definitely become my favorite library. (I've written four plugins in the last couple months to boot)

Here are a few links to useful jQuery resources.

jQuery: The Write Less, Do More, JavaScript Library
jQuery UI: Widgets, Components, and Interaction
Plugins - jQuery JavaScript Library
jQuery Blog
Learning jQuery
Visual jQuery
IBM developerWorks: Simplify Ajax development with jQuery


Ruby on Rails does not support composite primary keys and a legacy database I'm working with has lots of them...after a quick search I found this useful Gem:


About Me

My photo
Developer (Ruby on Rails, iOS), musician/composer, Buddhist, HSP, Vegan, Aspie.