Ajax scroll down pagination with Php and Mysql

by  
Share:
Ajax scroll down pagination with Php and Mysql

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.

DROP DATABASE IF EXISTS `demo`;
CREATE DATABASE `demo`;
USE `demo`;

CREATE TABLE IF NOT EXISTS `countries` (
  `countries_id` int(11) NOT NULL AUTO_INCREMENT,
  `countries_name` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`countries_id`),
  KEY `idx_countries_name_zen` (`countries_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;

INSERT INTO `countries` (`countries_id`, `countries_name`) VALUES
(240, 'Aaland Islands'),
(1, 'Afghanistan'),
(2, 'Albania'),
(3, 'Algeria'),
(4, 'American Samoa'),
(5, 'Andorra'),
(6, 'Angola'),
(7, 'Anguilla'),
(8, 'Antarctica'),
(9, 'Antigua and Barbuda'),
(10, 'Argentina'),
(11, 'Armenia'),
(12, 'Aruba'),
(13, 'Australia'),
(14, 'Austria'),
(15, 'Azerbaijan'),
(16, 'Bahamas'),
(17, 'Bahrain'),
(18, 'Bangladesh');

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.
<!-- this will hold all the data -->
<div id="results"></div>
<!-- loading image -->
<div id="loader_image"><img src="loader.gif" alt="" width="24" height="24"> Loading...please wait</div>
<!-- for message if data is avaiable or not -->
<div id="loader_message"></div>

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.

<?php

define('DB_DRIVER', 'mysql');
define('DB_PREFIX', '');
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASSWORD", "");
define("DB_DATABASE", "demo");

try {
  $DB = new PDO(DB_DRIVER . ':host=' . DB_HOST . ';dbname=' . DB_DATABASE, DB_USER, DB_PASSWORD, $dboptions);
} catch (Exception $ex) {
  echo $ex->getMessage();
  die;
}
?>

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.
<script type="text/javascript">
var busy = false;
var limit = 15
var offset = 0;

function displayRecords(lim, off) {
        $.ajax({
          type: "GET",
          async: false,
          url: "getrecords.php",
          data: "limit=" + lim + "&offset=" + off,
          cache: false,
          beforeSend: function() {
            $("#loader_message").html("").hide();
            $('#loader_image').show();
          },
          success: function(html) {
            $("#results").append(html);
            $('#loader_image').hide();
            if (html == "") {
              $("#loader_message").html('<button data-atr="nodata" class="btn btn-default" type="button">No more records.</button>').show()
            } else {
              $("#loader_message").html('<button class="btn btn-default" type="button">Loading please wait...</button>').show();
            }
            window.busy = false;

          }
        });
}
</script>

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.

$(document).ready(function() {
if (busy == false) {
  busy = true;
  // start to load the first set of data
  displayRecords(limit, offset);
}
});

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.

$(document).ready(function() {

$(window).scroll(function() {
          // make sure u give the container id of the data to be loaded in.
          if ($(window).scrollTop() + $(window).height() > $("#results").height() && !busy) {
            busy = true;
            offset = limit + offset;

            displayRecords(limit, offset);

          }
});

});

Now on the php part. check the code below its very simple I have used offset with limit.
<?php
require_once("config.php");

$limit = (intval($_GET['limit']) != 0 ) ? $_GET['limit'] : 10;
$offset = (intval($_GET['offset']) != 0 ) ? $_GET['offset'] : 0;

$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();
}
if (count($results) > 0) {
  foreach ($results as $res) {
    echo '<h3>' . $res['countries_name'] . '</h3>';
  }
}
?>

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.
<script type="text/javascript">
      var limit = 10
      var offset = 0;

      function displayRecords(lim, off) {
        $.ajax({
          type: "GET",
          async: false,
          url: "getrecords.php",
          data: "limit=" + lim + "&offset=" + off,
          cache: false,
          beforeSend: function() {
            $("#loader_message").html("").hide();
            $('#loader_image').show();
          },
          success: function(html) {
            $('#loader_image').hide();
            $("#results").append(html);

            if (html == "") {
              $("#loader_message").html('<button data-atr="nodata" class="btn btn-default" type="button">No more records.</button>').show()
            } else {
              $("#loader_message").html('<button class="btn btn-default" type="button">Load more data</button>').show();
            }

          }
        });
      }

      $(document).ready(function() {
        // start to load the first set of data
        displayRecords(limit, offset);

        $('#loader_message').click(function() {

          // if it has no more records no need to fire ajax request
          var d = $('#loader_message').find("button").attr("data-atr");
          if (d != "nodata") {
            offset = limit + offset;
            displayRecords(limit, offset);
          }
        });

      });

    </script>

View Demo
Download

Get the latest updates directly in your inbox for FREE: Subscribe here

Share your feedback / let me know your doubts regarding this tutorial in the comment box given below.

Share:
Hasan

Shahrukh Khan (Hasan)

A software engineer who's a die-hard coder, blogger, dreamer and mentor with years of expertise in web development. Know more...

Related Posts

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 🙂

Reply
Divya on

Nice Trick with avoiding data hang.. thanks

Reply
Subi on

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…

Reply
Shahrukh Khan on

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”];

Reply
Subi on

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?

Shahrukh Khan on

there must me some logical or syntax mistake, try to find the error through the mozilla/chrome debugger.

rishab on

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.

Reply
Muthu on

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

Reply
Shahrukh Khan on

it must be the case where your pagenum is not updated

Reply
Muthu on

Hi,

While placing video i got the page refreshing issue on scroll. Kindly suggest

Reply
Shahrukh Khan on

I dont understand your issue

Reply
Muthu on

Im trying to intergrade youtube, vimeo and dailymotion videos. While scroll to the next page , the page refreshes due to iframe loading. Kindly help me in resolving this….

Chinonso on

Hi,


Loading…please wait

In my code, I have class in place of id how will I replace it

Reply
Shahrukh Khan on

use $(“.classname) instead to $(‘#idname’)

Reply
Santhosh on

thanks!

Reply
Akash on

Nice tutorial bro.. thanks.

Reply
Shahrukh Khan on

Thanks

Reply
nik on

heei how to change url while loading data it remains static!!!

Reply
Shahrukh Khan on

Didn’t get your point

Reply
Prakash on

It works on the main page.
But when I apply it for a division it doesn’t work.
I put the code in a div in which I want auto scrolling. It shows the first 15 data but does not load more when I scroll down in the division. Please help!!

Reply
Shahrukh Khan on

what error are you getting.

Reply
sumit kumar pradhan on

really helpful…

Reply
Joseph on

Hi, can you help me to create a message chat where you scroll up you can see the previous messages? i need help with that.

Reply
FIncy Francis on

How will I write when I click on load more data button as loading….?Please assist

Reply
Shahrukh Khan on

its already working that way please refer tutorial or please be specific of what you want.

Reply
Jos on

Hi, I am thinking about creating a facebook like chat where we could retrieve the previous conversation between two exact users who were chatting but it’s a little bit tricky since it only retrieves me the data without a rational order. do you think you could help me there I have the script but that’s only my problem receiving the old data since it’s only showing the lastest 10 recent messages between the conversation.

Reply
Jos on

By the way, I have the chat but it’s not finished yet due to that problem I just mentioned you. Is there any possibility I could show some few screenshots of what I created so you can see how it looks like and where I wanna ahead with this project? this is an example “SELECT * FROM `conversation` WHERE(`receiver` = ‘joseph’ AND `sender`= ‘angelika’ OR
`receiver` = ‘angelika’ AND `sender` = ‘joseph’) AND `id` < $lastelement ORDER BY `id` DESC LIMIT $limit.

those two variable have numeric values that will dynamically change depending on the number associated with each id of the conversation.

Help me here please and thank you in advance.

Reply
Sam on

Thanks so much for this tutorial. But I have a little issue. I followed your step very carefully and my products load on scroll. But when when all of my products are loaded, the “Load…please wait” message or animated gif still displays. Can you please help me with this? How come my “Lo more records” message do not display when no more product to load? I have about 20 items on my page.

Reply
Shahrukh Khan on

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.

Reply
Herman on

HAlo friend, i have the same problem as mntioned hire;
var anotherID = 708;

at the function:

data: “limit=” + lim + “&offset=” + off +”&anotherID=”+anotherID,

at the getrecords:
$id=$_GET[‘anotherID’];
$sql = “SELECT * FROM imovel where id =$id ORDER BY id DESC LIMIT $limit OFFSET $offset”; but it does not display nothing can you halp me?

Reply
Herman on

$sql = “SELECT * FROM imovel where id =’$id’ ORDER BY id DESC LIMIT $limit OFFSET $offset”;

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.