Problems to showing connection db with ssp.class.php

ITC

Hi,
i want to use the datatables for my table, by connecting with my database.
The connection with my DB works, but i have some problems, becouse ssp.class.php file cannot be displayed properly.
Here is the error that is showed is : Undefined index: dt in Test_collegamento_db/ssp.class.php on line 331 and Undefined index: db in Test_collegamento_db/ssp.class.php on line 331.

Is there someone who can help me to solve this problem?
Thank you so much for the support.

These are the php file that i’m using

SSP.CLASS.PHP
      <?php 
        class SSP {
            static function data_output ( $columns, $data )
            {
          $out = array();
               for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
                $row = array();
    
                for ( $j=0, $jen=count($columns) ; $j<$jen ; $j++ ) {
                     $column = $columns[$j];
   
    
                       if ( isset( $column['formatter'] ) ) {
                    $row[ $column['dt'] ] = $column['formatter']( $data[$i][ $column['db'] ], $data[$i] );
                    }
                    else {
                        $row[ $column['dt'] ] = $data[$i][ $columns[$j]['db'] ];
                    }
                }
                $out[] = $row;
            }
            return $out;
        } 
        static function db ($conn=mysqli_connect)
        {
            if ( is_array($conn) ) {
                return self::sql_connect($conn);
            }
    
            return $conn;
        }
        static function limit ( $request, $columns )
        {
            $limit = '';
    
            if ( isset($request['start']) && $request['length'] != -1 ) {
                $limit = "LIMIT ".intval($request['start']).", ".intval($request['length']);
            }
    
            return $limit;
        }    
        static function order ( $request, $columns )
        {
            $order = '';    
            if ( isset($request['order']) && count($request['order']) ) {
                $orderBy = array();
                $dtColumns = self::pluck( $columns, 'dt' );  
                for ( $i=0, $ien=count($request['order']) ; $i<$ien ; $i++ ) {                  
                    $columnIdx = intval($request['order'][$i]['column']);
                    $requestColumn = $request['columns'][$columnIdx];    
                    $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                    $column = $columns[ $columnIdx ];    
                    if ( $requestColumn['orderable'] == 'true' ) {
                        $dir = $request['order'][$i]['dir'] === 'asc' ?
                            'ASC' :
                            'DESC';
                        $orderBy[] = '`'.$column['db'].'` '.$dir;
                    }
                }   
                if ( count( $orderBy ) ) {
                    $order = 'ORDER BY '.implode(', ', $orderBy);
                }
            }    
            return $order;
        }
        static function filter ( $request, $columns, &$bindings )
        {
            $globalSearch = array();
            $columnSearch = array();
            $dtColumns = self::pluck( $columns, 'dt' );   
            if ( isset($request['search']) && $request['search']['value'] != '' ) {
                $str = $request['search']['value'];
                for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
                    $requestColumn = $request['columns'][$i];
                    $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                    $column = $columns[ $columnIdx ];
                    if ( $requestColumn['searchable'] == 'true' ) {
                        $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                        $globalSearch[] = "`".$column['db']."` LIKE ".$binding;
                    }
                }
            }           
            if ( isset( $request['columns'] ) ) {
                for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
                    $requestColumn = $request['columns'][$i];
                    $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                    $column = $columns[ $columnIdx ]; 
                    $str = $requestColumn['search']['value'];
                    if ( $requestColumn['searchable'] == 'true' &&
                     $str != '' ) {
                        $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                        $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
                    }
                }
            }       
            $where = '';
            if ( count( $globalSearch ) ) {
                $where = '('.implode(' OR ', $globalSearch).')';
            }
            if ( count( $columnSearch ) ) {
                $where = $where === '' ?
                    implode(' AND ', $columnSearch) :
                    $where .' AND '. implode(' AND ', $columnSearch);
            }    
            if ( $where !== '' ) {
                $where = 'WHERE '.$where;
            }    
            return $where;
        }    
        static function simple ( $request, $conn, $AZIENDE_TEST, $primaryKey, $columns )
        {
            $bindings = array();
            $db = self::db( $conn );        
            $limit = self::limit( $request, $columns );
            $order = self::order( $request, $columns );
            $where = self::filter( $request, $columns, $bindings );
            $data = self::sql_exec( $db, $bindings,
                "SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
                 FROM `$AZIENDE_TEST`
                 $where
                 $order
                 $limit"
            );          
            $resFilterLength = self::sql_exec( $db, $bindings,
                "SELECT COUNT({$primaryKey})
                 FROM   $AZIENDE_TEST
                 $where"
            );
            $recordsFiltered = $resFilterLength[0][0];          
            $resTotalLength = self::sql_exec( $db,
                "SELECT COUNT ({$primaryKey})
                 FROM   $AZIENDE_TEST"
            );
            $recordsTotal = $resTotalLength[0][0];    
            return array(
                "draw"            => isset ( $request['draw'] ) ?
                    intval( $request['draw'] ) :
                    0,
                "recordsTotal"    => intval( $recordsTotal ),
                "recordsFiltered" => intval( $recordsFiltered ),
                "data"            => self::data_output( $columns, $data )
            );
        }  
        static function complex ( $request, $conn, $AZIENDE_TEST, $primaryKey, $columns, $whereResult=null, $whereAll=null )
        {
            $bindings = array();
            $db = self::db($conn);
            $localWhereResult = array();
            $localWhereAll = array();
            $whereAllSql = '';              
            $limit = self::limit($request, $columns);
            $order = self::order($request, $columns);
            $where = self::filter($request, $columns, $bindings);    
            $whereResult = self::_flatten($whereResult);
            $whereAll = self::_flatten($whereAll);    
            if ($whereResult) {
                $where = $where ?
                    $where .' AND '.$whereResult :
                    'WHERE '.$whereResult;
            }    
            if ( $whereAll ) {
                $where = $where ?
                    $where .' AND '.$whereAll :
                    'WHERE '.$whereAll;
    
                $whereAllSql = 'WHERE '.$whereAll;
            }               
            $data = self::sql_exec( $db, $bindings,
                "SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
                 FROM `$AZIENDE_TEST`
                 $where
                 $order
                 $limit"
            );              
            $resFilterLength = self::sql_exec( $db, $bindings,
                "SELECT COUNT(`{$primaryKey}`)
                 FROM   `$AZIENDE_TEST`
                 $where"
            );
            $recordsFiltered = $resFilterLength[0][0];              
            $resTotalLength = self::sql_exec( $db, $bindings,
                "SELECT COUNT(`{$primaryKey}`)
                 FROM   `$AZIENDE_TEST` ".
                $whereAllSql
            );
            $recordsTotal = $resTotalLength[0][0];          
            return array(
                "draw"            => isset ( $request['draw'] ) ?
                    intval( $request['draw'] ) :
                    0,
                "recordsTotal"    => intval( $recordsTotal ),
                "recordsFiltered" => intval( $recordsFiltered ),
                "data"            => self::data_output( $columns, $data )
            );
        }   
        static function sql_connect ( $sql_details )
        {
            try {
    
                    $db = @new PDO (
                    "mysql:host={$sql_details['host']}; dbname={$sql_details['db']}",
                    $sql_details['user'],
                    $sql_details['pass'],
                    array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )
                );
            }
            catch (PDOException $e) {
                self::fatal(
                    "An error occurred while connecting to the database. ".
                    "The error reported by the server was: ".$e->getMessage()
                );
            }    
            return $db;
        }    
        static function sql_exec ( $db, $bindings, $sql=null )
        {           
            if ( $sql === null ) {
                $sql = $bindings;
            }    
            $stmt = $db->prepare( $sql );
            //echo $sql;        
            if ( is_array( $bindings ) ) {
                for ( $i=0, $ien=count($bindings) ; $i<$ien ; $i++ ) {
                    $binding = $bindings[$i];
                    $stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] );
                }
            }       
            try {
                $stmt->execute();
            }
            catch (PDOException $e) {
                self::fatal( "An SQL error occurred: ".$e->getMessage() );
            }           
            return $stmt->fetchAll( PDO::FETCH_BOTH );
        }    
        static function fatal ( $msg )
        {
            echo json_encode( array(
                "error" => $msg
            ) );    
            exit(0);
        }    
        static function bind (&$a, $val, $type)
        {
            $key = ':binding_'.count($a);    
            $a[] = array(
                'key' => $key,
                'val' => $val,
                'type' => $type
            );    
            return $key;
        }    
        static function pluck ( $a, $prop )
        {
            $out = array();
    
            for ( $i=0, $len=count($a) ; $i<$len ; $i++ ) {
                $out[] = $a[$i][$prop];
            }    
            return $out;
        }    
        static function _flatten ( $a, $join = ' AND ' )
        {
            if ( ! $a ) {
                return '';
            }
            else if ( $a && is_array($a) ) {
                return implode( $join, $a );
            }
            return $a;
        }
    }

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

ITC

This is the Connection DB file
Connection DB
<?php $table = 'AZIENDE_TEST'; $primaryKey = 'ID_AZIENDA'; $columns = array( array( 'db' => 'ID_AZIENDA', 'dt' => 0 ), array( 'db' => 'COGNOME', 'dt' => 1 ), array( 'db' => 'NOME', 'dt' => 2 ), array( 'db' => 'INDIRIZZO', 'dt' => 3 ), array( 'db' => 'PROVINCIA', 'dt' => 4 ), array( 'db' => 'COMUNE', 'dt' => 5 ), array( 'db' => 'CAP', 'dt' => 6 ), array( 'db' => 'SESSO', 'dt' => 7 ), array( 'db' => 'DATA_NASCITA', 'dt' => 8 ), array( 'db' => 'CF_PIVA', 'dt' => 9 ), array( 'formatter' => function( $d, $row ) { return date( 'Y-m-d', strtotime($d)); } ), ); $sql_details = array( 'host' => 'localhost', 'user' => 'root', 'pass' => '', 'db' => 'db-name' ); include ('Test_collegamento_db/ssp.class.php'); echo json_encode( SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns ) ); ?>

ITC

Connection DB file

 ```
<?php

// DB table to use
$table = 'AZIENDE_TEST';

// Table's primary key
$primaryKey = 'ID_AZIENDA';

$columns = array(
    array( 'db' => 'ID_AZIENDA',   'dt' => 0 ),
    array( 'db' => 'COGNOME',      'dt' => 1 ),
    array( 'db' => 'NOME',         'dt' => 2 ),
    array( 'db' => 'INDIRIZZO',    'dt' => 3 ),
    array( 'db' => 'PROVINCIA',    'dt' => 4 ),
    array( 'db' => 'COMUNE',       'dt' => 5 ),
    array( 'db' => 'CAP',          'dt' => 6 ),
    array( 'db' => 'SESSO',        'dt' => 7 ),
    array( 'db' => 'DATA_NASCITA', 'dt' => 8 ),
    array( 'db' => 'CF_PIVA',      'dt' => 9 ),
    array(
        'formatter' => function( $d, $row ) {
            return date( 'Y-m-d', strtotime($d));
        }
    ),

);

$sql_details = array(
      'host' => 'localhost',
      'user' => 'root',
      'pass' => '',
      'db'   => 'db-name'
);
// Include SQL query processing class
include ('Test_collegamento_db/ssp.class.php');

// Output data as json format
echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);


?> ```

allan

Can you show me your client-side initialisation code as well please? Or even better a link to the page showing the issue.

Thanks,
Allan

allan

Looking at it more:

Test_collegamento_db/ssp.class.php on line 331.

The PHP file you show above isn't 331 lines long. So I'm not sure what line the error is referring to.

static function db ($conn=mysqli_connect)

Have you modified this bit? Using mysqli_connect here won't work since the rest of the class is assuming it is PDO.

Allan

ITC

Hi @allan, thank you for the answare

[http://cropping.it/Test_collegamento_db/index.php] this is the link.

concerning mysqli_connect i forgot to remove it.

This is my index file.

  <!DOCTYPE html>
    <html lang="en-US">
    <head>
    <title>Datatables Server Site</title>
    <meta charset="utf-8">
    <link rel="stylesheet" type="text/css"  media="screen" href="main.css"/>
    <script src="https://code.jquery.com/jquery-3.3.1.js"></script>
    <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>



    <script>
    $(document).ready(function()){
       $('AZIENDE_TEST').DataTable({
           "processing":true,
           "serverSide":true,
           "ajax": "getData.php"
       }

    });
    </script>
    <style>
    </style>
    </head>
    <body>
      <table id="AZIENDE_TEST" class="display" style="width:100%">
        <thead>
            <tr>
                <th>ID_AZIENDA</th>
                <th>COGNOME</th>
                <th>NOME</th>
                <th>INDIRIZZO</th>
                <th>PROVINCIA</th>
                <th>COMUNE</th>
                <th>CAP</th>
                <th>SESSO</th>
                <th>DATA_NASCITA</th>
                <th>CF_PIVA</th>
            </tr>
      <?php
      include "getData.php";
        $AZIENDE_TEST = mysqli_query($conn,"SELECT * FROM AZIENDE_TEST") 
  or die(mysqli_error($conn));
           while($row = mysqli_fetch_array($AZIENDE_TEST))
            {
               echo "<tr>
                 <td>".$row['ID_AZIENDA']."</td>
                 <td>".$row['COGNOME']."</td>
                 <td>".$row['NOME']."</td>
                 <td>".$row['INDIRIZZO']."</td>
                 <td>".$row['PROVINCIA']."</td>
                 <td>".$row['COMUNE']."</td>
                 <td>".$row['CAP']."</td>
                 <td>".$row['SESSO']."</td>
                 <td>".$row['DATA_NASCITA']."</td>
                 <td>".$row['CF_PIVA']."</td>
              </tr>";
            }
            ?>
            </body>
        </thead>
    </table>
    <script>
      $(document).ready(function(){
        $('AZIENDE_TEST').DataTable();
      });
    </script>
    </div>
    </html>

ITC

@allan
Sorry the error "Undefined index: dt in Test_collegamento_db/ssp.class.php" and "Undefined index: db in Test_collegamento_db/ssp.class.php" is on the line 259

allan

Thank you - this is the response from the server:

Query ok.{"error":"An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'field list'"}

So it is correct - that is not valid JSON. Remove whatever is printing out the Query ok. and it will be valid JSON.

Beyond that, I would suggest logging the SQL query that the script is generating so you can inspect that as see where it is going wrong.

Allan

ITC

Thank you so much @allan, i removed the printed sentences,so the JSON code is working now.
Anyway, i still have the same problem concerning my file error_log: "Undefined index: dt in /home/cropperi/cropping.it/Test_collegamento_db/ssp.class.php on line 258 " and "Undefined index: db in /home/cropperi/cropping.it/Test_collegamento_db/ssp.class.php on line 258".

After inernal check, i found out that the script gives this error: "Cannot read property 'length' of undefined"

Now i'll try to solve this problem , althought i don't know how i can solve the first one.

Do you have a solution?

allan

That error indicates this line:

$out[] = $a[$i][$prop];

So perhaps you need:

if (isset($a) && isset($a[$i]) && $a[$i][$prop]) {
  $out[] = $a[$i][$prop];
}

it's not clear to me why you are getting those errors and I'm not with my local version.

Regards,
Allan

ITC

Thank you for the support @allan.

I tryed the last code you wrote but it still doesn't run, so i think i change all and use the bootstrap method