This PHP function lets you reorder records on a table when you have a field used for save the position. If you have a field with position when you are editing records you probably want, for example, to move a record from position 5 to position 2, but if you already have a record in position 2 problems could raise, so how can you reorder all the records without losing informations and preserving the right order of the other elements?
A way is to move the records before the target position one step before, then move the selected record, and then rebuild the order index number for every row.
This function isn’t optimized, but works.
function reorder($table,$orderfield,$idfield,$id=null,$pos=null,$newpos=null) {
if($pos!=$newpos) {
if($newpos>$pos) {
mysql_query($sql = "UPDATE ".$table." SET ".$orderfield."=".$orderfield."-1 WHERE ".$orderfield."<= '".$newpos."' AND $idfield<>'".$id."'");
mysql_query($sql = "UPDATE ".$table." SET ".$orderfield."=".$orderfield."+1 WHERE ".$orderfield."> '".$newpos."' AND $idfield<>'".$id."'");
} else {
mysql_query($sql = "UPDATE ".$table." SET ".$orderfield."=".$orderfield."-1 WHERE ".$orderfield."< '".$newpos."' AND $idfield<>'".$id."'");
mysql_query($sql = "UPDATE ".$table." SET ".$orderfield."=".$orderfield."+1 WHERE ".$orderfield.">= '".$newpos."' AND $idfield<>'".$id."'");
}
}
if($pos!=$newpos || ($pos==null && $newpos == null && $id==null) ) {
$rs = mysql_query($sql = "SELECT $orderfield,$idfield FROM ".$table." ORDER BY ".$orderfield." ASC");
$p = 0;
while($r=mysql_fetch_array($rs)) {
$p++;
mysql_query($sql = "UPDATE ".$table." SET ".$orderfield."='".$p."' WHERE ".$idfield."= '".$r[$idfield]."'");
}
}
}
And this is an example of usage, suppose that you have this table:
| table users | ||
|---|---|---|
| id | name | pos |
| 12 | Gino | 1 |
| 7 | Mary | 2 |
| 9 | John | 3 |
| 33 | Doug | 4 |
If you want to move John to position 1 you have to call reorder function this way:
reorder("users","pos","id",9,3,1);
I know the above method isn’t optimized but it works, I call it everytime the admin update the record or insert a new record. Remember that you have also to always insert records with pos = max(pos)+1 value to avoid two records with the same position.
