When you use a huge data set, or you are loading things like images in your rows, the way to go is the DataTables server-side processing.
About the ordering issue, you should use the order parameter to build your server-side query (you can look at this and this tutorial)
I show you an example (based on the first tutorial link) with enabled ordering and filtering:
MySQL data:
CREATE TABLE `users` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`id` mediumint,
`name` varchar(255) default NULL,
`phone` varchar(100) default NULL,
`email` varchar(255) default NULL,
`country` varchar(100) default NULL,
`zip` varchar(10) default NULL,
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;
INSERT INTO `users` (`id`,`name`,`phone`,`email`,`country`,`zip`) VALUES (1,"Cameron Pugh","1-287-272-8335","tempus.eu@nunc.co.uk","Germany","399235"),(2,"Giacomo Cross","1-748-121-3694","id@scelerisque.com","Brazil","3734"),(3,"Chadwick Pennington","1-622-974-0042","et@neque.org","Seychelles","48538"),(4,"Isaiah Dawson","1-782-569-0750","eu.eros.Nam@faucibus.com","Zimbabwe","10902"),(5,"Justin Collier","1-990-947-4684","Nunc.ut.erat@Vestibulum.co.uk","New Zealand","5483"),(6,"Clayton Hines","1-100-279-3148","Cras@vitae.com","Hungary","NR33 4KU"),(7,"Kane Carey","1-254-416-8972","Fusce@vulputate.org","Gibraltar","9587NU"),(8,"Vincent Gould","1-628-187-2689","aptent.taciti.sociosqu@metusIn.org","British Indian Ocean Territory","548135"),(9,"Hayes Page","1-383-950-0401","Donec@feliseget.com","Somalia","68-941"),(10,"Charles Irwin","1-402-205-9063","urna.Nullam@Pellentesqueultricies.com","Burundi","63-166");
INSERT INTO `users` (`id`,`name`,`phone`,`email`,`country`,`zip`) VALUES (11,"Brady Forbes","1-234-148-9372","In.faucibus.Morbi@porttitor.ca","Cuba","X8L 3P8"),(12,"Lucius Keller","1-754-272-5845","mauris@consectetueradipiscingelit.co.uk","Colombia","66-646"),(13,"Beck Robbins","1-225-415-3242","ipsum.dolor@nec.net","Cyprus","24633-242"),(14,"Stuart Rivers","1-576-527-1727","ridiculus.mus.Aenean@egestas.net","Armenia","809758"),(15,"Slade Zamora","1-421-103-8055","montes@Duisac.ca","Macao","67640"),(16,"Jameson Holder","1-677-759-2237","massa@lacusUt.ca","Spain","55890-580"),(17,"Howard Buck","1-199-819-6096","euismod@pellentesquemassa.com","South Sudan","17819"),(18,"Nero Coffey","1-198-687-1573","Vestibulum@blanditatnisi.com","Serbia","22297"),(19,"Forrest Williamson","1-590-612-9090","rhoncus.Nullam.velit@Donec.co.uk","Costa Rica","5033JC"),(20,"Gavin Holland","1-707-598-2034","volutpat.ornare@Integereu.co.uk","Denmark","4096");
INSERT INTO `users` (`id`,`name`,`phone`,`email`,`country`,`zip`) VALUES (21,"Zachery Finch","1-521-861-7870","mollis@Nullam.com","Slovenia","177715"),(22,"Kyle Pollard","1-876-829-9807","at@vel.ca","Botswana","P4E 7V4"),(23,"Lance House","1-995-534-0452","Curabitur.sed@nuncsedlibero.org","Cuba","G9L 8N0"),(24,"Neville Robbins","1-698-395-8194","pede@tempuslorem.net","Rwanda","5447"),(25,"Lance Winters","1-454-856-3279","consequat.dolor@tristiquesenectuset.com","Israel","6113"),(26,"Craig Willis","1-562-870-9056","In@Fusce.com","Guadeloupe","780057"),(27,"Jermaine Crosby","1-413-697-9562","magna@facilisi.com","Maldives","39378"),(28,"Ivor Donaldson","1-692-141-6383","mauris.sapien.cursus@habitant.org","Namibia","9026"),(29,"Jeremy Nunez","1-762-801-8582","mi.ac@eleifendvitaeerat.com","Nepal","24414"),(30,"Jasper Wong","1-423-632-0253","eget@ultrices.org","Barbados","P2Z 8K7");
INSERT INTO `users` (`id`,`name`,`phone`,`email`,`country`,`zip`) VALUES (31,"Jeremy Morgan","1-215-508-6736","mi.pede@ultricesiaculisodio.org","Qatar","89828"),(32,"Caldwell Crane","1-538-817-8691","amet@enimCurabiturmassa.net","Norfolk Island","64429-524"),(33,"Lyle Benton","1-821-536-5939","Morbi@porttitortellusnon.net","Malawi","29345"),(34,"Peter Foster","1-676-358-9027","et.tristique.pellentesque@eudolor.com","Uzbekistan","4209"),(35,"Malcolm Owen","1-730-651-5418","at.risus.Nunc@Curabitur.ca","Kyrgyzstan","329947"),(36,"Alden Berg","1-476-759-9158","massa@Phasellusdapibus.net","Mauritius","63981"),(37,"Arthur Reed","1-222-924-9018","sed.facilisis@odiovelest.net","Turkey","B3B 3W8"),(38,"Elvis Gregory","1-644-857-8387","eleifend.egestas@blandit.net","Armenia","1850"),(39,"Robert Edwards","1-379-321-4803","Praesent.eu@est.edu","South Africa","G9G 0K7"),(40,"Jerome Thompson","1-957-166-1255","montes@odiosemper.net","Guam","00298");
INSERT INTO `users` (`id`,`name`,`phone`,`email`,`country`,`zip`) VALUES (41,"Barclay Dominguez","1-151-678-7008","Nunc.mauris@facilisisvitae.com","Libya","226787"),(42,"Mason Maxwell","1-833-633-1289","Vivamus.sit.amet@dui.com","Sierra Leone","25196"),(43,"Forrest Fields","1-604-741-2880","Donec@nostraper.ca","Kenya","4599"),(44,"Dante Ryan","1-334-938-5185","Lorem.ipsum.dolor@nullaante.net","Belize","1645"),(45,"Clark Webb","1-789-916-3405","Maecenas.ornare.egestas@Namtempordiam.org","Belarus","01-636"),(46,"Chase Hood","1-217-256-0245","accumsan.neque.et@nullamagna.net","Suriname","75564"),(47,"Nasim Sargent","1-179-443-9947","Phasellus@mauriseu.com","Tonga","6208"),(48,"Ross Sanford","1-180-648-0200","vel.pede.blandit@vel.ca","Syria","17-417"),(49,"Denton Rowland","1-110-687-0329","enim.condimentum.eget@accumsaninterdumlibero.co.uk","Norfolk Island","Z5 9VX"),(50,"Branden Chang","1-154-713-7375","Aliquam@Ut.ca","Equatorial Guinea","5815");
INSERT INTO `users` (`id`,`name`,`phone`,`email`,`country`,`zip`) VALUES (51,"George Gill","1-325-866-5545","non.magna@dictumProin.ca","Viet Nam","1475"),(52,"Hedley Cleveland","1-418-133-0843","ut.sem@ipsumnunc.co.uk","Monaco","77606"),(53,"Clayton Ellis","1-796-996-1413","a.feugiat.tellus@mauris.org","Papua New Guinea","403788"),(54,"Ivor Ramos","1-261-874-3568","elit@hendrerit.com","Australia","260263"),(55,"Malik Holt","1-837-382-3716","justo.eu@fermentum.net","Western Sahara","14540"),(56,"Mohammad Reese","1-321-170-6284","Aenean@ametfaucibusut.net","Denmark","5661"),(57,"Cameron Bryan","1-696-985-4867","scelerisque.lorem@arcuVestibulum.com","Haiti","99011"),(58,"Raphael Rodriquez","1-728-378-9772","est.Mauris@magna.ca","Benin","6061"),(59,"Simon Knox","1-725-880-2285","nunc.id@Nuncsollicitudin.co.uk","Belize","07313"),(60,"Damon Blevins","1-862-147-3498","luctus.lobortis@diamDuismi.net","Svalbard and Jan Mayen Islands","442151");
index.html:
<!DOCTYPE html>
<html>
<head>
<title></title>
<link rel="stylesheet" type="text/css" href="DataTables/datatables.min.css"/>
<script type="text/javascript" src="jquery.min.js"></script>
<script type="text/javascript" src="DataTables/datatables.min.js"></script>
</head>
<body>
<table cellpadding="1" cellspacing="1" id="users" class="display" width="100%">
<thead>
<tr>
<th>ID</th>
<th>NAME</th>
<th>PHONE</th>
<th>Email</th>
<th>Country</th>
<th>ZIP Code</th>
</tr>
</thead>
<tfoot>
<tr>
<th>ID</th>
<th>NAME</th>
<th>PHONE</th>
<th>Email</th>
<th>Country</th>
<th>ZIP Code</th>
</tr>
</tfoot>
</table>
<script type="text/javascript">
$(document).ready(function () {
$('#users').DataTable({
"processing": true,
"serverSide": true,
"ajax": {
url: 'data.php',
type: 'POST'
},
"columns": [
{"data": "id"},
{"data": "name"},
{"data": "phone"},
{"data": "email"},
{"data": "country"},
{"data": "zip"}
],
});
});
</script>
</body>
</html>
data.php:
<?php
// we need to check if there is a datatables post request
if (!empty($_POST) ) {
// declaring database connection parameters
$servername = "localhost";
$dbName = "datatablestest";
$username = "myusername";
$password = "********";
$tableName = "tablename";
// Create connection
global $connection;
$connection = $mysqli = new mysqli($servername, $username, $password, $dbName);
function getData($sql){
global $connection ; // we use connection already opened
$query = mysqli_query($connection, $sql);
$data = array();
foreach ($query as $row ) {
$data[] = $row ;
}
return $data;
}
$draw = $_POST["draw"]; // counter used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables
$orderByColumnIndex = $_POST['order'][0]['column']; // index of the sorting column (0 index based - i.e. 0 is the first record)
$orderBy = $_POST['columns'][$orderByColumnIndex]['data']; // Get name of the sorting column from its index
$orderType = $_POST['order'][0]['dir']; // ASC or DESC
$start = $_POST["start"]; // Paging first record indicator.
$length = $_POST['length']; // Number of records that the table can display in the current draw
// count total records
$recordsTotal = count(getData("SELECT * FROM $tableName "));
/* SEARCH CASE : Filtered data
if not empty, the user has input some data into the search field, so, we need to build a query to filter our data
*/
if(!empty($_POST['search']['value'])){
/* WHERE Clause for searching */
for($i=0 ; $i<count($_POST['columns']);$i++){
$column = $_POST['columns'][$i]['data'];// we get the name of each column using its index from POST request
$where[]="$column like '%".$_POST['search']['value']."%'";
}
$where = "WHERE ".implode(" OR " , $where);// id like '%searchValue%' or name like '%searchValue%' ....
/* End WHERE */
$sql = sprintf("SELECT * FROM %s %s", $tableName , $where);//Search query without limit clause (No pagination)
$recordsFiltered = count(getData($sql));//Count of search result
/* SQL Query for search with limit and orderBy clauses*/
$sql = sprintf("SELECT * FROM %s %s ORDER BY %s %s limit %d , %d ", $tableName, $where, $orderBy, $orderType, $start, $length);
$data = getData($sql);
}
/* END SEARCH
if there is no data from the search field, we query from all records
*/
else {
$sql = sprintf("SELECT * FROM %s ORDER BY %s %s limit %d , %d ", $tableName ,$orderBy,$orderType ,$start , $length);
$data = getData($sql);
$recordsFiltered = $recordsTotal;
}
$response = array(
"draw" => intval($draw),
"recordsTotal" => $recordsTotal,
"recordsFiltered" => $recordsFiltered,
"data" => $data
);
echo json_encode($response);
} else {
echo "NO POST Query from DataTable";
}
?>
