Tuesday, 25 September 2018

Load data dynamically on page scrolling using jQuery, Ajax, PHP, and MySQL



When we have too many records or contents to display on the web page, we used pagination. Pagination simply divides all the records among pages, such as page 1, page 2 and so on. But, nowadays we replaced this pagination mechanism with a more friendly feature that loads data dynamically from the database on page scrolling. So, in this tutorial, I will show you how to load data dynamically on page scrolling using jQuery, Ajax, PHP, and MySQL. This tutorial explained in very easy steps with live demo and complete source code to download. You may also like to read — Bootstrap pagination using PHP and MySQL.

File Structure

Before starting this tutorial please look at the file structure.
  • db.php (database configuration file)
  • index.php (main UI file)
  • get-data.php (fetch data from database)
  • bootstrap folder (CSS and js)

Database Configuration (db.php)

  1. <?php
  2. define('HOSTNAME','localhost');
  3. define('DB_USERNAME','USERNAME');
  4. define('DB_PASSWORD','PASSWORD');
  5. define('DB_NAME', 'DATABASE_NAME');
  7. //global $con;
  8. $con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("error");
  9. // Check connection
  10. if(mysqli_connect_errno($con)) echo "Failed to connect MySQL: " .mysqli_connect_error();
  11. ?>
Change the database name, database username and password according to your credentials. To get the dummy sample data please download the complete source code from the below download link.

Include Bootstrap Files (index.php)

  1. <link rel="stylesheet" href="bootstrap/css/bootstrap.min.css">
  2. <script src="bootstrap/js/jquery.min.js"></script>
  3. <script src="bootstrap/js/bootstrap.min.js"></script>
This example is responsive in design by using bootstrap.

HTML – Display Data (index.php)

  1. <body>
  2. <div class="container-fluid mtb-margin-top">
  3. <div class="row">
  4. <div class="col-md-12">
  5. <div id="output"><?php include('get-data.php');?></div>
  6. <div class="loader"><img src="loader.gif" /></div>
  7. </div>
  8. </div>
  9. </div>
  10. </body>
The above HTML code is very simple to understand. On page load it will display the content from the get-data.php page.

jQuery and Ajax – Load Data Dynamically (index.php)

  1. <script type="text/javascript">
  2. $(window).scroll(function() {
  3. if($(window).scrollTop() == ($(document).height() - $(window).height())) {
  4. var total_pages = parseInt($("#total_pages").val());
  5. var page = parseInt($("#page").val())+1;
  6. if(page <= total_pages) {
  7. load_more_data(page, total_pages);
  8. }
  9. }
  10. });
  12. function load_more_data(page, total_pages) {
  13. $("#total_pages, #page").remove();
  14. $.ajax({
  15. url: 'get-data.php',
  16. type: "POST",
  17. data: {page:page},
  18. beforeSend: function(){
  19. $(".loader").show();
  20. },
  21. complete: function(){
  22. $('.loader').hide();
  23. if(page == total_pages) {
  24. $(".loader").html('... No more article! ...').show();
  25. }
  26. },
  27. success: function(data){
  28. $("#output").append(data);
  29. },
  30. error: function(){
  31. $(".loader").html("No data found!");
  32. }
  33. });
  34. }
  35. </script>
When user scrolls down the page the $(window).scroll(function() {..}) funtion checks the position of the scrollbar and call the load_more_data() if it reaches at the bottom of the page. load_more_data() function sends data using post method to the get-data.php page and appends the data at the bottom of the output div whatever it receives from the get-data.php page using jQuery and Ajax.

PHP And MySQL – Fetch Data From Database (get-data.php)

  1. <?php include('db.php'); ?>
  2. <?php
  3. /*How many records you want to show in a single page.*/
  4. $limit = 10;
  5. if(isset($_POST['page']) && $_POST['page'] != "") {
  6. $page = $_POST['page'];
  7. $offset = $limit * ($page-1);
  8. } else {
  9. $page = 1;
  10. $offset = 0;
  11. }
  14. $query = "select count(*) 'total_rows' from `posts`";
  15. $res = mysqli_fetch_object(mysqli_query($con, $query));
  16. $total_pages = ceil($res->total_rows/$limit);
  19. $query = "select * from `posts` limit $offset, $limit";
  20. $res = mysqli_query($con, $query);
  21. if(mysqli_num_rows($res) > 0) {
  22. $results = "";
  23. $results .= '<input type="hidden" name="total_pages" id="total_pages" value="' . $total_pages . '">';
  24. $results .= '<input type="hidden" name="page" id="page" value="' . $page . '">';
  25. $results .= '<div id="results">';
  26. while($row = mysqli_fetch_object($res)) {
  27. $results .= '<h1 class="post-title"><a href="'.$row->link.'" target="_blank">'.$row->title.'</a></h1>';
  28. $results .= '<p class="text-justify">' . $row->content . '</p>';
  29. }
  30. $results .= '</div>';
  31. echo $results;
  32. }
  33. ?>
  35. <?php mysqli_close($con); ?>
At last, get-data.php fetches records dynamically from the database based on the data receives from the jQuery Ajax request.
Try online live demo on – load data dynamically using jQuery, Ajax, PHP and MySQL from the below demo link and download the complete source code from the below download link. Please like and share this tutorial with others.

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)