Last active
July 1, 2019 17:36
-
-
Save mariapaulinar/3b9c220e22bedee626ba5dc8c4c4f9ed to your computer and use it in GitHub Desktop.
DataTables con SQL Server 2008 R2
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| class DataTable { | |
| var $database = "test"; | |
| var $host = "127.0.0.1"; | |
| var $user = "usertest"; | |
| var $pass = "secret"; | |
| var $conn; | |
| var $numRows; | |
| var $baseTest = "test.dbo."; | |
| public function __construct() { | |
| $this->connect($this->host, $this->user, $this->pass, $this->database); | |
| } | |
| public function connect($_host, $_user, $_password, $_database) { | |
| $this->conn = mssql_connect($_host, $_user, $_password) or die(mssql_get_last_message()); | |
| mssql_select_db($this->database, $this->conn) or die(mssql_get_last_message()); | |
| } | |
| function query($sql) { | |
| mssql_query('SET ANSI_WARNINGS ON', $this->conn) or die(mssql_get_last_message()); | |
| mssql_query('SET ANSI_NULLS ON', $this->conn) or die(mssql_get_last_message()); | |
| $resultQuery = mssql_query($sql, $this->conn) or die(mssql_get_last_message()); | |
| $results = array(); | |
| while ($row = mssql_fetch_array($resultQuery)) | |
| $results[] = $row; | |
| return $results; | |
| } | |
| function numRows($sql) { | |
| $resultQuery = mssql_query($sql, $this->conn) or die(mssql_get_last_message()); | |
| $result = mssql_num_rows($resultQuery); | |
| return $result; | |
| } | |
| public function getData($params, $table, $columns) { | |
| $where = $sqlTot = $sqlRec = ''; | |
| if( !empty($params['search']['value']) ) { | |
| $where = " WHERE"; | |
| $i = 0; | |
| foreach($columns as $c) { | |
| if($i === 0) { | |
| $where .= (count($columns) === 1) ? " (" . $c . " LIKE '%" . $params['search']['value'] . "%' " | |
| : " ( " . $c . " LIKE '%" . $params['search']['value'] . "%' "; | |
| } else { | |
| $where .=" OR " . $c . " LIKE '%" . $params['search']['value'] . "%' "; | |
| } | |
| $i++; | |
| } | |
| $where .= " )"; | |
| } | |
| $sql = "SELECT * FROM " . $this->baseTest . "[" . $tabla . "]"; | |
| //Total de registros en la tabla | |
| $totalRecords = $this->numRows(utf8_decode($sql)); | |
| $sqlTot .= $sql; | |
| $sqlRec = "SELECT TOP " . $params['length'] . " * FROM( SELECT *, ROW_NUMBER() over (ORDER BY " | |
| . $columns[$params['order'][0]['column']] . " " | |
| . $params['order'][0]['dir'] . " ) as ct FROM " | |
| . $this->baseTest . "[" . $table . "]" | |
| . " " . $where . " ) sub WHERE ct > " . $params['start']; | |
| if(isset($where) && $where != '') { | |
| $sqlTot .= $where; | |
| } | |
| //Registros a mostrar en la tabla | |
| $dataRecords = $this->query(utf8_decode($sqlRec)); | |
| //Total registros después del filtro | |
| $totalRecordsFiltered = $this->numRows(utf8_decode($sqlTot)); | |
| $data = array( | |
| "draw" => intval($params['draw']), | |
| "recordsTotal" => intval($totalRecords), | |
| "recordsFiltered" => intval($totalRecordsFiltered), | |
| "data" => $dataRecords | |
| ); | |
| return $data; | |
| } | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <html> | |
| <head> | |
| <title>DataTables con SQL Server 2008 R2</title> | |
| <link href="https://cdn.datatables.net/1.10.13/css/jquery.dataTables.min.css" rel="stylesheet"> | |
| </head> | |
| <body> | |
| <table class="datatable"> | |
| <thead> | |
| <tr> | |
| <th>ID</th> | |
| <th>NOMBRE</th> | |
| </tr> | |
| </thead> | |
| <tbody> | |
| <tr> | |
| <td></td> | |
| <td></td> | |
| </tr> | |
| </tbody> | |
| </table> | |
| <script src="https://code.jquery.com/jquery-2.2.4.min.js" integrity="sha256-BbhdlvQf/xTY9gja0Dq3HiwQF8LaCRTXxZKRutelT44=" crossorigin="anonymous"></script> | |
| <script type="text/javascript" src="https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script> | |
| <script type="text/javascript" src="table.js"></script> | |
| </body> | |
| </html> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| (function($) { | |
| $(".datatable").DataTable({ | |
| processing: true, | |
| serverSide: true, | |
| ajax: { | |
| url: "table.php" | |
| } | |
| }); | |
| }(jQuery)); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| require_once('DataTable.php'); | |
| $dataTable = new DataTable(); | |
| $table = 'test'; | |
| $columns = array( | |
| 0 => 'id', | |
| 1 => 'nombre' | |
| ); | |
| $data = $dataTable->getData($_POST, $table, $columns); | |
| echo json_encode($data); |
Thanks - I got the same error:
var $baseTest = "contact.dbo."; //My DB
DataTables warning: table id=DataTables_Table_0 - Ajax error. For more information about this error, please see http://datatables.net/tn/7
Please read: https://datatables.net/manual/tech-notes/7
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi thank you for share code , i want used your code i try to my ms sql server
i get to error you
DataTables warning: table id=DataTables_Table_0 - Ajax error. For more information about this error, please see http://datatables.net/tn/7