serverSide causing table to return to the top after edit?

serverSide causing table to return to the top after edit?

rob1strob1st Posts: 84Questions: 22Answers: 0

I have two identical scripts on the test server.

One has serverSide enabled, the other doesn't. When I edit on the server side file, the table scrolls to the top, when I edit on the client side one, it doesn't. Both use the same ajax call.

test pages can be found here:

serverSide: https://test.assettrack.cx/asset/displayAssets_ss.php
clientSide: https://test.assettrack.cx/asset/displayAssets_cs.php

The code for the files is below, but apart from serverSide they are (should be) identifcal.

serverSide:

<?php

if(isset($_GET['loc'])) {
  $loc = clean($_GET['loc']);
  $loc = escape($loc);
  $_SESSION['loc'] = $loc;
  $sql = "SELECT docWBS FROM loc WHERE id = $loc";
  $result = query($sql);
  $row = mysqli_fetch_array($result);
  $_SESSION['docWBS'] = $row[0].'%';
} else {
  $_SESSION['loc'] = null;
  $loc = null;
  $_SESSION['docWBS'] = null;
}

if(isset($_GET['disc'])) {
  $disc = clean($_GET['disc']);
  $disc = escape($disc);
  $_SESSION['disc'] = $disc;
} else {
  $_SESSION['disc'] = null;
  $disc = null;
}

if(isset($_GET['at'])) {
  $at = clean($_GET['at']);
  $at = escape($at);
  $_SESSION['at'] = $at;
} else {
  $_SESSION['at'] = null;
  $at = null;
}

if(isset($_GET['ss'])) {
  $ss = clean($_GET['ss']);
  $ss = escape($ss);
  $_SESSION['ss'] = $ss;
} else {
  $_SESSION['ss'] = null;
  $ss = null;
}

if(isset($_GET['e'])) {
  $e = clean($_GET['e']);
  $e = escape($e);
  $_SESSION['e'] = $e;
} else {
  $_SESSION['e'] = null;
  $e = null;
}

if(isset($_GET['g'])) {
  $g = clean($_GET['g']);
  $g = escape($g);
  $_SESSION['g'] = $g;
} else {
  $_SESSION['g'] = null;
  $g = null;
}

if(isset($_GET['status'])) {
  $status = clean($_GET['status']);
  $status = escape($status);
  $_SESSION['status'] = $status;
} else {
  $_SESSION['status'] = null;
  $status = null;
}

?>
<header class='container-fluid page-header text-center'>
  <h1 class='page-title'>Assets</h1>
</header>
<div class="d-lg-none text-center mx-2"><h4 class="text-danger">This page is not designed to be read on a small screen</h4></div>
<?php
display_message();
?>

<input type="hidden" id='role' value="<?php echo $role ?>">

<div class="container-fluid ms-7 w-auto">
  <div class="card">
    <div class="card-body">
      <div id='phaseTable' class='content'>
        <table id='example' class='stripe row-border order-column' style="width:100%">
          <thead>
            <tr>
              <th  data-priority="20" class='text-center'>Drawing ID</th>
              <th  data-priority="30" class='text-center'>Spec Tag</th>
              <th  data-priority="20" class='text-center'>Location</th>
              <th  data-priority="20" class='text-center'>Building</th>
              <th  data-priority="40" class='text-center'>Room</th>
              <th  data-priority="40" class='text-center'>Level</th>
              <th  data-priority="50" class='text-center'>Type</th>
              <th  data-priority="60" class='text-center'>Entity</th>
              <th  data-priority="100" class='text-center'>Design</th>
              <th  data-priority="70" class='text-center'>Status</th>
              <th  data-priority="600" class='text-center'>Old ID</th>
              <th  data-priority="80" class='text-center'>PICO Status</th>
              <th  data-priority="90" class='text-center'>SAT Status</th>
              <th  data-priority="95" class='text-center'>Spec</th>
              <th  data-priority="92" class='text-center'>Subsystem</th>
              <th  data-priority="200" class='text-center'>Discipline</th>
              <th  data-priority="200" class='text-center'>PICO</th>
              <th  data-priority="300" class='text-center'>SAT</th>
              <th  data-priority="400" class='text-center'>BIC</th>
              <th  data-priority="500" class='text-center'>Phase</th>
              <th  data-priority="31" class='text-center'>Seq</th>
              <th  data-priority="1500" class='text-center'>Comments</th>
            </tr>
          </thead>
        </table>
      </div>
    </div>
  </div>
</div>

<script type="text/javascript" language="javascript" class="init">
var editor;
var permission = '<?php echo $role ?>';
var client = '<?php echo $client ?>';

$(document).ready(function() {

  var editor = new $.fn.dataTable.Editor( {
    ajax: "../ajax/at/assets.php",
    table: "#example",
    fields: [ 
      { 
        label: 'Location', 
        name: 'A.loc',
        type: 'select',
        placeholderDisabled: false,
        placeholder: "Select location"
        
      }, { 
          label: 'Design Tag',  
          name: 'designTag' 
      }, { 
          label: 'Room',  
          name: 'room'
      }, 
      {  
          label: 'Building',  
          name: 'A.building',
          type: 'select',
          placeholderDisabled: false,
          placeholder: "Choose Building (Optional)"
      },
      {  
          label: 'Level',  
          name: 'A.levels',
          type: 'select',
          placeholderDisabled: false,
          placeholder: "Choose Level"
      }, 
      {  
          label: 'Asset Type',  
          name: 'A.assetType',
          type: 'select',
          placeholderDisabled: false,
          placeholder: "Choose Asset Type"
      }, { 
          label: 'Entity',  
          name: 'A.entity',
          type: 'select',
          placeholderDisabled: false,
          placeholder: "Choose entity"
      }, {  
          label: 'Drawing',  
          name: 'design'
      }, {  
          label: 'Status',  
          name: 'A.assetStatus',
          type: 'select',
          placeholderDisabled: false,
          placeholder: "Select asset Status"
      }, { 
          label: 'Old ID',  
          name: 'oldID',
          multiEditable: false
      }, {  
          label: 'Subsystem',  
          name: 'subsystem',
          type: 'hidden'
      }, 
      {  
          label: 'PICO Report',  
          name: 'A.picoReport',
          type: 'select',
          placeholderDisabled: false,
          placeholder: "Select PICO Report"
      }, 
      { 
          label: 'PICO Status',  
          name: 'A.picoReportStatus',
          type: 'select',
          placeholderDisabled: false,
          placeholder: "PICO Report Status"
      },
      {  
          label: 'SAT Report',  
          name: 'A.satReport',
          type: 'select',
          placeholderDisabled: false,
          placeholder: "Select SAT Report"
      },  
      {
          label: 'SAT Status',  
          name: 'A.satReportStatus',
          type: 'select',
          placeholderDisabled: false,
          placeholder: "SAT Report Status"  
      }, {  
          label: 'Updated By',  
          name: 'A.updatedBy',
          type: 'hidden'  
      }, {  
          label: 'Ball in court',  
          name: 'A.bic',
          type: 'select',
          placeholderDisabled: false,
          placeholder: "Choose person (Optional)" 
      }, 
      {  
          label: 'Phase',  
          name: 'A.phase',
          type: 'select',
          placeholderDisabled: false,
          placeholder: "Choose phase (Optional)"
      },
      { 
          label: 'Sequence ID',  
          name: 'seq',
        },
      { 
          label: 'Comments',  
          name: 'A.comment',
          type: 'textarea' 
        },
    ],
    formOptions: {
            main: {
                scope: 'cell' // Allow multi-row editing with cell selection
            }
        },    
  } );

  var table = $('#example').DataTable( {
    dom: 'lBfrtip',
    
    serverSide: true,
    processing: true,
    ajax: {
      url: "../ajax/at/assets.php",
      type: "POST",
      deferRender: true,
    },

    //TABLE WINDOW
    scrollY:        "65vh",
    scrollX:        true,
    scrollCollapse: true,
    paging:         true,
    fixedColumns:   {
      left: 2
    },
    
        columns: [
      { data: "designTag",
        className: "dt-nowrap",
      },
      { data: "specTag",
        className: "dt-nowrap", 
      },
      { data: "L.LocationName" },
      { data: "B.buildingTLA" },
            { data: "room" },
            { data: "L1.levelTLA" },
            { data: "T.assetType" },
      { data: "E.entityTLA" },
      { data: "design",
        className: "dt-nowrap", 
      },
      { data: "S.assetStatus" },
      { data: "oldID" },
      { data: "PR" },
      { data: "SR" },
      { data: "T.spec",
        className: "dt-nowrap",
      },
      { data: "subsystem" },
      { data: "D.discipline" },
      { data: "PICO",
        className: "dt-nowrap",
      },
      { data: "SAT",
        className: "dt-nowrap",
      },
      { data: "U.username" },
      { data: "P.assetPhase" },
      { data: "seq" },
      { data: "A.comment" },
        ],

    columnDefs:[
      {
        searchPanes: {
            show: false
        },
        targets: [0,1,2,8,10,13,20,21]
      }
  ],

    //SHOW SELECTION & SAVE PREVIOUS STATE
    select: true,
    stateSave: true,

    //DISABLE AUTOWIDTH
    "autoWidth" : false,

    //BUTTONS
    buttons: [],

    //PAGINATION OPTIONS
    "pageLength": 250,
    "lengthMenu": [[50, 100, 250, 500, -1], [50, 100, 250, 500, "All"]],
  } );

    table.button().add(
      null, { extend: "edit", editor: editor },
    );

} );
</script>

ClientSide is the same but serverSide is false.

Any idea why I have the issue?

Debug scrip shows all files up to date.

configuration data uploaded. https://debug.datatables.net/idimaw

This question has an accepted answers - jump to answer

Answers

  • rob1strob1st Posts: 84Questions: 22Answers: 0

    The controller is:

    <?php
    //SESSION START
    if(!isset($_SESSION)) { 
        session_start(); 
      }
      
    if(isset($_SESSION['loc'])) {
      $loc = 1;
    } else {
      $loc = 1;
    }
    
    if(isset($_SESSION['docWBS'])) {
      $docWBS = $_SESSION['docWBS'];
    } else {
      $docWBS = 1;
    }
    
    if(isset($_SESSION['at'])) {
      $at = $_SESSION['at'];
    } else {
      $at = null;
    }
    if(isset($_SESSION['ss'])) {
      $ss = $_SESSION['ss'];
    } else {
      $ss = null;
    }
    if(isset($_SESSION['disc'])) {
      $disc = $_SESSION['disc'];
    } else {
      $disc = null;
    }
    if(isset($_SESSION['e'])) {
      $e = $_SESSION['e'];
    } else {
      $e = null;
    }
    if(isset($_SESSION['g'])) {
      $g = $_SESSION['g'];
    } else {
      $g = null;
    }
    if(isset($_SESSION['status'])) {
      $status = $_SESSION['status'];
    } else {
      $status = null;
    }
    
    include("../lib/DataTables.php");
    
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Join,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
        //DataTables\Editor\SearchPaneOptions;
    
    Editor::inst( $db, 'asset A', 'A.id' )
    ->field(
        //Static Fields - No Validation
        Field::inst( 'A.id' ),
        Field::inst( 'S.assetStatus' ),
        Field::inst( 'ST.assetTag AS specTag' ),
        Field::inst( 'T.assetType' ),
        Field::inst( 'D.discipline' ),
        Field::inst( 'E.entityTLA' ),
        Field::inst( 'L1.levelTLA' ),
        Field::inst( 'L.LocationName' ),
        Field::inst( 'A.oldID AS oldID' ),             
        Field::inst( 'TR.reportStatus AS PR' ),  
        Field::inst( 'TR1.reportStatus AS SR' ), 
        Field::inst( 'T.spec' ),
        Field::inst( 'SY.systemName AS subsystem' ),
        Field::inst( 'P.assetPhase' ),  
        Field::inst( 'C1.procedureTag AS PICO' ), 
        Field::inst( 'C2.procedureTag AS SAT' ),
        Field::inst( 'U.username' ),
        Field::inst( 'A.comment' ),
        Field::inst( 'A.seq AS seq' ),
        Field::inst( 'A.Room AS room' ),
        Field::inst( 'B.buildingTLA' ),
        
        //Static fields - with validation
        Field::inst( 'A.dwgTag AS designTag' )
          ->validator( Validate::notEmpty( ValidateOptions::inst()
            ->message( 'What is the asset called on the design?' )
          ) ),
        Field::inst( 'A.dwgNo AS design' ),
    
        //Select Fields
        Field::inst( 'A.loc' )
          ->options( Options::inst()
            ->table('loc')
            ->value('id')
            ->label('LocationName')
            ->order('id')
          )
          ->validator( Validate::dbValues() )
          ->validator( Validate::notEmpty( ValidateOptions::inst()
            ->message( 'What location are we using?' )
          ) ), 
    
        Field::inst( 'A.building' )
          ->options( Options::inst()
            ->table('building')
            ->value('id')
            ->label('building')
            ->order('building')
          )
          ->setFormatter( Format::ifEmpty( null )),
        
        Field::inst( 'A.assetType' )
          ->options( Options::inst()
            ->table('assettype')
            ->value('assetTypeID')
            ->label('assetType')
          )
          ->validator( Validate::dbValues() )
          ->validator( Validate::notEmpty( ValidateOptions::inst()
            ->message( 'What type of asset is it?' )
          ) ),
     
        Field::inst( 'A.entity' )
          ->options( Options::inst()
            ->table('entity')
            ->value('entityID')
            ->label('entityTLA')
            ->order('entityID')
          )
          ->validator( Validate::dbValues() )
          ->validator( Validate::notEmpty( ValidateOptions::inst()
            ->message( 'Which entity will own the asset when in service?' )
          ) ),
    
        Field::inst( 'A.assetStatus' )
          ->options( Options::inst()
            ->table('assetstatus')
            ->value('id')
            ->label('assetStatus')
            ->order('id')
          )
          ->validator( Validate::dbValues() )
          ->validator( Validate::notEmpty( ValidateOptions::inst()
            ->message( 'What status are we allocating?' )
          ) ),
    
        Field::inst( 'A.bic' )
          ->options( Options::inst()
            ->table('users_enc')
            ->value('id')
            ->label( array('lastname','firstname') )
            ->Render( function ( $row ) {
              return $row['lastname'].', '.$row['firstname'];
            } )
            ->order('lastname')
          )
        ->validator( Validate::dbValues() )
        ->setFormatter( Format::ifEmpty( null )),
    
        Field::inst( 'A.phase' )
          ->options( Options::inst()
            ->table('assetPhase')
            ->value('phaseID')
            ->label('assetPhase')
            ->order('assetPhase')
          )
          ->validator( Validate::dbValues() )
          ->setFormatter( Format::ifEmpty( null )),
        
        Field::inst( 'A.levels' )
          ->options( Options::inst()
            ->table('levels')
            ->value('levelID')
            ->label('levels')
            ->order('levels')
          )
          ->validator( Validate::dbValues() )
          ->setFormatter( Format::ifEmpty( null )),
    
          Field::inst( 'A.picoReport' )
          ->options( Options::inst()
            ->table('reports')
            ->value('id')
            ->label( array('docConNo', 'revision'))
            ->render( function ( $row ) {
              return $row['docConNo'].'_'.$row['revision'];
          } )
            ->where( function ( $q )  use ( $docWBS ) {
              $q->where('docConNo', '%XQ%', 'LIKE');
              $q->and_where('docConNo', $docWBS, 'LIKE');
            })
            ->order('docConNo')
          )
          ->validator( Validate::dbValues() )
          ->setFormatter( Format::ifEmpty( null )),
    
          Field::inst( 'A.satReport' )
          ->options( Options::inst()
            ->table('reports')
            ->value('id')
            ->label( array('docConNo', 'revision'))
            ->render( function ( $row ) {
              return $row['docConNo'].'_'.$row['revision'];
          } )
          ->where( function ( $q )  use ( $docWBS ) {
            $q->where('docConNo', '%XR%', 'LIKE');
            $q->and_where('docConNo', $docWBS, 'LIKE');
          })
            ->order('docConNo')
          )
          ->validator( Validate::dbValues() )
          ->setFormatter( Format::ifEmpty( null )),
    
          Field::inst( 'A.satReportStatus' )
          ->options( Options::inst()
            ->table('testStatus')
            ->value('reportStatusID')
            ->label( 'reportStatus')
            ->order('reportStatusID')
          )
          ->validator( Validate::dbValues() )
          ->setFormatter( Format::ifEmpty( null )),
    
          Field::inst( 'A.picoReportStatus' )
          ->options( Options::inst()
            ->table('testStatus')
            ->value('reportStatusID')
            ->label( 'reportStatus')
            ->order('reportStatusID')
          )
          ->validator( Validate::dbValues() )
          ->setFormatter( Format::ifEmpty( null )),
    
        //HIDDEN FIELDS
        Field::inst( 'A.updatedBy' )
            ->set('true')
            ->setValue( 1 ),
        Field::inst( 'A.dateCreated' )
            ->set(Field::SET_CREATE)
            ->setValue( date("Y-m-d H:i:s") ),
    )
      
    
    ->leftJoin( 'assettype T', 'T.assetTypeID', '=', 'A.assetType' )
    ->leftJoin( 'entity E', 'E.entityID', '=', 'A.entity' )
    ->leftJoin( 'loc L', 'L.id', '=', 'A.loc' )
    ->leftJoin( 'assetstatus S', 'S.id', '=', 'A.assetStatus' )
    ->leftJoin( 'testStatus TR', 'TR.reportStatusID', '=', 'A.picoReportStatus' )
    ->leftJoin( 'testStatus TR1', 'TR1.reportStatusID', '=', 'A.satReportStatus' )
    ->leftJoin( 'system SY', 'SY.id', '=', 'T.subsystem' )
    ->leftJoin( 'cxprocedure C1', 'C1.id', '=', 'T.PICO' )
    ->leftJoin( 'cxprocedure C2', 'C2.id', '=', 'T.SAT' )
    ->leftJoin( 'users_enc U', 'U.id', '=', 'A.bic' )
    ->leftJoin( 'assetPhase P', 'P.phaseID', '=', 'A.phase' )
    ->leftJoin( 'assetTag ST', 'ST.assetID', '=', 'A.id' )
    ->leftJoin( 'discipline D', 'D.disciplineID', '=', 'SY.discipline' )
    ->leftJoin( 'levels L1', 'L1.levelID', '=', 'A.levels' )
    ->leftJoin( 'building B', 'B.id', '=', 'A.building' )
    ->where( function ( $q ) use ( $loc, $at, $ss, $e, $disc, $g, $status ) {
      if(isset($status)) {
        $q->where('A.assetStatus', 99, '<');
      } else {
        $q->where('A.assetStatus', 2, '!=');
      }
      if(isset($loc)) {
        $q->and_where('A.loc', $loc, '='); 
      }
      if(isset($at)) {
        $q->and_where('A.assetType', $at, '='); 
      }
      if(isset($ss)) {
        $q->and_where('T.subsystem', $ss, '='); 
      }
      if(isset($e)) {
        $q->and_where('A.entity', $e, '='); 
      }
      if(isset($disc)) {
        $q->and_where('SY.discipline', $disc, '='); 
      }
      if(isset($g)) {
        $q->and_where('D.FS', $g, '='); 
      }
      if(isset($status)) {
        $q->and_where('A.assetStatus', $status, '='); 
      }
    } )
    ->debug(true)
    ->process( $_POST )
    ->json();
    ?>
    
  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    This thread should help, it's asking the same thing.

    Cheers,

    Colin

  • rob1strob1st Posts: 84Questions: 22Answers: 0

    Thanks Colin,

    I did look at that and the drawType, Loren uses inline editing, which I don't. And Allan did say he was looking to update this in the package so I thought it may be another issue.

    When I use the drawType with an inline fuction, I don't have any issues, the table stays where it is.

    I tried creating the function without inline editing as:

    $('#example').on( 'change', 'tbody td', function () {
        editor.edit( this, {
            drawType: 'none',
        } );
      } );
    

    When I do this there is no change to the behaviour and after update the page returns to the top. Did I miss something?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    edited October 2021 Answer ✓

    That's close, but in the wrong place, I think. On line 245 of your code above, add:

            formOptions: {
              main: {
                scope: 'cell', // Allow multi-row editing with cell selection
                drawType: 'none' // <<<<<<<< this is the one here
              }
            },
    

    I tried it locally here and it's not resetting the scroll position, so hopefully that'll do the trick for you,

    Colin

  • rob1strob1st Posts: 84Questions: 22Answers: 0

    Thanks Colin, I'll add that when I get home tonight and if all good mark it as the answer, appreciate the help.

  • rob1strob1st Posts: 84Questions: 22Answers: 0
    edited October 2021

    Worked perfectly thanks, for those using this as reference and copying, note the missing comma after 'cell'.

    Thanks Colin.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Good to hear, thanks for reporting, and comma added in the example above :)

    Colin

Sign In or Register to comment.