Call for remote paging

[mewbie]

Hello,

I tried to replicate remote paging from https://paramquery.com/pro/demos/paging
to my own API call. It's already ok for local paging call but still won't work for remote,

Code: [Select]

    var dataModel = {
        recIndx: "TicketNo",
        location: "remote",
        dataType: "text",
        method: "GET",
        sorting: "local",
        paging: "remote",
        sortIndx: "TicketNo",
        sortDir: "down",
        url: "/ticketingAPI.php?job=get_tickets"
        , getData: function (dataJSON) {
            var data = dataJSON.data;
            return { curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords, data: data };
        }
    };

    // $("#grid_crud").pqGrid(
    var newObj = {
        height: 'flex',
        resizable: true,
        flexHeight: true,
        flexWidth: false,
        collapsible: true,
        hoverMode: 'row',
        // maxHeight: 400,
        pageModel: { type: "remote", rPP: 20, strRpp: "{0}" },
        dataModel: dataModel,
        colModel: colModel,
        filterModel: { mode: 'OR', header: true },
   // the rest
--------------------------------------

Code: [Select]

  if($job == 'get_tickets')
  {
    if (isset($_GET["pq_curpage"]) && isset($_GET["pq_rpp"]) ){   
      $pq_curPage = $_GET["pq_curpage"];
      $pq_rPP=$_GET["pq_rpp"];

      $sql = "Select count(*) from noc_tickets";
      $dsn = 'mysql:host='.$db_server.';dbname='.$db_name;
        $options = array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
      );
      $dbh = new PDO($dsn, $db_username, $db_username, $options);
      $stmt = $dbh->query($sql);   
      $total_Records = $stmt->fetchColumn();
     
      $skip = ($pq_rPP * ($pq_curPage - 1));

      if ($skip >= $total_Records)
      {       
          $pq_curPage = ceil($total_Records / $pq_rPP);
          $skip = ($pq_rPP * ($pq_curPage - 1));
      }
      // Get tickets
      $query = "SELECT * FROM noc_tickets ORDER BY `TicketNo` limit ".$skip." , ".$pq_rPP;
      $query = mysqli_query($db_connection, $query);
      if (!$query){
        $result  = 'error';
        $message = 'query error';
      } else {
        $result  = 'success';
        $message = 'query success';
        while ($ticketing = mysqli_fetch_array($query)){
          $mysql_data[] = array(
            "TicketNo"  => $ticketing['TicketNo'],
            "Title"  => $ticketing['Title'],
            "Status"    => $ticketing['Status'],
            "Carrier"       => $ticketing['Carrier'],
            "CustomerName"  => $ticketing['CustomerName'],
            "Service"  => $ticketing['Service'],
            "Downtime"     => $ticketing['Downtime'],
            "Uptime"     => $ticketing['Uptime'],
            "Duration"     => $ticketing['Duration'],
            "Summary"     => $ticketing['Summary'],
            "NextAction"     => $ticketing['NextAction'],
            "RFO"     => $ticketing['RFO']
          );
        }
      }
    }
  }
--------------------------
Any idea what's wrong?
Thanks

[paramvir]

1. $job is a $_GET variable. $job = $_GET['job']

2. You are supposed to json_encode the response.

3. and echo that response from the server.

4. Response is supposed to be in this format:

{"totalRecords":2155,"curPage":2,"data":[{},....]}

https://paramquery.com/pro/invoice/paging?pq_datatype=JSON&pq_curpage=2&pq_rpp=20&_=1514561945254

[mewbie]

Thank you for your response,

I had to look again at my code and made fix with
     

Code: [Select]

$dbh = new PDO($dsn, $db_username, $db_username, $options);
supposed to be :
     

Code: [Select]

$dbh = new PDO($dsn, $db_username, $db_password, $options);
and the rest of the code following in the demo page and got me successfully echo the data from my endpoint in
{"totalRecords":10,"curPage":1,"data":[{ .... format

but I'm still unable to get the records on the grid and wonder if it has something to do with old query response in this format :
{"result":"success","message":"query success","data":[{

I put console.log(dataJSON); and got this error :

Code: [Select]

uncaught exception: Error : SyntaxError: JSON.parse: unexpected non-whitespace character after JSON data at line 1 column 5487 of the JSON data
Is it a good idea to break $sb and $product in demo page to accommodate the old format (like $product broken down into mysqli_fetch_array first)? 


 

[mewbie]

Just a quick head-up,

I'm finally able to display the data with :

Code: [Select]

       
        $sql ="SELECT * FROM noc_tickets ORDER BY `TicketNo` limit ".$skip." , ".$pq_rPP;
        $stmt = $dbh->query($sql);
        if (!$stmt){
          $result  = 'error';
          $message = 'query error';
        } else {
          $result  = 'success';
          $message = 'query success';
          $total_Records = $total_Records;
          $curPage = $pq_curPage;     
          // $products = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $query = mysqli_query($db_connection, $sql);
            while ($ticketing = mysqli_fetch_array($query)){
            $mysql_data[] = array(
              "TicketNo"  => $ticketing['TicketNo'],
              "Title"  => $ticketing['Title'],
// the rest of the code
But still got error at the bottom of the grid "page 1 of NaN"

[paramvir]

Please check the value of totalRecords send by your remote script.

[mewbie]

Yes already solved it. Thank you for your help.