Ajax scroll down pagination with Php and Mysql

18

My last article on ajax pagination with php and mysql was very well appreciated. This time I am posting ajax scroll down pagination with PHP and MySQL. In this tutorial it has two demos; first one where records are displayed when you scroll down to the bottom of the page and the other one is where you scroll down the page and you get a button asking to display next set of data.

View Demo

Create database and table in MySQL database

First of all lets start with making mysql database and table. I will be using country names.

The database part is done, now the html part. Add a div container with an unique id that will hold all the data. Also make two more container for loader image and loader message.

Implementing ajax scroll down pagination with PHP and MySQL

In this demo, user will scroll down to the bottom of page and data will be displayed accordingly until all data is displayed.

First let us create a database connection file config.php. I will be using PDO classes. In case you want to learn by using some example, I have made a small mini project with PDO classes. Download Simple address book with php and mysql using pdo.

Moving on now we need to call an ajax request on page load to get the first set of data, after that when user scrolls down the page the next set of data will be displayed automatically. I have created a function displayRecords() that will display the data via ajax request. Check the code below.

You have to call this function on page load. I have added 3 javascript variables limit, offset, busy.

  • limit – The number of records to display per request.
  • offset – The starting pointer of the data.
  • busy – Check if current request is going on or not.

Trick behind the scroll down pagination

The main trick for this scroll down pagination is binding the window scroll event and checking with the data container height.

Now on the php part. check the code below its very simple I have used offset with limit.

That was all for demo1, now in demo 2 the trick is very simple, when first set of records is displayed, I am adding a buton with the results set to the div container which is binded with the click event. So when user clicks on it, an ajax request will be fired. When there is no data, I am adding a attribute with the button data-attr=”nodata” which provides a check when user tries to fetch the records even if its not there.

View Demo
Download

Share.
 

About Author

Entrepreneur & Dreamer

I am a passionate Software Professional, love to learn and share my knowledge with others.

Software is the hardware of my life.

18 Comments

  1. James Vornhagen on

    why are you using Prepared_Statements when you don´t use them?
    [code]
    $sql = “SELECT countries_name FROM countries WHERE 1 ORDER BY countries_name ASC LIMIT $limit OFFSET $offset”;

    try {

    $stmt = $DB->prepare($sql);

    $stmt->execute();

    $results = $stmt->fetchAll();

    } catch (Exception $ex) {

    echo $ex->getMessage();

    }

    [/code]

    you could use instead

    [code]
    $sql = “SELECT countries_name FROM countries WHERE 1 ORDER BY countries_name ASC LIMIT ? OFFSET ? “;
    [/code]

    in Combination with:
    [code]
    $stmt->execute(arrayi($limit, $offset));
    [/code]

    Kind regards 🙂

    But nice tutorial!! Thanks 🙂

  2. i want to passing a variable name id and get it through getrecords.php file…

    i change the java code like

    insert var id = 150; at the line below the var offset = 0;

    and change data : data: “limit=” + lim + “&offset=” + off + “&id=” + id

    and insert a line in getrecords.php file like $id = $_GET[‘id’];

    it does not work… pls help me…

    • in the ajax function displayRecords() you can add one more parameter say anotherID
      and in this line
      data: “limit=” + lim + “&offset=” + off,
      change it to data: “limit=” + lim + “&offset=” + off+”&anotherID=”+anotherID,

      in your PHP get the value of anotherID as $_GET[“anotherID”];

      • i changed the codings like your suggestion. it’ll send & get the id. but now the problem is it does not show “no more records” even the results was over. it’ll shows “loading please wait” instead of “no more records”. hw can i solve this?

  3. Very good tutorial really helps me i viewed another tutorial on infinite scroll pagination on talkerscode.com you can also view very easy coded with demo.

  4. Hi,

    I have used your code. It’s nice and perfect. But i have a problem. For the page load the first 10 records comes exactly. But while scroll, the second 10 records comes exactly. But the page refreshes at that time. How can i rectify this. Kindly give us a suggestion

Leave A Reply

CommentLuv badge

Get more stuff like this
in your inbox

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.