Php/Laravel issue trying to resort row order using drag and drop jquery datatables

tjyoung
tjyoung used Ask the Experts™
on
Hi,
I have a datatables table I am using to allow a person to resort the row order of the slides (see attached as example). In my mysql table I am using an additional column to set the row order of each row.
So essentially in mysql I have
id - primary auto increment
SlideOrder - I am using for the sequence
SlideTitle
and a couple created/modified datetime etc.

 
table example
When the user drops a row into place the code I'm using is below to send to server ( I start getting sketchy here on in):

oTable.on( 'row-reorder', function ( e, diff, edit ) 
{
var result;
var sendloop = [];
 		
	for ( var i=0, ien=diff.length ; i<ien ; i++ )
	{
		var rowData = oTable.row( diff[i].node ).data();
		result = ''+diff[i].newData+','+diff[i].oldData+'';
		sendloop.push(result);
	}
        	
	var jsonString = JSON.stringify(sendloop);
   
$.ajax({
        type: "GET",
        url: "/dealboxx/ohs/admin/sort",
        data: {data : jsonString}, 
        cache: false
    });
    });

Open in new window


The result of which sends an ajax request and the data variable (when I drag row 1 below row 4 for example):
data["1,2","2,3","3,4","4,1"]

Had I dragged row 2 below row 3 initially I'd be sending:
data["2,3","3,2"]

Had I dragged row 3 in front of row 1:
data["1,3","2,1","3,2"]

When the data gets to my server (my wrong code) does this:
public function sortCourse(Request $request)
    {
	$obj = json_decode($request->input('data'), true);
	
	foreach($obj as $row)
	{
     
		$insertArray = explode(",",$row);
		
		$slidechange = DB::table('ohs_courses_content')
			->select('id')
			->where('SlideOrder',$insertArray[1])
		    ->first();
		
    
	DB::table('ohs_courses_content')
            ->where('id', $slidechange->id)
            ->update(['SlideOrder' => $insertArray[0]]);
 	 	}
	
    
    }

Open in new window


Depending on what I initially dragged it may work (if I'm dragging down or up). Couple drags into it and its a mess.

So my logic etc is no doubt totally wrong.

any help would be very much appreciated as always.
tj
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
leakim971Multitechnician
Top Expert 2014

Commented:
Let's say you store the ID in the data.
You just need to parse the table and get each ID

oTable.on( 'row-reorder', function ( e, diff, edit ) {

	var jsonArray = [];

	for ( var i=0, ien=diff.length ; i<ien ; i++ ) {
		var ID = oTable.row( diff[i].node ).data();
		var obj = {};
		obj[ID] = i;
		jsonArray.push(obj);
	}

	var jsonString = JSON.stringify(jsonArray);

	$.getJSON("/dealboxx/ohs/admin/sort", { data: jsonString }, function() {}); 

});

Open in new window


so if you put ID 4 in position one, you get :
[{4:1},{1:2},{2:3},{3:4}]

so you can use :
public function sortCourse(Request $request)
{
    $obj = json_decode($request->input('data'), true);
    for($i=0;$i<sizeof($obj);$i++)
    {
        foreach($obj[$i] as $ID => $SlideOrder )
        {
            DB::table('ohs_courses_content')
                ->where('id', $ID)
                ->update(['SlideOrder' => $SlideOrder]);
        }
    }

Open in new window

Author

Commented:
This is awesome.
Its gotta be close but the json looks like this:
data      [{"[object+Object]":0},{"[object+Object]":1}] that its sending
leakim971Multitechnician
Top Expert 2014

Commented:
the main key is "oTable.row( diff[ i ].node ).data()" must return an ID.
if you see [object+Object] that mean it don"t return the ID.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Author

Commented:
I see. I'll investigate this.

The jquery table looks like this:
var oTable;
	oTable = $('#ohs_table').DataTable({
        processing: true,
        serverSide: true,
        responsive: true,
        searchDelay: 600,
        autoWidth: false,
        rowReorder: true,
        rowReorder: {
        dataSrc: 'id'
		},
        order: [ [1, "asc"]],
        dom: '<"datatable-header"><"datatable-scroll"tr><"datatable-footer"fp>',
        language: {
        	"emptyTable": "No content yet",
            "search": '_INPUT_',
            "lengthMenu": '_MENU_',
            "info": "_TOTAL_ slides in total",
            "infoFiltered": " - filtered from _MAX_"
        },
        pagingType: "full_numbers",
        bFilter : true,
        bSort : true,
		bLengthChange: false,
        ajax: '/dealboxx/ohs/admin/getcontent?id={{{ $course->id }}}',
        columns: [
        	{ data: 'id', name: 'id', searchable: false},
        	{ data: 'SlideOrder', name: 'SlideOrder', searchable: false, responsivePriority: 3 },
        	{ data: 'Active', name: 'Active', searchable: false, sortable: false, responsivePriority: 5 },
        	{ data: 'SlideTitle', name: 'SlideTitle', searchable: true, sortable: false, responsivePriority: 2 },
        	{ data: 'SlideType', name: 'SlideType', searchable: false, sortable: false, responsivePriority: 4 },
            { data: 'action', name: 'action', searchable: false, sortable: false, responsivePriority: 1}
        ],
        columnDefs: [
        	{ targets: [0], visible: false},
            { orderable: true, className: 'reorder', targets: 1 },
            { orderable: false, targets: '_all' }
        ],
        
     });

Open in new window


And the table loads with this data:
{"draw":4,"recordsTotal":4,"recordsFiltered":4,"data":[{"id":20,"CourseId":42,"SlideOrder":1,"SlideType":"<i style='color:#43A047;' class='icon-file-presentation2'><\/i>","SlideTitle":"My second slide","SlideContent":"<figure class=\"text-center\"><img src=\"http:\/\/www.steeleauto.com\/assets\/dealboxx\/images\/ohs\/ohs81810963.jpg\" data-image=\"3\"><\/figure><h1 class=\"text-center\">some stuff here<\/h1>","Created":"2018-01-10 10:15:30","CreatedBy":"Chris Beaton","Modified":"2018-01-10 10:20:57","ModifiedBy":"Chris Beaton","Active":"<a class=\"label label-success unpublish\" data-id=\"20\">&nbsp;&nbsp;Active&nbsp;&nbsp;<\/span><\/a>","action":"<ul class='icons-list icons-list-extended text-nowrap'>\n\t\t\t\t<li><a style='color:#4a4a4a;' href='..\/course\/42\/20' title='Edit' ><i class='icon-square-right'><\/i><\/a><\/li>&nbsp;\n\t\t\t\t<li><a href='#' data-popup='tooltip' data-id='20' title='Delete' data-toggle='modal' class='remove'><i class='icon-cancel-square2'><\/i><\/a><\/li>\n\t\t\t\t<\/ul>"},{"id":19,"CourseId":42,"SlideOrder":2,"SlideType":"<i style='color:#43A047;' class='icon-file-presentation2'><\/i>","SlideTitle":"My first slide","SlideContent":"<p>Here it is<br><\/p>","Created":"2018-01-09 17:11:30","CreatedBy":"Todd Thompson","Modified":"2018-01-10 10:21:28","ModifiedBy":"Chris Beaton","Active":"<a class=\"label label-success unpublish\" data-id=\"19\">&nbsp;&nbsp;Active&nbsp;&nbsp;<\/span><\/a>","action":"<ul class='icons-list icons-list-extended text-nowrap'>\n\t\t\t\t<li><a style='color:#4a4a4a;' href='..\/course\/42\/19' title='Edit' ><i class='icon-square-right'><\/i><\/a><\/li>&nbsp;\n\t\t\t\t<li><a href='#' data-popup='tooltip' data-id='19' title='Delete' data-toggle='modal' class='remove'><i class='icon-cancel-square2'><\/i><\/a><\/li>\n\t\t\t\t<\/ul>"},{"id":21,"CourseId":42,"SlideOrder":2,"SlideType":"<i style='color:#43A047;' class='icon-file-presentation2'><\/i>","SlideTitle":"A third slide ","SlideContent":"<p>asdfasdf<\/p>","Created":"2018-01-10 10:20:30","CreatedBy":"Chris Beaton","Modified":"2018-01-10 11:30:31","ModifiedBy":"Tim Peacock","Active":"<a class=\"label label-warning publish\" data-id=\"21\">InActive<\/span><\/a>","action":"<ul class='icons-list icons-list-extended text-nowrap'>\n\t\t\t\t<li><a style='color:#4a4a4a;' href='..\/course\/42\/21' title='Edit' ><i class='icon-square-right'><\/i><\/a><\/li>&nbsp;\n\t\t\t\t<li><a href='#' data-popup='tooltip' data-id='21' title='Delete' data-toggle='modal' class='remove'><i class='icon-cancel-square2'><\/i><\/a><\/li>\n\t\t\t\t<\/ul>"},{"id":24,"CourseId":42,"SlideOrder":3,"SlideType":"<i style='color:#43A047;' class='icon-file-presentation2'><\/i>","SlideTitle":"My slide","SlideContent":"<h1 class=\"text-center\"><mark>as<\/mark>dfadsfadsfasfa<span style=\"color: rgb(149, 55, 52);\">sfasd<\/span><\/h1>\r\n<p><\/p>\r\n<div class=\"video-container\"><iframe width=\"500\" height=\"281\" src=\"\/\/www.youtube.com\/embed\/Hl0GSLcwHVs\" frameborder=\"0\" allowfullscreen=\"\"><\/iframe><\/div>\r\n<p><\/p>","Created":"2018-01-10 11:33:14","CreatedBy":"Tim Peacock","Modified":"2018-01-10 11:34:30","ModifiedBy":"Tim Peacock","Active":"<a class=\"label label-warning publish\" data-id=\"24\">InActive<\/span><\/a>","action":"<ul class='icons-list icons-list-extended text-nowrap'>\n\t\t\t\t<li><a style='color:#4a4a4a;' href='..\/course\/42\/24' title='Edit' ><i class='icon-square-right'><\/i><\/a><\/li>&nbsp;\n\t\t\t\t<li><a href='#' data-popup='tooltip' data-id='24' title='Delete' data-toggle='modal' class='remove'><i class='icon-cancel-square2'><\/i><\/a><\/li>\n\t\t\t\t<\/ul>"}],"queries":[{"query":"select count(*) as aggregate from (select '1' as `row_count` from `ohs_courses_content` where `CourseId` = ?) count_row_table","bindings":["42"],"time":0.8},{"query":"select * from `ohs_courses_content` where `CourseId` = ? order by `SlideOrder` asc limit 10 offset 0","bindings":["42"],"time":0.75}],"input":{"id":"42","draw":"4","columns":[{"data":"id","name":"id","searchable":"false","orderable":"false","search":{"value":"","regex":"false"}},{"data":"SlideOrder","name":"SlideOrder","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"Active","name":"Active","searchable":"false","orderable":"false","search":{"value":"","regex":"false"}},{"data":"SlideTitle","name":"SlideTitle","searchable":"true","orderable":"false","search":{"value":"","regex":"false"}},{"data":"SlideType","name":"SlideType","searchable":"false","orderable":"false","search":{"value":"","regex":"false"}},{"data":"action","name":"action","searchable":"false","orderable":"false","search":{"value":"","regex":"false"}}],"order":[{"column":"1","dir":"asc"}],"start":"0","length":"10","search":{"value":"","regex":"false"},"_":"1515665781894"}}

Open in new window

Just in case anything jumps out at you

Author

Commented:
Getting somewhere I think...
changed to:

oTable.on( 'row-reorder', function ( e, diff, edit ) {

	var jsonArray = [];

	for ( var i=0, ien=diff.length ; i<ien ; i++ ) {
		var ID = oTable.row( diff[i].node ).index();
		var obj = {};
		obj[ID] = i;
		jsonArray.push(obj);
	}

	var jsonString = JSON.stringify(jsonArray);

	$.getJSON("/dealboxx/ohs/admin/sort", { data: jsonString }, function() {}); 

});

Open in new window


and now getting:
data      [{"3":0},{"0":1},{"1":2},{"2":3}]

Author

Commented:
that is just getting me the index of the rows 0, 1, 2 etc. not the id. So can't update the sort with the table index
I'll keep on it
Multitechnician
Top Expert 2014
Commented:
replace :
var ID = oTable.row( diff[ i ].node ).data().id;
instead :
var ID = oTable.row( diff[ i ].node ).data();

if that don't help, please post what you see in the console with :

oTable.on( 'row-reorder', function ( e, diff, edit ) {

	var jsonArray = [];

	for ( var i=0, ien=diff.length ; i<ien ; i++ ) {
		var ID = oTable.row( diff[i].node ).data();
                console.log("obj is : ", ID);
		var obj = {};
		obj[ID] = i;
		jsonArray.push(obj);
	}

	var jsonString = JSON.stringify(jsonArray);

	$.getJSON("/dealboxx/ohs/admin/sort", { data: jsonString }, function() {}); 

});

Open in new window

Author

Commented:
that did the trick! Now I'm getting the DB id for the ID value.
Going to try and bring it all together now.
leakim971Multitechnician
Top Expert 2014

Commented:
please don't forget to close the question

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial