/** * Perform the SQL queries needed for an server-side processing requested, * utilising the helper functions of this class, limit(), order() and * filter() among others. The returned array is ready to be encoded as JSON * in response to an SSP request, or can be modified if needed before * sending back to the client. * * @param array $request Data sent to server by DataTables * @param array $sql_details SQL connection details - see sql_connect() * @param string $table SQL table to query * @param string $primaryKey Primary key of the table * @param array $columns Column information array * @param array $joinQuery Join query String * @param string $extraWhere Where query String * * @return array Server-side processing response array * */ static function simple($conn, $request, $table, $primaryKey, $columns, $joinQuery = NULL, $extraWhere = '', $groupBy = '') { // static function simple ( $request, $sql_details, $table, $primaryKey, $columns, // $joinQuery = NULL, $extraWhere = '', $groupBy = '') { $bindings = array(); //$db = SSP::sql_connect( $sql_details ); // Build the SQL query string from the request $limit = SSP::limit($request, $columns); $order = SSP::order($request, $columns, $joinQuery); $where = SSP::filter($request, $columns, $bindings, $joinQuery); // IF Extra where set then set and prepare query if ($extraWhere) { $extraWhere = $where ? ' AND ' . $extraWhere : ' WHERE ' . $extraWhere; } $groupBy = $groupBy ? ' GROUP BY ' . $groupBy . ' ' : ''; // Main query to actually get the data if ($joinQuery) { $col = SSP::pluck($columns, 'db', $joinQuery); $query = "SELECT SQL_CALC_FOUND_ROWS " . implode(", ", $col) . "\n {$joinQuery}\n {$where}\n {$extraWhere}\n {$groupBy}\n {$order}\n {$limit}"; } else { $query = "SELECT SQL_CALC_FOUND_ROWS " . implode(", ", SSP::pluck($columns, 'db')) . "\n FROM {$table}\n {$where}\n {$extraWhere}\n {$groupBy}\n {$order}\n {$limit}"; } $conn->Execute("SET NAMES 'utf8'"); $data = SSP::sql_exec($conn, $bindings, $query); // Data set length after filtering $resFilterLength = SSP::sql_exec($conn, "SELECT FOUND_ROWS() as cnt"); $recordsFiltered = $resFilterLength[0][cnt]; // Total data set length $resTotalLength = SSP::sql_exec($conn, "SELECT COUNT({$primaryKey}) as cnt FROM {$table}"); $recordsTotal = $resTotalLength[0][cnt]; //file_put_contents('d:\query', $query); //file_put_contents('d:\bindings', $bindings); //file_put_contents('d:\11', varDump($resFilterLength)); //file_put_contents('d:\12', $recordsFiltered); //file_put_contents('d:\data', varDump($data)); /* * Output */ return array("draw" => intval($request['draw']), "recordsTotal" => intval($recordsTotal), "recordsFiltered" => intval($recordsFiltered), "data" => SSP::data_output($columns, $data, $joinQuery)); }
/** * 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 * @return string SQL where clause */ static function filter($request, $columns) { $globalSearch = array(); $columnSearch = array(); $dtColumns = SSP::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') { if (isset($column['coloumn_name'])) { $globalSearch[] = $column['coloumn_name'] . " LIKE " . '"%' . $str . '%"'; } else { $globalSearch[] = $column['db'] . " LIKE " . '"%' . $str . '%"'; } } } } // Individual column filtering 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 != '') { if (isset($column['coloumn_name'])) { $columnSearch[] = $column['coloumn_name'] . " LIKE " . '"%' . $str . '%"'; } else { if (in_array($column['db'], array("ad.clad_category", "ad.clad_id", "u_id"))) { $columnSearch[] = $column['db'] . " LIKE " . '"' . $str . '%"'; } else { $columnSearch[] = $column['db'] . " LIKE " . '"%' . $str . '%"'; } } } } // 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); } return $where; }
/** * 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 EDIT : added $mywhere functionality for passing initial filtering conditions */ static function filter($request, $columns, &$bindings, $myWhere) { $globalSearch = array(); $columnSearch = array(); $dtColumns = SSP::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 = SSP::bind($bindings, '%' . $str . '%', PDO::PARAM_STR); $globalSearch[] = "" . $column['db'] . " LIKE " . $binding; } } } // Individual column filtering 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 = SSP::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 ', $globalSearch) : $where . ' AND ' . implode(' AND ', $globalSearch); } if ($where !== '') { $where = 'WHERE ' . $where; // add my clause if ($myWhere !== '') { $where .= ' AND ' . $myWhere; } } if ($where == '' && $myWhere !== '') { // add my clause $where = 'WHERE ' . $myWhere; } return $where; }
$whereAll .= $filter_sql; function jp_bind($bindings) { $return_array = array(); if (is_array($bindings)) { for ($i = 0, $ien = count($bindings); $i < $ien; $i++) { //$binding = $bindings[$i]; // $stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] ); $return_array[$bindings[$i]['key']] = $bindings[$i]['val']; } } return $return_array; } $where .= !empty($where) ? " AND " . $whereAll : "WHERE " . $whereAll; $bindings = jp_bind($bindings); $complete_query = "SELECT SQL_CALC_FOUND_ROWS `" . implode("`, `", SSP::pluck($columns, 'db')) . "`\n FROM `qry_assets` {$where} {$order} {$limit}"; // echo $complete_query; //var_dump($bindings); // echo $complete_query; // var_dump($bindings); // die; $data = $con->myQuery($complete_query, $bindings)->fetchAll(); $recordsFiltered = $con->myQuery("SELECT FOUND_ROWS();")->fetchColumn(); $recordsTotal = $con->myQuery("SELECT COUNT(id) FROM `qry_assets` {$where};", $bindings)->fetchColumn(); $json['draw'] = isset($request['draw']) ? intval($request['draw']) : 0; $json['recordsTotal'] = $recordsFiltered; $json['recordsFiltered'] = $recordsFiltered; $json['data'] = SSP::data_output($columns, $data); echo json_encode($json); // $resTotalLength = SSP::sql_exec( $db, $bindings, // "SELECT COUNT(`{$primaryKey}`)
/** * Perform the SQL queries needed for an server-side processing requested, * utilising the helper functions of this class, limit(), order() and * filter() among others. The returned array is ready to be encoded as JSON * in response to an SSP request, or can be modified if needed before * sending back to the client. * * @param array $request Data sent to server by DataTables * @param array $sql_details SQL connection details - see sql_connect() * @param string $table SQL table to query * @param string $primaryKey Primary key of the table * @param array $columns Column information array * @return array Server-side processing response array */ static function simple($request, $sql_details, $table, $primaryKey, $columns) { $bindings = array(); $db = SSP::sql_connect($sql_details); // Build the SQL query string from the request $limit = SSP::limit($request, $columns); $order = SSP::order($request, $columns); $where = SSP::filter($request, $columns, $bindings); // Main query to actually get the data $data = SSP::sql_exec($db, $bindings, "SELECT SQL_CALC_FOUND_ROWS `" . implode("`, `", SSP::pluck($columns, 'db')) . "` FROM `$table` $where $order $limit" ); // Data set length after filtering $resFilterLength = SSP::sql_exec($db, "SELECT FOUND_ROWS()" ); $recordsFiltered = $resFilterLength[0][0]; // Total data set length $resTotalLength = SSP::sql_exec($db, "SELECT COUNT(`{$primaryKey}`) FROM `$table`" ); $recordsTotal = $resTotalLength[0][0]; /* * Output */ return array( "draw" => intval($request['draw']), "recordsTotal" => intval($recordsTotal), "recordsFiltered" => intval($recordsFiltered), "data" => SSP::data_output($columns, $data) ); }
/** * Perform the SQL queries needed for an server-side processing requested, * utilising the helper functions of this class, limit(), order() and * filter() among others. The returned array is ready to be encoded as JSON * in response to an SSP request, or can be modified if needed before * sending back to the client. * * @param array $request Data sent to server by DataTables * @param array $sql_details SQL connection details - see sql_connect() * @param string $table SQL table to query * @param string $primaryKey Primary key of the table * @param array $columns Column information array * @param array $joinQuery Join query String * @param string $extraWhere Where query String * * @return array Server-side processing response array * */ static function simple($request, $sql_details, $table, $primaryKey, $columns, $joinQuery = NULL, $extraWhere = '', $groupBy = '') { $bindings = array(); $db = SSP::sql_connect($sql_details); // Build the SQL query string from the request $limit = SSP::limit($request, $columns); $order = SSP::order($request, $columns, $joinQuery); $where = SSP::filter($request, $columns, $bindings, $joinQuery); // IF Extra where set then set and prepare query if ($extraWhere) { $extraWhere = $where ? ' AND ' . $extraWhere : ' WHERE ' . $extraWhere; } $groupBy = $groupBy ? ' GROUP BY ' . $groupBy . ' ' : ''; // Main query to actually get the data if ($joinQuery) { $col = SSP::pluck($columns, 'db', $joinQuery); $query = "SELECT SQL_CALC_FOUND_ROWS " . implode(", ", $col) . "\n\t\t\t {$joinQuery}\n\t\t\t {$where}\n\t\t\t {$extraWhere}\n {$groupBy}\n\t\t\t {$order}\n\t\t\t {$limit}"; } else { $query = "SELECT SQL_CALC_FOUND_ROWS `" . implode("`, `", SSP::pluck($columns, 'db')) . "`\n\t\t\t FROM `{$table}`\n\t\t\t {$where}\n\t\t\t {$extraWhere}\n\t\t\t {$groupBy}\n {$order}\n\t\t\t {$limit}"; } $data = SSP::sql_exec($db, $bindings, $query); // Data set length after filtering $resFilterLength = SSP::sql_exec($db, "SELECT FOUND_ROWS()"); $recordsFiltered = $resFilterLength[0][0]; // Total data set length $count_request = "SELECT COUNT(`{$primaryKey}`)"; if ($joinQuery) { $count_request .= $joinQuery; } else { $count_request .= "FROM `{$table}`"; } $resTotalLength = SSP::sql_exec($db, $count_request); $recordsTotal = $resTotalLength[0][0]; /* * Output */ return array("draw" => intval($request['draw']), "recordsTotal" => intval($recordsTotal), "recordsFiltered" => intval($recordsFiltered), "data" => SSP::data_output($columns, $data, $joinQuery)); }