Posted By

ginoplusio on 12/20/13


Tagged

mysql php ordering


Versions (?)

How to reorder rows changinga position field


 / Published in: PHP
 

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

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.

  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. }

Report this snippet  

You need to login to post a comment.