How to reorder rows changinga position field


/ Published in: PHP
Save to your folder(s)

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? See how to use in the above link.


Copy this code and paste it in your HTML
  1. function reorder($table,$orderfield,$idfield,$id=null,$pos=null,$newpos=null) {
  2. if($pos!=$newpos) {
  3. if($newpos>$pos) {
  4. mysql_query($sql = "UPDATE ".$table." SET ".$orderfield."=".$orderfield."-1 WHERE ".$orderfield."<= '".$newpos."' AND $idfield<>'".$id."'");
  5. mysql_query($sql = "UPDATE ".$table." SET ".$orderfield."=".$orderfield."+1 WHERE ".$orderfield."> '".$newpos."' AND $idfield<>'".$id."'");
  6. } else {
  7. mysql_query($sql = "UPDATE ".$table." SET ".$orderfield."=".$orderfield."-1 WHERE ".$orderfield."< '".$newpos."' AND $idfield<>'".$id."'");
  8. mysql_query($sql = "UPDATE ".$table." SET ".$orderfield."=".$orderfield."+1 WHERE ".$orderfield.">= '".$newpos."' AND $idfield<>'".$id."'");
  9. }
  10. }
  11. if($pos!=$newpos || ($pos==null && $newpos == null && $id==null) ) {
  12. $rs = mysql_query($sql = "SELECT $orderfield,$idfield FROM ".$table." ORDER BY ".$orderfield." ASC");
  13. $p = 0;
  14. while($r=mysql_fetch_array($rs)) {
  15. $p++;
  16. mysql_query($sql = "UPDATE ".$table." SET ".$orderfield."='".$p."' WHERE ".$idfield."= '".$r[$idfield]."'");
  17. }
  18. }
  19. }

URL: http://www.barattalo.it/2013/12/18/reorder-records-table/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.