Navigation link sorting for custom cms

I am building a small cms where the user can add, edit, and removed pages. Each time the user adds or edits a page, they can specify the order placement of that page within the navigation.

I have a field called sort_order where the user can add the sort order number. What I am wanting is when the user adds a sort number that is already assigned to another page, that the page with that sort number is bumped down along with any other pages underneath.

I hope this makes sense. If at all possible, it would be cool to integrate this with a drag and drop function instead of having to manually type a number. Thanks in advance for the help!

Pfft. I imagine something like this;



$thingId = 13; // The thing you are bumping, or moving, has an ID of 13.
$thingSortOrder = 2; // We are moving this item to position 2.

$sql = 'SELECT id, sort_order FROM things WHERE sort_order = "' . $thingSortOrder . '" LIMIT 1';
$result = $db->query($sql);

if ($result->numRows() != 0) {
// Something else has this sort order, so lets bump everything with a HIGHER sort order by 1.
$sql = 'UPDATE things SET sort_order = (sort_order + 1) WHERE sort_order >= "' . $thingSortOrder . '" '
$result = $db->query($sql);
}

// Now lets use the sort order that we wanted.
$sql = 'UPDATE things SET sort_order = "' . $thingSortOrder . '" WHERE id = "' . $thingId . '" LIMIT 1';
$result = $db->query($sql);



This will mean that you will eventually end up with sort orders which are not consecutive (eg: 1, 2, 7, 8, 9, 15, 16), but that should not really matter. This should be fine with a table of about 50 rows or so, with a maximum potential update of 49+1 rows, but this is an admin function and on average, those conditions will be pretty rare.

Add a time field as well and sort by position and time updated. Multiple items with the same position id will be sorted by time they were updated.