Data Load While Page Scrolling (infinite scroll) with jQuery PHP and MySQL

Infinity scroll is a way to load more data using Ajax request while page goes to scroll down. Now days this technique is mostly used by the most of the successful websites like Facebook, Google and Twitter. You will be see that on these web sites as long as you scrolling down the page loads more and more data or posts, that is called infinity scroll or endless scroll. Though it can be possible by using jQuery's jQuery.ajax or jQuery.load method but now days there are many jQuery plugins (like, Infinity Scroll, Infinite Ajax Scroll) are available to make it more easy. Today I am going to show you how we can turn a PHP and MySQL paginavigation based website into dynamic infinity scroll with jQuery Infinite Ajax Scroll plugin.


Live Demo Download

Database Structure

First we need to create a database table. Because, we need some large amount of data to make this demo up and run here I am using Sakila simple database's actor_info table. The structure of this table looks like....

CREATE TABLE `actor_info` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `film_info` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DB Configuration

The config.php file is responsible for database connect. I also include a variable named $limit with value of 10. It is the number of content you want to show on per page. You can increase or decrease this number as you want.

# db configuration 
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', 'root');
define('DB_NAME', 'dbname');

$limit = 10; #item per page
# db connect
$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die('Could not connect to MySQL DB ') . mysql_error();
$db = mysql_select_db(DB_NAME, $link);

PHP and HTML

Take a look at the PHP and HTML code, this PHP code retrieves data from MySQL database with simple pagination.

<?php
include('config.php');
$page = (int) (!isset($_GET['p'])) ? 1 : $_GET['p'];
# sql query
$sql = "SELECT * FROM actor_info ORDER BY id DESC";
# find out query stat point
$start = ($page * $limit) - $limit;
# query for page navigation
if( mysql_num_rows(mysql_query($sql)) > ($page * $limit) ){
  $next = ++$page;
}
$query = mysql_query( $sql . " LIMIT {$start}, {$limit}");
if (mysql_num_rows($query) < 1) {
  header('HTTP/1.0 404 Not Found');
  echo 'Page not found!';
  exit();
}
?>
<!doctype html>
<html lang="en">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  <title>jQuery Load While Scroll</title>
</head>
<body>
<div class="wrap">
  <h1><a href="#">Data load while scroll</a></h1>

  <!-- loop row data -->
  <?php while ($row = mysql_fetch_array($query)): ?>
  <div class="item" id="item-<?php echo $row['id']?>">
    <h2>
      <span class="num"><?php echo $row['id']?></span>
      <span class="name"><?php echo $row['first_name'].' '.$row['last_name']?></span>
    </h2>
    <p><?php echo $row['film_info']?></p>
  </div>
  <?php endwhile?>

  <!--page navigation-->
  <?php if (isset($next)): ?>
  <div class="nav">
    <a href='index.php?p=<?php echo $next?>'>Next</a>
  </div>
  <?php endif?>
</div><!--.wrap-->
</body>
</html>

JavaScript part

In the JavaScript section we need to first include jQuery and Infinity Ajax Scroll library. In the second pert we need to call jQuery.ias method and configure it. Take a look on the comments

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script type="text/javascript" src="js/jquery-ias.min.js"></script>
<script type="text/javascript">
  $(document).ready(function() {
    // Infinite Ajax Scroll configuration
    jQuery.ias({
      container : '.wrap', // main container where data goes to append
      item: '.item', // single items
      pagination: '.nav', // page navigation
      next: '.nav a', // next page selector
      loader: '<img src="css/ajax-loader.gif"/>', // loading gif
      triggerPageThreshold: 3 // show load more if scroll more than this
    });
  });
</script>

64 comments:

  1. Great tutorial really helpful, i needed something like to integrate into a mobile app i am creating, Is there any way i get separate the html part from the php so that i make the request let say from index.html and retrieve the data from process.php?

    ReplyDelete
    Replies
    1. If you want to retrieve data form other files or resources it is better to use ajax and json. You can take a look at this tutorial https://tutsplus.com/lesson/ajax/

      Delete
  2. Very helpful tutorial! Great job!

    ReplyDelete
  3. thank you very much

    ReplyDelete
  4. Hey! thank you vere much!.

    How doy you configured .htaccess for dinamic url?

    For me in http://mydomain.com/index.php works your code
    but in http://mydomain.com/wherever/ not works

    in htaccess I have this Rewriterule ^wherever/(.*)/ index.php
    and load the file but not take the page

    ReplyDelete
    Replies
    1. very helpful tutorial, but i am having the same problem. did you figure it out?

      Delete
  5. Good work, thank you very much.
    Could you make a tutorial for use this infinite scrolling with laravel 4 please?

    ReplyDelete
    Replies
    1. You welcome!
      If you use Laravel 4 Pagination it will work for you. just use pagination::simple

      Delete
    2. Hi.. how to use infinite ajax scroll in laravel.?

      Delete
  6. Hai how to use that code viewer which you have used to display codes...

    ReplyDelete
    Replies
    1. Im using pretty print syntax highlighter by Google. If you want to use this in you project visit
      http://net.tutsplus.com/tutorials/html-css-techniques/quick-tip-how-to-add-syntax-highlighting-to-any-project/
      Thanks!

      Delete
  7. Script works fine. Thank you very much!

    ReplyDelete
  8. Hi, I am facing problem with tabs structure i have tabs on left side of my page on each of them when i click respective content shows others hide. Now when i click on one tab i have content which has infinite scroll used when i scroll the page it goes across smoothly no problem with page no proper but when i go to some another tab on the left side and then i come back to this tab the page not getting load properly gets hanged with no pagination. Please help urgent.

    ReplyDelete
  9. Hi, great tutorial! I have this working but also need to have filters and sorting. I currently have the filters working without ajax so whenever a filter is selected it refreshes the whole page. I am struggling to find out how to have the filters work as well as the infinite scrolling. Can you provide any advice on how to approach this? Many thanks in advance!

    ReplyDelete
  10. Awesome! Just awesome! Thanks a lot, grettings from Brasil ;)

    ReplyDelete
  11. Very Nice! Thank you!

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. Thank you so much for this api . I've been looking for such a tuto to integrate the ias() plugin into my project.

    ReplyDelete
  14. One problem - It breaks the Back button!

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  16. it doesn't work with mobile chrome =(

    ReplyDelete
  17. hello, thank you for that explanation. I'm trying to implement a search form. and view content with this system. Unfortunately, if I change the query $sql
    example
    from
    $sql = "SELECT * FROM actor_info ORDER BY id DESC";
    to
    $sql = "SELECT * FROM actor_info WHERE name LIKE '%$name%' ORDER BY id DESC";
    only on the first page i see the filtered results, while, when it loads the second page, I see all

    ReplyDelete
    Replies
    1. same problem with me , do you figure it out ? .

      Delete
  18. Ca you explain how does it get $_GET['p'], and calculate $page?

    ReplyDelete
  19. Good work, thank you very much.

    ReplyDelete
  20. to xyz : yes, because u don't put LIMIT in your queries

    ReplyDelete
  21. good stuff! but it seems not working well with WHERE 2 conditions. any suggestion????

    ReplyDelete
  22. Hi , How can I code your nice tutorial in Codeigniter ?

    Thanks you in advance. :)

    ReplyDelete
  23. Thank you so much for your awesome guide. it is working like a magic.

    ReplyDelete
  24. I have the same problems as xyz above. I have a sql that shows products where category= $_GET['cat'] but as he describes when it loads page number 2 it shows everyone in my database. Can you tell me why?

    $limit = 15;
    $page = (int) (!isset($_GET['p'])) ? 1 : $_GET['p'];
    $catID = (int) $_GET['cat'];

    # sql query
    $sql = "Select * From tbl_produkter Where visa=1 and (kategoriID=$catID or kategoriID2=$catID or kategoriID3=$catID) Order by sort";
    # find out query stat point
    $start = ($page * $limit) - $limit;
    # query for page navigation
    if( mysql_num_rows(mysql_query($sql)) > ($page * $limit) ){
    $next = ++$page;
    }
    $query = mysql_query( $sql . " LIMIT {$start}, {$limit}");
    if (mysql_num_rows($query) < 1) {
    header('HTTP/1.0 404 Not Found');
    echo 'Page not found!';
    exit();
    }

    ReplyDelete
  25. Very good script.
    But I have a problem, it's in conflict with other jquery script... my main menu stop works. Can I do something to avoid the conflict?

    ReplyDelete
    Replies
    1. I changes the position of the script and now everything work well.

      Delete
  26. Great job, there is another one, it may help you too:
    Ajax Scroll Paging Using jQuery, PHP and MySQL
    http://www.bewebdeveloper.com/tutorial-about-ajax-scroll-paging-using-jquery-php-and-mysql

    ReplyDelete
    Replies
    1. Thanks a lot. This works in most major browsers

      Delete
  27. I have an issue with this script. Once I load items the javascript of the items (thumbnail zoom) doesnt work. Just with the already loaded items. Could you please help me?

    ReplyDelete
  28. not working with firefox in my case

    ReplyDelete
  29. Great! Can u just help with this: How can i remove hashtag from url? (/#/page?=1) and go just /page?=1. THANKS!

    ReplyDelete
  30. This post is very useful for us. Because we have a lot of
    tips and tricks from this post. Thank you for this amazing post share. I many
    tips about bd career as well. If you want to know more about a career sites, please visit our website.

    www.bd-career.com

    ReplyDelete
  31. Hi, nice scroll! :) However, I'm curious, how can I get this to work inside a div container?
    I posted a question on Stackoverflow, if anyone is able to help I would really appreciate it.
    Link: http://stackoverflow.com/questions/32607784/adding-ajax-infinity-scroll-inside-a-div

    ReplyDelete
  32. Why is it that when it comes to the end of the page, instead of loading on scroll, it displays a load more ?

    ReplyDelete
  33. Hello
    I have tried your system and tried to convert it into procedural but i face a problem with binding of parametters. What is the equivalent of that ? How to convert this into procedural

    $query->bindParam(':last_id', $last_id, PDO::PARAM_INT);
    $query->bindParam(':limit', $limit, PDO::PARAM_INT);
    $query->execute();

    ReplyDelete
  34. awesome plugin. . very easy to use than other jquery plugins found on the net

    ReplyDelete
  35. Obat Kanker Kolon Stadium 4 - Ace Maxs merupakan solusi terbaik untuk mencegah dan mengobati kanker kolon secara aman dan tanpa ada efek samping, BUKTIKAN bahwa ace maxs betul betul membasmi penyakit kanker kolon dengan cepat dan aman. baca juga
    jelly gamat gold g di apotik

    ReplyDelete
  36. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Hello
      i try use your code in my wordpress site but every time I scrolling the page I get the same 10 items from DB you can see in fire bug:

      GET http://localhost/mydomain.com/index.php?p=2
      GET http://localhost/mydomain.com/
      GET http://localhost/mydomain.com/index.php?p=2
      GET http://localhost/mydomain.com/
      GET http://localhost/mydomain.com/index.php?p=2
      GET http://localhost/mydomain.com/

      the P parameter is on 2 every time, and i didnt have index.php page.

      Delete
  37. Hello
    i try use your code in my wordpress site but every time I scrolling the page I get the same 10 items from DB you can see in fire bug:

    GET http://localhost/mydomain.com/index.php?p=2
    GET http://localhost/mydomain.com/
    GET http://localhost/mydomain.com/index.php?p=2
    GET http://localhost/mydomain.com/
    GET http://localhost/mydomain.com/index.php?p=2
    GET http://localhost/mydomain.com/

    the P parameter is on 2 every time, and i didnt have index.php page.

    ReplyDelete
    Replies
    1. I have the same problem on my website. Furthermore when i scroll the bottom, page becomes frozen for 10-15 seconds. After that it shows the same 10 items.

      Delete
  38. Thank you so much
    is very simple and effective.
    i thinking that infinite scroll is more hard
    but if your tutorial is easy.
    Again, thank you - Team Mestre Search

    ReplyDelete

Go to top