<?php /* * Helper functions for building a DataTables server-side processing SQL query * * The static functions in this class are just helper functions to help build * the SQL used in the DataTables demo server-side processing scripts. These * functions obviously do not represent all that can be done with server-side * processing, they are intentionally simple to show how it works. More complex * server-side processing operations will likely require a custom script. * * See http://datatables.net/usage/server-side for full details on the server- * side processing requirements of DataTables. * * @license MIT - http://datatables.net/license_mit * Modified by JAKWEB.ch to fit the medoo db class. * Modified for Version 2.0.4 and PHP 8.1 compatibility */ class SSP { /** * Create the data output array for the DataTables rows * * @param array $columns Column information array * @param array $data Data from the SQL get * @return array Formatted data in a row based format */ 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]; // Is there a formatter? 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 data_output_join ( $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]; // Is there a formatter? if ( isset( $column['formatter'] ) ) { $row[ $column['dt'] ] = $column['formatter']( $data[$i][ $column['dbjoin'] ], $data[$i] ); } else { $row[ $column['dt'] ] = $data[$i][ $columns[$j]['dbjoin'] ]; } } $out[] = $row; } return $out; } /** * Paging * * Construct the LIMIT clause for server-side processing SQL query * * @param array $request Data sent to server by DataTables * @param array $columns Column information array * @return string SQL limit clause */ static function limit ( $request, $columns ) { $limit = ''; if ( isset($request['start']) && $request['length'] != -1 ) { $limit = "LIMIT ".intval($request['start']).", ".intval($request['length']); } return $limit; } /** * Ordering * * Construct the ORDER BY clause for server-side processing SQL query * * @param array $request Data sent to server by DataTables * @param array $columns Column information array * @return string SQL order by clause */ 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++ ) { // Convert the column index into the column data property $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; } } $order = 'ORDER BY '.implode(', ', $orderBy); } return $order; } /** * Searching / Filtering * * Construct the WHERE clause for server-side processing SQL query. * * 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 performance on large * databases would be very poor * * @param array $request Data sent to server by DataTables * @param array $columns Column information array * @param array $bindings Array of values for PDO bindings, used in the * sql_exec() function * @return string SQL where clause */ 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; } } } // Individual column filtering 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; } } } // Combine the filters into a single string $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; } /* Run the simple query */ static function simple ( $request, $table, $primaryKey, $columns, $whereResult ) { $bindings = array(); global $jakdb; // Build the SQL query string from the request $limit = self::limit( $request, $columns ); $order = self::order( $request, $columns ); $where = self::filter( $request, $columns, $bindings ); if ( $whereResult ) { $where = $where ? $where .' AND '.$whereResult : 'WHERE '.$whereResult; } // Main query to actually get the data $data = $jakdb->sql_exec( $bindings, "SELECT ".implode(", ", self::pluck($columns, 'db'))." FROM $table $where $order $limit" ); // Data set length after filtering $resFilterLength = $jakdb->sql_exec( $bindings, "SELECT COUNT({$primaryKey}) FROM $table $where" ); $recordsFiltered = $resFilterLength[0][0]; // Total data set length $resTotalLength = $jakdb->sql_exec( "SELECT COUNT({$primaryKey}) FROM $table" ); $recordsTotal = $resTotalLength[0][0]; /* * Output */ return array( "draw" => isset ( $request['draw'] ) ? intval( $request['draw'] ) : 0, "recordsTotal" => intval( $recordsTotal ), "recordsFiltered" => intval( $recordsFiltered ), "data" => self::data_output( $columns, $data ) ); } /* Run the query, quite heavy with joins */ static function join ( $request, $table, $table2, $table3, $primaryKey, $columns, $whereResult=null, $whereAll=null ) { $bindings = array(); $whereAllSql = ''; global $jakdb; // Build the SQL query string from the request $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; } // Main query to actually get the data $data = $jakdb->sql_exec( $bindings, "SELECT ".implode(", ", self::pluck($columns, 'db'))." FROM $table$table2$table3 $where $order $limit" ); // Data set length after filtering $resFilterLength = $jakdb->sql_exec( $bindings, "SELECT COUNT({$primaryKey}) FROM $table$table2$table3 $where" ); $recordsFiltered = $resFilterLength[0][0]; // Total data set length $resTotalLength = $jakdb->sql_exec( "SELECT COUNT({$primaryKey}) FROM $table$table2$table3". $whereAllSql ); $recordsTotal = $resTotalLength[0][0]; /* * Output */ return array( "draw" => isset ( $request['draw'] ) ? intval( $request['draw'] ) : 0, "recordsTotal" => intval( $recordsTotal ), "recordsFiltered" => intval( $recordsFiltered ), "data" => self::data_output_join( $columns, $data ) ); } /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Internal methods */ /** * Create a PDO binding key which can be used for escaping variables safely * when executing a query with sql_exec() * * @param array &$a Array of bindings * @param * $val Value to bind * @param int $type PDO field type * @return string Bound key to be used in the SQL where this parameter * would be used. */ static function bind ( &$a, $val, $type ) { $key = ':binding_'.count( $a ); $a[] = array( 'key' => $key, 'val' => $val, 'type' => $type ); return $key; } /** * Pull a particular property from each assoc. array in a numeric array, * returning and array of the property values from each item. * * @param array $a Array to get data from * @param string $prop Property to read * @return array Array of property values */ static function pluck ( $a, $prop ) { $out = array(); for ( $i=0, $len=count($a) ; $i<$len ; $i++ ) { $out[] = $a[$i][$prop]; } return $out; } /** * Return a string from an array or a string * * @param array|string $a Array to join * @param string $join Glue for the concatenation * @return string Joined string */ static function _flatten ( $a, $join = ' AND ' ) { if ( ! $a ) { return ''; } else if ( $a && is_array($a) ) { return implode( $join, $a ); } return $a; } } ?>