CODEWITHSUNDEEP
jquerypaginationdatatables
Md imran
Md imran

Reputation: 67

pagination not working in data-tables , showing all records at a time?

since few days I have been working with data-table . everything is working quite fine, just issue is that pagination not working perfectly.enter image description here

I used the below code for fetching the data from database-

$data = array();
while( $row=mysqli_fetch_array($query) ) {  // preparing an array
    $nestedData=array();        
    $nestedData[] = $row["category"];
    $nestedData[] = $row["itemValue"];
    $nestedData[] = $row["quantity"];
    $nestedData[] = $row["location"];
    $nestedData[] = $row["comment"];
    $nestedData['file'] = $row["file"];
    $nestedData['itemId'] = $row["itemId"];
    $data[] = $nestedData;
}



$json_data = array(
            "draw" => intval( $requestData['draw'] ),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw. 
            // "totalFiltered"    => intval( $totalData ),  // total number of records
            "recordsTotal"  => intval( $totalData ),  // total number of records
            "recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
            "aaData" => $data  // total data array
            );

echo json_encode($json_data);

and for datatable API used the below-

var table = $('#example').DataTable( {
    "serverSide": true,
    "bProcessing": true,
    "paging": true,        
    "Filter": true,
    "ajax": {
            url:"select.php",
            type:"post",
        },        
    "rowId":'itemId',
    "file":'file',
    // "pagingType": "simple_numbers",
    "columnDefs": [ {
        "targets": -1,
         // "data": data,
        "defaultContent": "<div class='btn-group'><button type='button'  class='viewItem btn btn-success'><span class='glyphicon glyphicon-eye-open' aria-hidden='false'></span></button><button type='button'  class='editItem btn btn-success'><span class='glyphicon glyphicon-pencil' aria-hidden='false'></span></button></div>"
    } ]

});

In the image you can see, even the pagination length is 10 but its showing all records at a time and even if I click on pagination button it doesn't change the rows in datatables. please anyone there to suggest ? thanks

Upvotes: 2

Views: 10744

Answers (2)

STK
STK

Reputation: 167

I would like to explain the Omar's answer and hope that this will be beneficial for others especially for beginners.

If you are sending the data to DataTable from server and expecting that it will automatically paginate your records based on the setting you defined in your Datatable declaration, so you wrong. You will have to code the pagination functionality at server side. Whenever you would click on "NEXT" button of Datatable, it will call the server code (Web Service, method, etc) and sends a list of parameters using which you can very easily implement pagination.

Please read this carefully and you can very easily implement this in whatever technology you are working with.

Here are the steps you need to follow to correctly implement the pagination using DataTable.

Its really really simple and easy guys, just pay attention.

I am using DataTable, Spring MVC, Spring REST and Hibernate.

JavaScript code

    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>JSP Page</title>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.13/css/jquery.dataTables.min.css">
    <script src= "https://code.jquery.com/jquery-1.12.4.js"></script>
    <script src= "https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script>

    <script type="text/javascript">
        $(document).ready(function() {
            $('#example').DataTable( {
                "processing": true,
                "serverSide": true,
                "paging":true,
                "ajax": "rest/getQuestions",
                "columns": [
                    { "data": "id" },
                    { "data": "name" },
                    { "data": "city" },
                    { "data": "country" },
                    { "data": "date" },
                    { "data": "email" },
                    { "data": "subject" },
                    { "data": "question" },
                    { "data": "status" },
                    { "data": "views" },
                    { "data": "submittedBy" }
                ]
            } );
        } );
    </script>
</head>

rest/getQuestions is WebService call. The name of my table is Question_tbl and I am showing all the columns of my table.

HTML Table

        <table id="example" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th> ID </th>
                <th> Name </th>
                <th> City </th>
                <th> Country </th>
                <th> Date </th>
                <th> Email </th>
                <th> Subject </th>
                <th> Question </th>
                <th> Status </th>
                <th> Views </th>
                <th> Submitted By </th>
            </tr>
        </thead>

    </table>

Spring Rest Service

    import com.fasterxml.jackson.databind.ObjectMapper;
    import org.springframework.web.bind.annotation.RequestParam;
    import com.fasterxml.jackson.core.JsonProcessingException;
    import com.fasterxml.jackson.databind.JsonMappingException;

    @RequestMapping(value = "/rest/getQuestions", method = RequestMethod.GET)
    public String getPaginatedQuestions(@RequestParam Integer draw, @RequestParam Integer start, @RequestParam Integer length ) {

    List<Question_tbl> paginatedQuestions = questionService.getPaginatedQuestionsSrv(start, length);       
    int questionCount = questionService.getQuestionCountByLimit(300);
    QuestionDataTable dt = new QuestionDataTable();

    dt.setDraw(draw);
    dt.setRecordsTotal(questionCount);
    dt.setRecordsFiltered(questionCount);
    dt.setData(paginatedQuestions);

    ObjectMapper mapper = new ObjectMapper();
    try {
        String jsonGenerated = mapper.writeValueAsString(dt);
        return jsonGenerated;
    } catch (JsonProcessingException ex) {
        Logger.getLogger(QuestionRestController.class.getName()).log(Level.SEVERE, null, ex);
    }
    return new JsonMappingException("Exception").toString();

    }

Question_tbl is my entity table which is mapped to the Database table name Question. The 3 request parameters draw, start, length are sent to server from Datatable.

There are other parameters also which I have shown below but these will be enough to implement pagination. Send them to service layer to get the paginated records. At database layer, you just simply need to implement the following code and you will get the required records.

DAO Method

    public List<Question_tbl> getPaginatedQuestionsDao(int start, int length){

    Query qry = sessionFactory.getCurrentSession().createQuery("from Question_tbl q ORDER BY q.date DESC");
    qry.setFirstResult(start);
    qry.setMaxResults(length);
    return qry.list();
    }

What is QuestionDataTable and its purpose

To get the data in Json format we have a RestService. When calling a RestService, the resultant json that is returned from the service call is not in the format required for DataTable. So We need to take care of the returned JSON to correctly display the data in DataTable. Actual JSON returned was like this 

      {
      "id": 10,
      "name": "Muhammad.s",
      "city": "Lahore",
      "country": "Pakistan",
      "date": 1491549259000,
      "email": "[email protected]",
      "subject": "Testing Subject",
      "question": "Test Question-1",
      "status": "unanswered",
      "views": 0,
      "submittedBy": null
    },
    {
      "id": 8,
      "name": "Tariq.s",
      "city": "Hyderabad",
      "country": "Pakistan",
      "date": 1490465223000,
      "email": "[email protected]",
      "subject": "Subject 2",
      "question": "Test question",
      "status": "unanswered",
      "views": 0,
      "submittedBy": null
    }

But the required format for DataTable should have 3 initial elements which are "draw","recordsTotal","recordsFiltered". For further clarification about JSON format please check this link. Here is the required & correct format.

{
  "draw": 9,
  "recordsTotal": 12,
  "recordsFiltered": 12,
  "data": [
    {
      "id": 12,
      "name": "Qalb-E-Muhammadi",
      "city": "Oval",
      "country": "England",
      "date": 1491550466000,
      "email": "[email protected]",
      "subject": "Test Subject 1",
      "question": "Test Question",
      "status": "unanswered",
      "views": 0,
      "submittedBy": null
    },
    {
      "id": 11,
      "name": "Buzjani",
      "city": "Sydney",
      "country": "Australia",
      "date": 1491549438000,
      "email": "[email protected]",
      "subject": "Testing Subject",
      "question": "Testing Question",
      "status": "unanswered",
      "views": 0,
      "submittedBy": null
    }
  ]
}

For this purpose I created util class called "QuestionDataTable". 

public class QuestionDataTable {
    private int draw;
    private int recordsTotal;
    private int recordsFiltered;
    private List<Question_tbl> data;
    // Getters, Setters
}

Then I converted QuestionDataTable object to Json via ObjectMapper class defined in Rest service defined above.

Request Parameters returned from DataTable to Web Service

draw:1
columns[0][data]:firstName
columns[0][name]:
columns[0][searchable]:true
columns[0][orderable]:true
columns[0][search][value]:
columns[0][search][regex]:false
columns[1][data]:lastName
columns[1][name]:
columns[1][searchable]:true
columns[1][orderable]:true
columns[1][search][value]:
columns[1][search][regex]:false
columns[2][data]:age
columns[2][name]:
columns[2][searchable]:true
columns[2][orderable]:true
columns[2][search][value]:
columns[2][search][regex]:false
order[0][column]:0
order[0][dir]:asc
start:0
length:10
search[value]:
search[regex]:false

You can check the above parameters sent in HEAD section of request in web browser after making the Rest Service call. The image is chrome example Chrome example

I know its a long post and I have tried my best to cover all relevant aspect so that you wont have to jump here & there. I hope that this will save your precious time in finding the relevant solution.

Upvotes: 2

Omar
Omar

Reputation: 527

// You missing to put query code , anyway see the below example 
$sql="SELECT * FROM table WHERE category = '$code' ";
$query_count=mysql_query($sql);

$per_page =30;//define how many games for a page
$count = mysql_num_rows($query_count);
$pages = ceil($count/$per_page);

if($_GET['page']==""){
$page="1";
}else{
$page=$_GET['page'];
}
$start    = ($page - 1) * $per_page;
$sql     = $sql." LIMIT $start,$per_page";
$query2=mysql_query($sql);

// Hope you can find your solution here

Upvotes: 0

Related Questions