added: db_reposition() (renumbering not tested)

diff --git a/db.php b/db.php
index 51452b8..c5683c0 100644 (file)
--- a/db.php
+++ b/db.php
@@ -369,4 +369,88 @@ function db_delete($table, $where = '') {
        db_send_query($sql);
 }
 
-?>
+
+define('DB_ORD_MAX', 2000000000);
+
+function db_reposition_respace($table, $field) {
+       $ids = db_get_column($table, 'id', "where $field != 0 order by $field");
+       $c = count($ids);
+       if(!$c) {
+               # should never happen
+               return;
+       }
+       $inc = floor(DB_ORD_MAX / ($c + 1));
+       $cur = $inc;
+       foreach($ids as $id) {
+               db_update($table, $field, $cur, 'where id=%i', $id);
+               $cur += $inc;
+       }
+}
+
+# this function facilitates letting the user manually sort records (with (int) $field != 0)
+#
+# When editing a particular row, give the user a pulldown, with 0 -> first, 1 -> second, etc, and pass this integer to db_reposition (3rd parameter). The value "ignored" can be passed, and the row will be given a sort value of 0 and ignored for all sorting.
+#
+# $pretty is used in error messages to refer to the row, it defaults to whatever you pass for $table.
+
+function db_reposition($table, $row_id, $new_pos, $field = 'ord', $pretty = 'same as $table', $renumbered_already = false) {
+       if($pretty = 'same as $table') {
+               $pretty = $table;
+       }
+
+       if($new_pos == 'ignored') {
+               # not sorted
+               return '0';
+       }
+
+       # strategy: calculate $prev_ord and $next_ord. If there's no space between, renumber and recurse
+       if($new_pos == '0') {
+               $row = db_get_row($table, "id,$field", "where $field != 0 order by $field limit 1");
+               if($row) {
+                       list($first_row_id, $first_row_ord) = $row;
+                       if($first_row_id == $row_id) {
+                               # already first
+                               return $first_row_ord;
+                       }
+                       $next_ord = $first_row_ord;
+               } else {
+                       # this is the only row, put it in the middle
+                       return '' + floor(DB_ORD_MAX / 2);
+               }
+
+               $prev_ord = 0;
+       } else {
+               $new_pos = format_int_0($new_pos);
+               $rows = db_get_rows($table, "id,$field", "where $field != 0 order by $field limit %i,2", $new_pos - 1);
+               if(!$rows) {
+                       message("Sorry, couldn't find the $pretty you asked to put this $pretty after. Putting it first instead.");
+                       return db_reposition($table, $row_id, '0', $field, $pretty);
+               } else {
+                       list($prev_id, $prev_ord) = $rows[0];
+                       if($prev_id == $row_id) {
+                               # after self? this shouldn't happen
+                               return $prev_ord;
+                       }
+                       if(count($rows) == 1) {
+                               # we should be last
+                               $next_ord = DB_ORD_MAX;
+                       } else {
+                               list($next_id, $next_ord) = $rows[1];
+                               if($next_id == $row_id) {
+                                       # after prev (already there)
+                                       return $next_ord;
+                               }
+                       }
+               }
+       }
+       if($prev_ord + 1 == $next_ord || $prev_ord == $next_ord) { # the latter should never happen
+               if($renumbered_already) {
+                       message("Programmer error in $pretty ordering code. Please tell your website administrator.");
+                       return '' . rand(2, DB_ORD_MAX - 2); # reasonably unlikely to be the same as some other ord
+               }
+               db_reposition_respace($table, $field);
+               return db_reposition($table, $row_id, $new_pos, $field, $pretty, $renumbered_already = true);
+       } else {
+               return $prev_ord + round(($next_ord - $prev_ord) / 2);
+       }
+}