PHP SSP::pluck方法代码示例

 /**
  * 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));
 }