Last active
November 1, 2024 17:04
-
-
Save ChiranjeeviAdi/b4e5f4acada28a4f680574d38404d868 to your computer and use it in GitHub Desktop.
(Datatables) Server Side Pagination using PHP,MYSQL,Jquery
This file contains hidden or 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
| $servername = "localhost"; | |
| $username = "root"; | |
| $password = ""; | |
| $dbname = "testusersdb"; | |
| $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")); | |
| // set the PDO error mode to exception | |
| $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); | |
This file contains hidden or 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 'dbconnect.php'; | |
| $where=""; | |
| if( !empty($_REQUEST['search']['value']) ) { | |
| $where.=" WHERE ( email LIKE '".$_REQUEST['search']['value']."%' "; | |
| $where.=" OR mobile_number LIKE '".$_REQUEST['search']['value']."%' )"; | |
| } | |
| $totalRecordsSql = "SELECT count(*) as total FROM users $where;"; | |
| $stmt = $conn->prepare($totalRecordsSql); | |
| $stmt->execute(); | |
| $res = $stmt->fetchAll(); | |
| $totalRecords=0; | |
| foreach ($res as $key => $value) { | |
| $totalRecords = $value['total']; | |
| } | |
| $columns = array( | |
| 0 =>'user_id', | |
| 1 => 'name', | |
| 2=> 'email', | |
| 3=>'mobile_number' | |
| ); | |
| $sql = "SELECT user_id,name,email,mobile_number"; | |
| $sql.=" FROM users $where"; | |
| $sql.=" ORDER BY ". $columns[$_REQUEST['order'][0]['column']]." ".$_REQUEST['order'][0]['dir']." LIMIT ".$_REQUEST['start']." ,".$_REQUEST['length']." "; | |
| $stmt = $conn->prepare($sql); | |
| $stmt->execute(); | |
| $result = $stmt->fetchAll(); | |
| $json_data = array( | |
| "draw" => intval( $_REQUEST['draw'] ), | |
| "recordsTotal" => intval($totalRecords ), | |
| "recordsFiltered" => intval($totalRecords), | |
| "data" => $result // total data array | |
| ); | |
| echo json_encode($json_data); | |
| ?> |
This file contains hidden or 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
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <script src="plugins/jquery/jquery-1.10.2.js" type="text/javascript"></script> | |
| <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css"> | |
| <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script> | |
| <div class="container"> | |
| <div class=""> | |
| <h1>Table</h1> | |
| <div class=""> | |
| <table id="table" > | |
| <thead> | |
| <tr> | |
| <th>Id</th> | |
| <th>Name</th> | |
| <th>Email</th> | |
| <th>Mobile Number</th> | |
| </tr> | |
| </thead> | |
| </table> | |
| </div> | |
| </div> | |
| </div> | |
| <script type="text/javascript"> | |
| $(document).ready(function () { | |
| var mainurl = "getData.php"; | |
| $('#table').DataTable({ | |
| "bProcessing": true, | |
| "serverSide": true, | |
| "ajax":{ | |
| url :mainurl, // json | |
| type: "post", // type of method | |
| error: function(){ | |
| } | |
| } | |
| }); | |
| }); | |
| </script> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment