Full-featured table drag-n-drop feature

Adam · December 13, 2018, 1:32pm

I put together a (quite elegant) solution :)There is very little to do - both in terms of setup and implementation, and (unlike most implementations) the method even works when table paging is in operation!The only requirement for tables using this feature is a suitably-sized unsigned INT field which is indexed and has a default value of AUTO_INCREMENT - I prefer to name this field ‘Order’, but you can use any name you wish. Here is an example from one of my tables (I find that ‘smallint’ is quite sufficient, but you r can use ‘int’ if you prefer):Order smallint(5) unsigned NOT NULL AUTO_INCREMENTSo, now we can start implementing the feature…Begin by adding jquery-ui to your project if it is not already included. You can download it from jqueryui.com/download/
I saved the plug-in to the existing PHPM ‘jquery’ folder, so you will need to adapt the path if you save it somewhere different for your project.Begin by adding this to Server Events > Global > All Pages > Page_Head …Now add this to Server Events > Global > All Pages > Global Code …function GetKeyField($scope) {
foreach ($scope as $key => $subkey) {
if (is_object($subkey) && isset($subkey->IsPrimaryKey) && $subkey->IsPrimaryKey)
return ($key);
}
}Next, add this to Server Events > Other > Index Page > Page_Load …

if (isset($_POST['table']) && isset($_POST['field']) && isset($_POST['order']) && isset($_POST['data'])) {
	$table = $_POST['table'];
	$field = $_POST['field'];
	$order = $_POST['order'];
	$data = $_POST['data'];

	if (empty($table) || empty($field) || !is_array($data) || count($data) == 0)
		die('Incompatible arguments');

	$fields = array();
	$orders = array();
	$offset = count($data);

	foreach ($data as $row) {
		$pair = explode('{||}', $row);
		$fields[] = $pair[0];
		$orders[] = $pair[1];
	}

	for ($index = 0; $index < count($data); $index++) {
		Execute("UPDATE `{$table}` SET `{$order}` = {$orders[$index]} WHERE `{$field}` = '{$fields[$index]}'");
	}

	die();
}

Finally, add this to Client Scripts > Global > Pages with header/footer > Global Code …function tableDnD(table, field, order = ‘Order’) {
$(‘table’).find(‘tbody’).addClass(‘sortable-table’);

$('.sortable-table').sortable({
	delay: 150,
	stop: function() {
		var lowest = 1000000;

		$('.sortable-table tr').each (function() {
			$(this).find('td').each (function() {
				if ($(this).attr('data-name') == order)
					lowest = Math.min(lowest, parseInt($(this).find(':only-child:last').text()));
			});
		});

		var index = 0;
		var data = new Array();

		$('.sortable-table tr').each (function() {
			$(this).find('td').each (function() {
				if ($(this).attr('data-name') == field)
					data.push($(this).text().replace(/[\t\n]+/g, '') + '{||}' + (lowest + index));
				else if ($(this).attr('data-name') == order)
					$(this).find(':only-child:last').html(lowest + index);

				$(this).removeClass('ew-table-last-row');
			});

			if (index === 0 || !!(index && !(index % 2)))
				$(this).removeClass('ew-table-alt-row');
			else
				$(this).addClass('ew-table-alt-row');

			index++;
		});

		$('.sortable-table tr:last').each (function() {
			$(this).find('td').each (function() {
				$(this).addClass('ew-table-last-row');
			});
		});

		$.ajax({
			url: 'index.php',
			type: 'post',
			data: {table:table, field:field, order:order, data:data},
			success: function(response) {
			}
		});
	}
});

}…and that’s it!To activate the feature, go to Client Scripts > Table-Specific > List Page > Startup Script and add this …tableDnD(‘<?=CurrentPage()->TableName?>’, ‘<?=GetKeyField(CurrentPage())?>’);** If you named your ‘Order’ field something different then use:

tableDnD(‘<?=CurrentPage()->TableName?>’, ‘<?=GetKeyField(CurrentPage())?>’, ‘NameOfYourOrderField’);Now just rebuild your project and have fun

Adam · December 13, 2018, 1:46pm

Just one thing I forgot to mention… D-n-D tables should be sorted ASCENDING on your ‘Order’ field - if other sort orders are used then the effects will be unpredictable!

Creabis · June 17, 2019, 9:32am

Hi Adam, is your code a solution to order the table rows? That would be awesome. I have a practical case for it: I need to sort order the detail rows of an invoice. So the I want to decide which row should displayed first and wich last e.g.
Is your code able to sort the orders of the detail rows on the master/detail grid edit page?

Adam · July 13, 2019, 11:36am

Hi, Creabis. This code is intended for manual “drag-n-drop” ordering of records - it should do what you need.

Veertualia · October 12, 2020, 4:53pm

Sorry for bringing up an old thread.
Everything was working fine in v2020 but in v2021 the OP code does not work. There is no Server Events > Other > Index Page > Page_Load anymore.
I managed to invoke a custom PHP file from Ajax (generated under the “views” folder and with include common files active), and, inside it, I can get the POST variables,but now I cannot access the Execute query function, nor any other PHPMaker function. I have the required data but I cannot update the DB. The custom file only contains the namespace declaration and the default GetDebugMessage code, but it’s not working when invoked by Ajax.
Thanks for any help.

Adam · October 13, 2020, 9:20am

The v2021 approach involves moving the code from the old Server Events > Other > Index Page > Page_Load event into Server Events > Global > All Pages > Route_Action:

$app->post(‘/sort’, function ($request, $response, $args) {
$table = @$_POST[‘table’];
$field = @$_POST[‘field’];
$order = @$_POST[‘order’];
$data = @$_POST[‘data’];

if (empty($table) || empty($field) || !is_array($data) || !count($data))
return $response->withJson([“text” => “Incompatible arguments”]);

$fields = array();
$orders = array();
$offset = count($data);

foreach ($data as $row) {
$pair = explode(‘{||}’, $row);
$fields = $pair[0];
$orders = $pair[1];
}

for ($index = 0; $index < count($data); $index++) {
$sql = “UPDATE {$table} SET {$order} = {$orders[$index]} WHERE {$field} = ‘{$fields[$index]}’”;
ExecuteUpdate($sql);
}

return $response->withJson([“text” => “”]);
});

…and tweaking the function in Client Scripts > Global > Pages with header/footer > Global Code

function tableDnD(table, field, order = ‘Order’) {
$(‘table’).find(‘tbody’).addClass(‘sortable’);

sortable(‘.sortable’, {
items: ‘.sortable tr’,
placeholder: ‘’,
forcePlaceholderSize: true
})[0].addEventListener(‘sortupdate’, function(e) {
var lowest = 1000000;

$(‘.sortable tr’).each (function() {
$(this).find(‘td’).each (function() {
if ($(this).attr(‘data-name’) == order)
lowest = Math.min(lowest, parseInt($(this).find(‘:only-child:last’).text()));
});
});

var index = 0;
var data = new Array();

$(‘.sortable tr’).each (function() {
$(this).find(‘td’).each (function() {
if ($(this).attr(‘data-name’) == field)
data.push($(this).text().replace(/[\t\n]+/g, ‘’) + ‘{||}’ + (lowest + index));
else if ($(this).attr(‘data-name’) == order)
$(this).find(‘:only-child:last’).html(lowest + index);

$(this).removeClass(‘ew-table-last-row’);
});

if (index === 0 || !!(index && !(index % 2)))
$(this).removeClass(‘ew-table-alt-row’);
else
$(this).addClass(‘ew-table-alt-row’);

index++;
});

$(‘.sortable tr:last’).each (function() {
$(this).find(‘td’).each (function() {
$(this).addClass(‘ew-table-last-row’);
});
});

$.ajax({
url: ‘/sort’,
type: ‘post’,
data: {table:table, field:field, order:order, data:data},
success: function(response) {
if (response.text != ‘’)
alert(response.text);
}
});
});
}

Veertualia · October 14, 2020, 6:00pm

Hi, thanks for your precious help. I really appreciate it.

JS console was reporting an Uncaught ReferenceError: sortable is not defined, right on the first line of your latest JS: $(‘table’).find(‘tbody’).addClass(‘sortable’).
I solved by using your former JS code, with the same route tweak in the url parameter of the Ajax invocation, and the JS error disappeared.

However, since my project url structure is 127.0.0.1/folder1/folder2/phpmaker_prj, if I use “/sort” as the Ajax url parameter, 127.0.0.1/sort is opened and the invocation returns a 404.
When I remove the initial slash from the JS code, I see in the developer console that the url 127.0.0.1/folder1/folder2/phpmaker_prj/sort is called and a 200 OK is returned but nothing happens, the table order does not get updated in the DB.

If I try opening the route directly with the browser by calling 127.0.0.1/folder1/folder2/phpmaker_prj/sort PHPMaker returns 404 Route ‘sort’ not found.
Any idea what I am missing?

Thanks in advance.

Adam · October 14, 2020, 10:57pm

You really should use virtual hosts - I use “.tst” local virtual host domains so if the real domain is mydomain.com then my dev server will have a vhost for mydomain.tst

I don’t know which WAMP you’re using but WampServer has the functionality built in - just add a new VHOST from the GUI menu.

Veertualia · October 15, 2020, 10:07am

Hi, thanks for your answer.
Indeed, creating a virtual host worked. However, this is the update of an existing project with a frontend and a backend.
The frontend sits under the host root, while the backend in its own “admin” folder inside the host root. A pretty common configuration. I cannot change this structure since it’s a production site. I can only update the source.

By adopting Slim and its model, PHPmaker made an important step in the right direction, in my opinion, and I’m sure they have taken into account that there are a lot of apps out there, each with its own structure.
There should be an easy way to use some route prefix or similar in Slim or in PHPMaker, at the very least.

Any idea?

Adam · October 15, 2020, 1:04pm

I haven’t come across this issue because I always build my systems to run from one application root.

I guess you could create a copy of the route_action so you have one with a “/” and one without - then, in your calling code, determine whether a slash is required (or not) and call the relevant action.

sticcino · October 19, 2020, 8:28pm

nice work Adam.

appears you know your JS/JQUERY from all your enhancements…

what do you think the feasibility/complexity would be to add a "columns’ visibility feature. basically when your on a page (list page in particular), display all the fields available so the user can show/hide the fields they want to see, and then save that like you can for a search criteria.

for example i have various departments (QA, Admin, etc) . each will want to see different columns based on there department needs, this would be better than hard code programming… if(group == “QA”) show this hide that…

Veertualia · October 20, 2020, 11:14am

Adam wrote:

I haven’t come across this issue because I always build my systems to run
from one application root.

I guess you could create a copy of the route_action so you have one with a
“/” and one without - then, in your calling code, determine
whether a slash is required (or not) and call the relevant action.

I solved by merging the separate DBs (that was tough), changing the structure so that the admin page is now on its own root and creating a user table, so that each user can login and access just its tables and fields.
That also simplifies the backend update process, since I don’t have to update separate admin instances. So, after all, changing was for the best.
Thank you very much for your great help.

Just a couple of questions more, if I may.

Any idea what could be the issue with JQuery-UI?
If I use the latest version (v1.12.1) and your latest JS code, the console reports “Uncaught ReferenceError: sortable is not defined”.
I have to change to your former JS code, which used “sortable-table” class, to make it work.
Since this is a POST request, to prevent Cross-site request forgery, I put the following parameters in the Ajax call:

csrf_name: ew.TOKEN_NAME,
csrf_value: ew.ANTIFORGERY_TOKEN,

However, I don’t know if this is useful or the correct way of proceeding. The DB query gets executed with or without those parameters in the Ajax invocation.
Do I need to use some verifying code server side? Or PHPMaker middleware will transparently take care of it? I thing the documentation is somewhat scarce about it.

Thanks in advance.

Adam · October 21, 2020, 12:11pm

@sticcino … if you need particular columns not to be available to certain security groups then you’re best using ListOptions_Rendering to hide the relevant columns, but if you just want to present more pertinent columns to certain groups then I ran across a really neat solution for showing / hiding columns based on their “importance” and may be exactly what you’re looking for - see if it helps you:

h??ps://api.jquerymobile.com/1.4/table-columntoggle/

@Veertualia … I can only assume that jQueryUI includes a “sortable” directive - I remember using jQueryUI a while ago, but it was clashing with something else (I never discovered what) and resulted in a page element “shaking”, so I dropped that plug-in and found alternate code that did the job.

As for the CSRF tokens, I’m not sure they’re really relevant in the context as they’re handled in form processing code, not the Ajax call for row re-ordering.

sticcino · October 22, 2020, 11:06am

Thanks, I’ll check it out, see if can figure out how to add those required attributes.