queries to change sort order

[bcamp1973]

I have a grid view that allows the user to change the sort order of items (see attached screen shot). I have it working. However, my SQL skills are minimal at best and i fear I've made it far more complicated than it needs to be. Every time a user clicks on an arrow to change an items order I do ALL of the following (FYI $wpdb is a database class for wordpress). $currentorder is obviously the items current "item_order" and $neworder is either $currentorder+1 or $currentorder-1 depending on the direction it's being moved.

 

$wpdb->query("UPDATE table_name SET item_order=9999 WHERE item_order=$currentorder);
$wpdb->query("UPDATE table_nameSET item_order=$currentorder WHERE item_order=$neworder);
$wpdb->query("UPDATE table_name SET item_order=$neworder WHERE item_order=9999);

 

Of course, if any item is removed from the list, this potentially causes issues...so I then run this so the items are numbered 1-n...

 

$items = $wpdb->get_col("SELECT id FROM table_name ORDER BY item_order ASC");

for($i=0; $i<count($items); $i++){
      $wpdb->query("UPDATE table_name SET item_order=".($i+1)." WHERE id=".$items[$i]['id']);
}

 

I actually feel unclean after writing this...but I'm not sure how to do this "properly".  Any suggestions?

[fenway]

The cleanest way is simply to update *ALL* sortorders on each change... assuming the list isn't large, this should be just fine.

[bcamp1973]

wow, ok...i guess i thought i was breaking a whole bunch of rules with my approach. glad I'm not   and, i would expect there will rarely be more than 30 to 40 items in a list that's being sorted except in very extreme cases...so, performance was probably never really an issue. just wanted to make sure I'm doing things "right" whenever i can or I'll never learn anything

[fenway]

Alternatively, you could start the sortorders in the 100s (e.g. 100, 200, 300), and then move the up or down by one a a time, and check for dupes, but why bother?