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

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
LVL 1
tjyoungAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leakim971PluritechnicianCommented:
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

0
tjyoungAuthor Commented:
This is awesome.
Its gotta be close but the json looks like this:
data      [{"[object+Object]":0},{"[object+Object]":1}] that its sending
0
leakim971PluritechnicianCommented:
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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

tjyoungAuthor 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
0
tjyoungAuthor 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}]
0
tjyoungAuthor 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
0
leakim971PluritechnicianCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tjyoungAuthor 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.
0
leakim971PluritechnicianCommented:
please don't forget to close the question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Laravel

From novice to tech pro — start learning today.