Jump to content

queries to change sort order


bcamp1973

Recommended Posts

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?

Link to comment
https://forums.phpfreaks.com/topic/92964-queries-to-change-sort-order/
Share on other sites

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 ;)

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.