MySQL + Pagination, Loading dynamic content

olliej777

Hello,

I'm very new to datatables, and in general web developments, so apologies for the question.

I am using PHP and MySQL to pull data from my database, however there are over 10,000 records in this database. I've had to use pagination on my sql and return a limit of 100 records per request otherwise it's too heavy. I've developed a very basic HTML/JS that will dump out this data depending on the page requested.

The issue I now have is that my datatable only displays the first page of 100 records for obvious reason. I suppose my question is,

a) How do I connect the pagination within datatables to my pagination on MySQL.
b) How do I then load the next page from MySQL to then feed back into the datatable once a user hits 'next'?

Currently I am using a basic JavaScript that simply dumps out the page from MySQL depending on the requested page;

function showPortfolio(page) {

  curPage = page;
  console.log("showPortfolio, page; " + page + " curPage; " + curPage);

  var xmlhttp = new XMLHttpRequest();
  xmlhttp.onreadystatechange = function() {

      if (this.readyState == 4 && this.status == 200) {
          console.log("Getting table in background...");

          //this is basically a var dump
          document.getElementById("txtOutput").innerHTML = this.responseText;
      }
  };

  //calls to my php script that gets the records from MySQL using the 'page'
  xmlhttp.open("GET","get_portfolio.php?page="+page,true);
  xmlhttp.send();
}

Thank you.

colin

It sounds like you're redeveloping serverSide - this limits the amount of data returned by the server to ensure performance. The protocol is discussed here. Also see examples here. If you download the DataTables repo, there are examples of the server-side scripts in /examples/server_side/scripts,

Cheers,

Colin