From: Jason Woofenden <jason@jasonwoof.com>
Date: Tue, 23 Feb 2010 19:25:47 +0000 (-0500)
Subject: added: db_reposition() (renumbering not tested)
X-Git-Url: https://jasonwoof.com/gitweb/?p=wfpl.git;a=commitdiff_plain;h=6d9765bdf97af8351ed09c4187869cf05e74e2af

added: db_reposition() (renumbering not tested)
---

diff --git a/db.php b/db.php
index 51452b8..c5683c0 100644
--- 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);
+	}
+}