Reordering rows records on a mysql table with PHP

This PHP function lets you reorder records on a table when you have a field used for save the position.…

December 18, 2013

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.

Author

I'm a software engineer, an everyday web developer and a maker. I usually build sites with PHP, within or without WordPress. I build Internet of Things with Arduino and ESP8266. I'm the founder of Rockit.it and Dailybest.it and I'm actually the Chief Technical Officer of Better Days web agency.

Recommended

Highlight text for search results in PHP

Useful code to highlight text occurences in search results or in a text. How to highlight text in a string…

September 2, 2016

Modify wp_query for a specific category in category.php

Suppose you have a category called “events“, with a custom field with the date of the event, which is different…

April 8, 2016

How to add rel=”nofollow” to links with preg_replace()

Adding rel="nofollow" to external link is a good SEO practice.

September 22, 2015

Limit the number of categories for posts in WordPress

CHOOSE ONLY ONE CATEGORY WORDPRESS If you need to limit the number of categories used by the authors of your…

September 14, 2015

Scraping content with PHP as if it was jQuery

Building a spider or a bot needs some knowledge of regular expressions, you must know and use preg_match or preg_match_all…

December 8, 2013

Embedding images in HTML or CSS with PHP

This small function returns an encoded string to embed images, inline, inside your html/css code and reduce the number of…

December 4, 2013