how to send parameters if my data is extracted from a view, serverside

cris19n

i am using serverside.

<?php
    require 'serverside.php';
    $table_data->get('name_table', 'id_table', array('dt1','dt2','dt3','dt4','dt5','dt6'));

but my table has no id, since it is a view that stores information from other tables, which must go in id_table, it can go null or omit this parameter?

allan

Where did your serverside.php script come from? The API being used there isn't one I immediately recognise, so I'm not sure if the primary key can be nulled out or not.

In our own server-side processing file, the primary key is not optional as we use it for the count() (it has an index, so it should be fast). The script could be readily modified if you need though.

Allan

cris19n

este es mi archivo serverside.php

<?php
include 'serversideConexion.php';
class TableData {
    private $_db;
   public function __construct() {
       try {
           $host        = SERVIDOR;
           $database    = BD;
           $user        = USUARIO;
           $passwd      = PASS;
           $this->_db = new PDO('mysql:host='.$host.';dbname='.$database, $user, $passwd, array(
               PDO::ATTR_PERSISTENT => true, PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
       } catch (PDOException $e) {
           error_log("Failed to connect to database: ".$e->getMessage());
       }
   }
   public function get($table, $index_column, $columns) {
       // Paging
       $sLimit = "";
       if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) {
           $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".intval( $_GET['iDisplayLength'] );
       }

       // Ordering
       $sOrder = "";
       if ( isset( $_GET['iSortCol_0'] ) ) {
           $sOrder = "ORDER BY  ";
           for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ ) {
               if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" ) {
                   $sortDir = (strcasecmp($_GET['sSortDir_'.$i], 'ASC') == 0) ? 'ASC' : 'DESC';
                   $sOrder .= "`".$columns[ intval( $_GET['iSortCol_'.$i] ) ]."` ". $sortDir .", ";
               }
           }

           $sOrder = substr_replace( $sOrder, "", -2 );
           if ( $sOrder == "ORDER BY" ) {
               $sOrder = "";
           }
       }

       /*
        * Filtering
        * NOTE this does not match the built-in DataTables filtering which does it
        * word by word on any field. It's possible to do here, but concerned about efficiency
        * on very large tables, and MySQL's regex functionality is very limited
        */
       $sWhere = "";
       if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
           $sWhere = "WHERE (";
           for ( $i=0 ; $i<count($columns) ; $i++ ) {
               if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" ) {
                   $sWhere .= "`".$columns[$i]."` LIKE :search OR ";
               }
           }
           $sWhere = substr_replace( $sWhere, "", -3 );
           $sWhere .= ')';
       }

       // Individual column filtering
       for ( $i=0 ; $i<count($columns) ; $i++ ) {
           if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) {
               if ( $sWhere == "" ) {
                   $sWhere = "WHERE ";
               }
               else {
                   $sWhere .= " AND ";
               }
               $sWhere .= "`".$columns[$i]."` LIKE :search".$i." ";
           }
       }

       // SQL queries get data to display
       $sQuery = "SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $columns))."` FROM `".$table."` ".$sWhere." ".$sOrder." ".$sLimit;
       $statement = $this->_db->prepare($sQuery);

       // Bind parameters
       if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
           $statement->bindValue(':search', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
       }
       for ( $i=0 ; $i<count($columns) ; $i++ ) {
           if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) {
               $statement->bindValue(':search'.$i, '%'.$_GET['sSearch_'.$i].'%', PDO::PARAM_STR);
           }
       }

       $statement->execute();
       $rResult = $statement->fetchAll();

       $iFilteredTotal = current($this->_db->query('SELECT FOUND_ROWS()')->fetch());

       // Get total number of rows in table
       $sQuery = "SELECT COUNT(`".$index_column."`) FROM `".$table."`";
       $iTotal = current($this->_db->query($sQuery)->fetch());

       // Output
       $output = array(
           "sEcho" => intval($_GET['sEcho']),
           "iTotalRecords" => $iTotal,
           "iTotalDisplayRecords" => $iFilteredTotal,
           "aaData" => array()
       );

       // Return array of values
       foreach($rResult as $aRow) {
           $row = array();          
           for ( $i = 0; $i < count($columns); $i++ ) {
               if ( $columns[$i] == "version" ) {
                   // Special output formatting for 'version' column
                   $row[] = ($aRow[ $columns[$i] ]=="0") ? '-' : $aRow[ $columns[$i] ];
               }
               else if ( $columns[$i] != ' ' ) {
                   $row[] = $aRow[ $columns[$i] ];
               }
           }
           $output['aaData'][] = $row;
       }

       echo json_encode( $output );
   }
}
$table_data = new TableData();
?>

cris19n

if i use your file
server_processing.php
can I send the variable $primaryKey empty

link: https://github.com/DataTables/DataTablesSrc/blob/944252f7004cdfdc9ad14b6e6d400b1651c6aa9e/examples/server_side/scripts/server_processing.php

allan

can I send the variable $primaryKey empty

No - as I mentioned above:

In our own server-side processing file, the primary key is not optional as we use it for the count()

It looks like the use is similar in your own script, so you could modify it to use COUNT(*) instead if you wanted.

Allan