MongoDB AJAX Pagination with Search and Sort using PHP

I was recently working on a project that makes use of MongoDB as database, the client wanted to have a Paginated list of documents, Search box, Column Sorting feature and a Per Page Filter. There are over 5 million documents in their database which are basically generated tickets for their customers. Since I have an existing pagination which actually works like a charm, I thought i’d try to convert it to make use mongoDB’s query methods. I was successful in developing my client’s requests and now I just wanted to share to you guys how I did it.

The code

  1. Lets start off by making a directory in our local server, let’s call it mongodb. 
  2. I am currently using Windows as my local server and XAMPP as my web server. If you have not setup MongoDB yet and want to know how to set it up in Windows, I made a separate tutorial for that – see this link
  3. Inside our mongodb folder, create the following directories and files:
    • index.php
    • This file is basically where our pagination will be displayed.
    • Code::
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      <?php require_once('inc/config.php'); ?>
      <?php require_once('header.php'); ?>
       
      <div class="container">
          <form class = "post-list">
              <input type = "hidden" value = "" />
          </form>
         
          <article class="navbar-form navbar-left ml-b">
              <div class="form-group">
                  <label>Per Page: </label>
                  <select class="form-control post_max">
                      <option value="40">40</option>
                      <option value="80">80</option>
                      <option value="160">160</option>
                  </select>
                  <label>Search Keyword: </label>
                  <input type="text" class="form-control post_search_text" placeholder="Enter a keyword">
              </div>
              <input type = "submit" value = "Filter" class = "btn btn-primary post_search_submit" />
          </article>
         
          <br class = "clear" />
         
          <div class = "wave-box-wrapper">
              <div class = "wave-box"></div>
              <table class = "table table-striped table-post-list no-margin">
                  <thead>
                      <tr>
                          <th id = "name" class = "active"><a href = "#">Name</a></th>
                          <th id = "price"><a href = "#">Price</a></th>
                          <th id = "status"><a href = "#">Status</a></th>
                          <th id = "date"><a href = "#">Date</a></th>
                          <th id = "quantity"><a href = "#">Quantity</a></th>
                          <th>Action</th>
                      </tr>
                  </thead>
                  <tbody class = "pagination-container"></tbody>
              </table>
             
              <div class = "pagination-nav"></div>
          </div>
      </div>

      <?php require_once('footer.php'); ?>
    • header.php:
    • For this example I decided to use Bootstrap for a quick theme setup.
    • Code:
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      <!DOCTYPE html>
      <html lang="en">
      <head>
          <title>MongoDB AJAX Pagination with Search and Sort | by Carlo Fontanos</title>
         
          <meta charset="utf-8">
          <meta name="viewport" content="width=device-width, initial-scale=1">
         
          <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
          <link rel="stylesheet" href="//cdn.rawgit.com/Eonasdan/bootstrap-datetimepicker/e8bddc60e73c1ec2475f827be36e1957af72e2ea/build/css/bootstrap-datetimepicker.css">
          <link rel="stylesheet" href="css/styles.css">
      </head>
      <body class="override">
          <div class="container">
              <nav class="navbar navbar-default">
                  <div class="container-fluid">
                      <div class="navbar-header">
                          <a class="navbar-brand" href="index.php">MongoDB Example</a>
                      </div>
                  </div>
              </nav>
          </div>
    • footer.php:
    • Code:
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
          <footer class="m-t">
              <div class="container">
                  <div class="panel panel-default">
                      <div class="panel-body">
                          <span class="pull-right">Copyright &copy <a href="https://carlofontanos.com" target="_blank">www.carlofontanos.com</a>; <?php echo date('Y'); ?></span>
                      </div>
                  </div>
          </footer>
         
          <script src="//ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
          <script src="//maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
          <script src="js/app.js"></script>
      </body>
      </html>
    • /css
      • styles.css
      • Code:
        1
        2
        3
        4
        5
        6
        7
        /* Pagination Styles */
        .pagination-nav { margin: 30px 0 20px 0; }
        .pagination-nav ul {margin: 0; padding: 0;}
        .pagination-nav ul li {display: inline-block; margin: 3px; padding: 4px 8px; background: #FFF; color: black; }
        .pagination-nav ul li.active:hover {cursor: pointer; background: #367fa9; color: white; }
        .pagination-nav ul li.inactive {background: #CACACA;}
        .pagination-nav ul li.selected {background: #367fa9; color: white;}
    • /inc
      • config.php
      • Code:
        1
        2
        $connection = new MongoClient();
        $db = $connection->selectDB('my_database_name');

        You can change the string my_database_name to anything you like.

      • view.php

      • This is where all the logic happens. If you want to integrate this with your existing app, you will have to change the document field names to match your requirements. For this example I would assume you have a database with the following document fields:

        • name (string)
        • price (string)
        • status (string)
        • date (string)
        • quantity (string)
      • Code:
        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24
        25
        26
        27
        28
        29
        30
        31
        32
        33
        34
        35
        36
        37
        38
        39
        40
        41
        42
        43
        44
        45
        46
        47
        48
        49
        50
        51
        52
        53
        54
        55
        56
        57
        58
        59
        60
        61
        62
        63
        64
        65
        66
        67
        68
        69
        70
        71
        72
        73
        74
        75
        76
        77
        78
        79
        80
        81
        82
        83
        84
        85
        86
        87
        88
        89
        90
        91
        92
        93
        94
        95
        96
        97
        98
        99
        100
        101
        102
        103
        104
        105
        106
        107
        108
        109
        110
        111
        112
        113
        114
        115
        116
        117
        118
        119
        120
        121
        122
        123
        124
        125
        126
        127
        128
        129
        130
        131
        132
        133
        134
        135
        136
        137
        138
        139
        140
        141
        142
        143
        144
        145
        146
        require_once('config.php');

        $collection = $db->products;
        $pag_content = '';
        $pag_navigation = '';

        if( isset( $_POST['data']['page'] ) ){
           
            $page = $_POST['data']['page']; /* The page we are currently at */
            $name = $_POST['data']['th_name']; /* The name of the column name we want to sort */
            $sort = $_POST['data']['th_sort']; /* The order of our sort (DESC or ASC) */
            $max  = $_POST['data']['max']; /* Number of items to display per page */
            $cur_page = $page;
            $page -= 1;
            $per_page = $max ? $max : 40;
            $previous_btn = true;
            $next_btn = true;
            $first_btn = true;
            $last_btn = true;
            $start = $page * $per_page;
           
            $where_search = array();
           
            /* Check if there is a string inputted on the search box */
            if( ! empty( $_POST['data']['search']) ){
                /* If a string is inputted, include an additional query logic to our main query to filter the results */
                $filter = new MongoRegex('/' . $_POST['data']['search'] . '/i');
                $where_search = array(
                    '$or' => array(
                        array('name' => $filter),
                        array('price' => $filter)
                    )
                );
            }
           
            /* Retrieve all the posts */
            $all_items = $collection
                ->find( $where_search, array('_id', 'name', 'price', 'status', 'date', 'quantity') )
                ->limit( $per_page )
                ->skip( $start )
                ->sort( array(
                    $name => $sort == 'ASC' ? 1 : -1
                ));
           
            $count = $collection
                ->find($where_search)
                ->count();
               
            /* Check if our query returns anything. */
            if( $count ){
               
                /* Iterate thru each item */
                foreach( $all_items as $key => $item ){
                   
                    $item = (object) $item;
                    $status = $item->status == 1 ? 'Active' : 'Inactive';
                   
                    $pag_content .= '
                    <tr>
                        <td>'         . $item->name . '</td>
                        <td>$'         . $item->price . '</td>
                        <td>'         . $status . '</td>
                        <td>'         . date("F j, Y, g:i a", strtotime( $item->date ) ) . '</td>
                        <td>'         . $item->quantity . '</td>
                        <td>
                            <a href="#_" class="text-success"><span class="glyphicon glyphicon-pencil" title="Edit"></span></a> &nbsp; &nbsp;
                            <a href="#_" class="text-danger delete-product" item_id="'         . $item->_id . '"><span class="glyphicon glyphicon-remove" title="Delete"></span></a>
                        </td>
                    </tr>'        ;        
                }
               
            /* If the query returns nothing, we throw an error message */
            } else {
                $pag_content .= '<td colspan = "7" class = "bg-danger p-d">No results found.</td>';
               
            }

            $pag_content = $pag_content . "<br class = 'clear' />";
           
            $no_of_paginations = ceil($count / $per_page);

            if ($cur_page >= 7) {
                $start_loop = $cur_page - 3;
                if ($no_of_paginations > $cur_page + 3)
                    $end_loop = $cur_page + 3;
                else if ($cur_page <= $no_of_paginations && $cur_page > $no_of_paginations - 6) {
                    $start_loop = $no_of_paginations - 6;
                    $end_loop = $no_of_paginations;
                } else {
                    $end_loop = $no_of_paginations;
                }
            } else {
                $start_loop = 1;
                if ($no_of_paginations > 7)
                    $end_loop = 7;
                else
                    $end_loop = $no_of_paginations;
            }
             
            $pag_navigation .= "<ul>";

            if ($first_btn && $cur_page > 1) {
                $pag_navigation .= "<li p='1' class='active'>First</li>";
            } else if ($first_btn) {
                $pag_navigation .= "<li p='1' class='inactive'>First</li>";
            }

            if ($previous_btn && $cur_page > 1) {
                $pre = $cur_page - 1;
                $pag_navigation .= "<li p='$pre' class='active'>Previous</li>";
            } else if ($previous_btn) {
                $pag_navigation .= "<li class='inactive'>Previous</li>";
            }
            for ($i = $start_loop; $i <= $end_loop; $i++) {

                if ($cur_page == $i)
                    $pag_navigation .= "<li p='$i' class = 'selected' >{$i}</li>";
                else
                    $pag_navigation .= "<li p='$i' class='active'>{$i}</li>";
            }
           
            if ($next_btn && $cur_page < $no_of_paginations) {
                $nex = $cur_page + 1;
                $pag_navigation .= "<li p='$nex' class='active'>Next</li>";
            } else if ($next_btn) {
                $pag_navigation .= "<li class='inactive'>Next</li>";
            }

            if ($last_btn && $cur_page < $no_of_paginations) {
                $pag_navigation .= "<li p='$no_of_paginations' class='active'>Last</li>";
            } else if ($last_btn) {
                $pag_navigation .= "<li p='$no_of_paginations' class='inactive'>Last</li>";
            }

            $pag_navigation = $pag_navigation . "</ul>";   
        }


        $response = array(
            'content'       =>  $pag_content,
            'navigation'    =>  $pag_navigation,
        );

        echo json_encode( $response );

        exit();
    • /js
      • app.js
      • This app js class handles column sorting, searching, pagination clicks, and filter.
      • Code:
        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24
        25
        26
        27
        28
        29
        30
        31
        32
        33
        34
        35
        36
        37
        38
        39
        40
        41
        42
        43
        44
        45
        46
        47
        48
        49
        50
        51
        52
        53
        54
        55
        56
        57
        58
        59
        60
        61
        62
        63
        64
        65
        66
        67
        68
        69
        70
        71
        72
        73
        74
        75
        76
        77
        78
        79
        80
        81
        82
        83
        84
        85
        86
        87
        88
        89
        90
        91
        92
        93
        94
        95
        96
        97
        98
        99
        100
        101
        102
        103
        104
        105
        106
        107
        108
        109
        110
        111
        112
        113
        114
        115
        116
        117
        118
        119
        120
        121
        122
        123
        124
        125
        126
        127
        128
        129
        130
        131
        132
        133
        134
        135
        136
        137
        138
        139
        140
        141
        142
        143
        144
        145
        146
        147
        148
        /**
         * App Class
         *
         * @author      Carl Victor Fontanos
         * @author_url  www.carlofontanos.com
         *
         */

        /**
         * Setup a App namespace to prevent JS conflicts.
         */
        var app = {
               

            Posts: function() {
               
                /**
                 * This method contains the list of functions that needs to be loaded
                 * when the "Posts" object is instantiated.
                 *
                 */
                this.init = function() {
                    // this.loaded_posts_pagination();
                    this.get_items_pagination();
                }
               
                /**
                 * Load items pagination.
                 */
                this.get_items_pagination = function() {
                   
                    _this = this;
                   
                    /* Check if our hidden form input is not empty, meaning it's not the first time viewing the page. */
                    if($('form.post-list input').val()){
                        /* Submit hidden form input value to load previous page number */
                        data = JSON.parse($('form.post-list input').val());
                        _this.ajax_get_items_pagination(data.page, data.th_name, data.th_sort);
                    } else {
                        /* Load first page */
                        _this.ajax_get_items_pagination(1, 'name', 'ASC');
                    }
                   
                    var th_active = $('.table-post-list th.active');
                    var th_name = $(th_active).attr('id');
                    var th_sort = $(th_active).hasClass('DESC') ? 'DESC': 'ASC';
                               
                    /* Search */
                    $('body').on('click', '.post_search_submit', function(){
                        _this.ajax_get_items_pagination(1, th_name, th_sort);
                    });
                    /* Search when Enter Key is triggered */
                    $(".post_search_text").keyup(function (e) {
                        if (e.keyCode == 13) {
                            _this.ajax_get_items_pagination(1, th_name, th_sort);
                        }
                    });
                   
                    /* Pagination Clicks   */                  
                    $('body').on('click', '.pagination-nav li.active', function(){
                        var page = $(this).attr('p');
                        var current_sort = $(th_active).hasClass('DESC') ? 'DESC': 'ASC';
                        _this.ajax_get_items_pagination(page, th_name, current_sort);                
                    });

                    /* Sorting Clicks */
                    $('body').on('click', '.table-post-list th', function(e) {
                        e.preventDefault();                            
                        var th_name = $(this).attr('id');
                                                           
                        if(th_name){
                            /* Remove all TH tags with an "active" class */
                            if($('.table-post-list th').removeClass('active')) {
                                /* Set "active" class to the clicked TH tag */
                                $(this).addClass('active');
                            }
                            if(!$(this).hasClass('DESC')){
                                _this.ajax_get_items_pagination(1, th_name, 'DESC');
                                $(this).addClass('DESC');
                            } else {
                                _this.ajax_get_items_pagination(1, th_name, 'ASC');
                                $(this).removeClass('DESC');
                            }
                        }
                    });
                }
               
                /**
                 * AJAX items pagination.
                 */
                this.ajax_get_items_pagination = function(page, th_name, th_sort){
                   
                    if($(".pagination-container").length){
                        $(".pagination-container").html('<img src="img/loading.gif" class="ml-tb" />');
                       
                        var post_data = {
                            page: page,
                            search: $('.post_search_text').val(),
                            th_name: th_name,
                            th_sort: th_sort,
                            max: $('.post_max').val(),
                        };
                       
                        $('form.post-list input').val(JSON.stringify(post_data));
                       
                        var data = {
                            action: "demo_load_my_posts",
                            data: JSON.parse($('form.post-list input').val())
                        };
                       
                        $.ajax({
                            url: 'inc/view.php',
                            type: 'POST',
                            data: data,
                            success: function (response) {
                                response = JSON.parse(response);
                               
                                if($(".pagination-container").html(response.content)){
                                    $('.pagination-nav').html(response.navigation);
                                    $('.table-post-list th').each(function() {
                                        /* Append the button indicator */
                                        $(this).find('span.glyphicon').remove();    
                                        if($(this).hasClass('active')){
                                            if(JSON.parse($('form.post-list input').val()).th_sort == 'DESC'){
                                                $(this).append(' <span class="glyphicon glyphicon-chevron-down pull-right"></span>');
                                            } else {
                                                $(this).append(' <span class="glyphicon glyphicon-chevron-up pull-right"></span>');
                                            }
                                        }
                                    });
                                }
                            }
                        });
                    }
                }
            }
        }

        /**
         * When the document has been loaded...
         *
         */
        jQuery(document).ready( function () {
           
            posts = new app.Posts(); /* Instantiate the Posts Class */
            posts.init(); /* Load Posts class methods */
           
        });
  4. I can’t provide a demo link at the moment as I am only running my site in a shared hosting, but I do hope that you find this tutorial useful.

Challenge

Continue working on the Edit and Delete buttons.


Do you need help with a project? or have a new project in mind that you need help with?

Contact Me

100% Bootstrap Admin Template

  Download

Python E-commerce

  Download

Gallery Plugin for Advanced Custom Fields

  Download

Wp Trade

  Download

Ruby on Rails E-commerce

  Download