Load Data on Page Scroll in CodeIgniter using jQuery and Ajax

Infinite scroll is the most user-friendly way to add pagination in the data list. With infinite scroll pagination, the user does not need to click the link to load data from the database. Rather than the dynamic content is loaded from the server automatically while scrolling page down. Infinite Scrolling is the best replacement of links pagination for autoloading more data from the database.

Generally, CodeIgniter Pagination library is used to add pagination links to the data list. But if you want to implement an advanced pagination functionality, infinite scroll is the best option. Load data on scroll functionality can be easily implemented using jQuery and Ajax. When the user reaches the end of the web page, the more data is retrieved from the MySQL database and additional content is loaded in the web page while scrolling the page down. In this tutorial, we will show you how to load data on page scroll in CodeIgniter using jQuery and Ajax.

In the example code, we will implement infinite scroll pagination for the posts data list in CodeIgniter application.

• Fetch and list the post data from the database.
• Detect page scroll down using jQuery.
• Make an AJAX request to load data from the database.
• Display more post data under the previously loaded data.

Create Database Table

To store the posts data a table is required in the database. The following SQL creates a posts table with some basic fields in the MySQL database.

CREATE TABLE `posts` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `content` text COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL,
 `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1=Active, 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Controller (Posts.php)

The Posts controller contains 3 functions, __construct(), index(), and loadMoreData().
__construct():

• Load Post model to fetch data from the database.
• Set per page data list limit.

index():

• Fetch posts data from the database using getRows() function of Post model.
• Pass the post data to view and load this view.

loadMoreData(): This method is called by the Ajax request to load more data.

• Get the ID of the last displayed post.
• Fetch the posts data based on the last post ID.
• Pass the post data to the view to load more post data.

<?php defined('BASEPATH') OR exit('No direct script access allowed');

class Posts extends CI_Controller {
    
    function __construct() {
        parent::__construct();
        
        // Load post model
        $this->load->model('post');
        
        // Per page limit
        $this->perPage = 4;
    }
    
    public function index(){
        $data = array();
        
        // Get posts data from the database
        $conditions['order_by'] = "id DESC";
        $conditions['limit'] = $this->perPage;
        $data['posts'] = $this->post->getRows($conditions);
        
        // Pass the post data to view
        $this->load->view('posts/index', $data);
    }
    
    function loadMoreData(){
        $conditions = array();
        
        // Get last post ID
        $lastID = $this->input->post('id');
        
        // Get post rows num
        $conditions['where'] = array('id <'=>$lastID);
        $conditions['returnType'] = 'count';
        $data['postNum'] = $this->post->getRows($conditions);
        
        // Get posts data from the database
        $conditions['returnType'] = '';
        $conditions['order_by'] = "id DESC";
        $conditions['limit'] = $this->perPage;
        $data['posts'] = $this->post->getRows($conditions);
        
        $data['postLimit'] = $this->perPage;
        
        // Pass data to view
        $this->load->view('posts/load-more-data', $data, false);
    }
}

Model (Post.php)

The Post model handles the database related work.

• __construct() – Specify the table name of the database.
• getRows() – Fetch the posts data from the database based on the condition and returns as an array.

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Post extends CI_Model{
    
    function __construct() {
        $this->tblName = 'posts';
    }
    
    /*
     * Fetch posts data from the database
     * @param id returns a single record if specified, otherwise all records
     */
    function getRows($params = array()){
        $this->db->select('*');
        $this->db->from($this->tblName);
        
        //fetch data by conditions
        if(array_key_exists("where",$params)){
            foreach ($params['where'] as $key => $value){
                $this->db->where($key,$value);
            }
        }
        
        if(array_key_exists("order_by",$params)){
            $this->db->order_by($params['order_by']);
        }
        
        if(array_key_exists("id",$params)){
            $this->db->where('id',$params['id']);
            $query = $this->db->get();
            $result = $query->row_array();
        }else{
            //set start and limit
            if(array_key_exists("start",$params) && array_key_exists("limit",$params)){
                $this->db->limit($params['limit'],$params['start']);
            }elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){
                $this->db->limit($params['limit']);
            }
            
            if(array_key_exists("returnType",$params) && $params['returnType'] == 'count'){
                $result = $this->db->count_all_results();
            }else{
                $query = $this->db->get();
                $result = ($query->num_rows() > 0)?$query->result_array():FALSE;
            }
        }

        //return fetched data
        return $result;
    }
    
}

View (posts/)

Two view files are used, index.php and load-more-data.php.

index.php:
Initially, a limited number of posts data is fetched from the database and listed on the web page. Additional data will be retrieved via Ajax while page scrolling down.

At first, load the jQuery library.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

The jQuery scroll() method is used to detect the page scroll and an Ajax request is initiated when the user scrolling page down. The last displayed post ID ($lastID) is sent via Ajax to the loadMoreData() method of Posts controller. Once the Ajax success method returns the more posts data, the content HTML will append to the posts list.

<script type="text/javascript">
$(document).ready(function(){
    $(window).scroll(function(){
        var lastID = $('.load-more').attr('lastID');
        if(($(window).scrollTop() == $(document).height() - $(window).height()) && (lastID != 0)){
            $.ajax({
                type:'POST',
                url:'<?php echo base_url('posts/loadMoreData'); ?>',
                data:'id='+lastID,
                beforeSend:function(){
                    $('.load-more').show();
                },
                success:function(html){
                    $('.load-more').remove();
                    $('#postList').append(html);
                }
            });
        }
    });
});
</script>

The posts data that passed from the Posts controller are displayed in a list.

<div id="postList">
    <?php if(!empty($posts)){ foreach($posts as $post){ ?>
        <div class="list-item">
            <h2><?php echo $post['title']; ?></h2>
            <p><?php echo $post['content']; ?></p>
        </div>
    <?php } ?>
        <div class="load-more" lastID="<?php echo $post['id']; ?>" style="display: none;">
            <img src="<?php echo base_url('assets/images/loading.gif'); ?>"/> Loading more posts...
        </div>
    <?php }else{ ?>
        <p>Post(s) not available.</p>
    <?php } ?>
</div>

load-more-data.php:
This view appears while the user scrolling page down. It requested by the Ajax and loaded by the loadMoreData() method of Posts controller.

<div id="postList">
    <?php if(!empty($posts)){ foreach($posts as $post){ ?>
        <div class="list-item">
            <h2><?php echo $post['title']; ?></h2>
            <p><?php echo $post['content']; ?></p>
        </div>
    <?php } ?>
    <?php if($postNum > $postLimit){ ?>
        <div class="load-more" lastID="<?php echo $post['id']; ?>" style="display: none;">
            <img src="<?php echo base_url('assets/images/loading.gif'); ?>"/> Loading more posts...
        </div>
    <?php }else{ ?>
        <div class="load-more" lastID="0">
            That's All!
        </div>
    <?php } ?>    
<?php }else{ ?>    
    <div class="load-more" lastID="0">
            That's All!
    </div>    
<?php } ?>

Load Data on Page Scroll from MySQL Database using jQuery Ajax PHP

Conclusion

If you want to allow users to load more data without page refresh and provide a user-friendly interface for pagination in the CodeIgniter application, the Ajax infinite scroll definitely a better option. Load data on scroll can be easily integrated with jQuery and Ajax. Alternatively, Ajax pagination can be used to implement pagination without page refresh in CodeIgniter.