PHP SSP::sql_exec方法代码示例

 /**
  * 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, $db, $table, $primaryKey, $columns, $myJoin, $myWhere)
 {
     $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, $myWhere);
     // Main query to actually get the data
     $data = SSP::sql_exec($db, $bindings, "SELECT SQL_CALC_FOUND_ROWS " . implode(", ", SSP::pluckAs($columns)) . "\n             FROM {$table}\n             {$myJoin}\n             {$where}\n             {$order}\n             {$limit}");
     // Data set length after filtering
     $resFilterLength = SSP::sql_exec($db, "SELECT FOUND_ROWS()");
     $recordsFiltered = $resFilterLength[0][0];
     //add my initial where clause for correct results
     $dataWhere = $myWhere !== '' ? 'WHERE ' . $myWhere : '';
     // Total data set length
     $resTotalLength = SSP::sql_exec($db, "SELECT COUNT({$primaryKey})\n             FROM {$table}\n             {$myJoin}\n             {$dataWhere}");
     $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($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));
 }

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

            case 'date':
                switch ($compare) {
                    case 'eq':
                        $qs .= " AND `" . $field . "` = '" . date('Y-m-d', strtotime($value)) . "'";
                        break;
                    case 'lt':
                        $qs .= " AND `" . $field . "` < '" . date('Y-m-d', strtotime($value)) . "'";
                        break;
                    case 'gt':
                        $qs .= " AND `" . $field . "` > '" . date('Y-m-d', strtotime($value)) . "'";
                        break;
                }
                break;
        }
    }
    $where .= $qs;
}
// DB table to use
$table = 'sp500';
// SQL server connection information
$sql_details = array('user' => 'sortabletable', 'pass' => '', 'db' => 'sortabletable', 'host' => 'localhost');
$query = "SELECT * FROM `" . $table . "` WHERE " . $where;
$query .= " ORDER BY `" . $sortProperty . "` " . $sortDirection;
$query .= " LIMIT " . $start . "," . $count . ";";
$db = SSP::sql_connect($sql_details);
$bindings = array();
$rows = SSP::sql_exec($db, $bindings, $query);
$resTotalLength = SSP::sql_exec($db, $bindings, "SELECT COUNT(`id`) FROM `" . $table . "` WHERE " . $where . ";");
$count = $resTotalLength[0][0];
$result = SSP::sql_exec($db, $bindings, $query);
echo json_encode(array("total" => $count, "data" => $rows));

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