How to reorder table rows using drag and drop and save the sequence in database

How to reorder table rows using drag and drop and automatically save the sequence in database.

Sample Table code start

<table class=”table table-bordered” id=”tbl_1″>
<thead>
<tr>
<th width=”25%”>Column1 Label</th>
<th width=”25%”>Column2 Label</th>
<th width=”25%”>Column3 Label</th>
<th width=”25%”>Column4 Label</th>
</tr>
</thead>
<tbody>
<tr idpcid=”1″ style=”cursor: pointer;” draggable=”true” ondragstart=”dragit(event,’tbl_1′)” ondragover=”dragover(event,’tbl_1′)” ondragend=”saveItemOrder(event,’tbl_1′)”>
<td>Row 1 Col1 Val 1</td>
<td>Row 1 Col2 Val 2</td>
<td>Row 1 Col3 Val 1</td>
<td>Row 1 Col4 Val 1</td>
</tr>
<tr idpcid=”2″ style=”cursor: pointer;” draggable=”true” ondragstart=”dragit(event,’tbl_1′)” ondragover=”dragover(event,’tbl_1′)” ondragend=”saveItemOrder(event,’tbl_1′)”>
<td>Row 2 Col1 Val 1</td>
<td>Row 2 Col2 Val 2</td>
<td>Row 2 Col3 Val 1</td>
<td>Row 2 Col4 Val 1</td>
</tr>
<tr idpcid=”233″ style=”cursor: pointer;” draggable=”true” ondragstart=”dragit(event,’tbl_1′)” ondragover=”dragover(event,’tbl_1′)” ondragend=”saveItemOrder(event,’tbl_1′)”>
<td>Row 3 Col1 Val 1</td>
<td>Row 3 Col2 Val 2</td>
<td>Row 3 Col3 Val 1</td>
<td>Row 3 Col4 Val 1</td>
</tr>
<tr idpcid=”787″ style=”cursor: pointer;” draggable=”true” ondragstart=”dragit(event,’tbl_1′)” ondragover=”dragover(event,’tbl_1′)” ondragend=”saveItemOrder(event,’tbl_1′)”>
<td>Row 4 Col1 Val 1</td>
<td>Row 4 Col2 Val 2</td>
<td>Row 4 Col3 Val 1</td>
<td>Row 4 Col4 Val 1</td>
</tr>
</tbody>
</table>

You Javascript code start

// Code to drag and reorder the post trip audit item start
let targetRow
function ondragstart(event,tblid){
targetRow=event.target;
}
function dragover(e,tblid){
let children=Array.from(e.target.parentNode.parentNode.children);
if(children.indexOf(e.target.parentNode)>children.indexOf(targetRow))
{
e.target.parentNode.after(targetRow);
// setItemSeq(tblid).delay(5000);
}
else
{
e.target.parentNode.before(targetRow);
// setItemSeq(tblid).delay(5000);
}
}

// This function basically catch all the row under the target table with the id tbl_1 and club into into one post array, to process it on server side
function saveItemOrder(e,tblid)
{
var TableData = new Array();
jQuery(‘#’+tblid+’ tbody tr’).each(function(row, tr){
TableData = TableData + ‘seq[‘+row+’]=’+jQuery(this).attr(‘idpcid’)+’&’;
});
console.log(TableData); // This will give all the table data row value which is set under the attribute name “idpcid”
jQuery.post(“example.php?action=setRowSeq”,TableData).done(function( data )
{
// alert( “Data Loaded: ” + data );
console.log( “Response Data Loaded: ” + data );
});
}
// Code to drag and reorder the post trip audit item end

// Server Side code start , that goes into example.php file

if(isset($_GET[‘action’]) && $_GET[‘action’] == ‘setRowSeq’)
{
if(isset($_POST[‘seq’]) && !empty($_POST[‘seq’]))
{
$seq = $_REQUEST[‘seq’];
}
else
{
$seq = [];
}

// $seq is the data array which is posted as “TableData” in the above javascript code
if(!empty($seq))
{
$seqInsertionErr = ”;
$seqOrderToSet = 1;
foreach ($seq as $skey => $svalue)
{
$seqData = array(
‘table_colunm_which_store_seq’ => $seqOrderToSet
);
// $dbVr is database connection object . you can also simple mysql query to update the table value
$dbVr->where(‘table_column_name’,$svalue);
$dbVr->update (‘target_table_name’, $seqData);
if ($dbVr->getLastError() != “”)
{
$seqInsertionErr .= ‘Update failed due to : ‘ . $dbVr->getLastError();
}
$seqOrderToSet++;
}
if($seqInsertionErr == “”){
echo “Sequence updated Successfully for Items”;
}
else
{
echo “Error in updating Sequence due to “.$seqInsertionErr;
}

}
else
{
echo ‘Required parameter required for changing the order is missing’;exit;
}
exit();

}