Creating a Web App From Scratch Using Python Flask and MySQL: Part 5

In the previous part of this series, we saw how to implement the Edit and Delete wish functionality for our Bucket List Application. In this part we'll implement the paging functionality for our user home list.

Getting Started

Let's start by cloning the previous part of the tutorial from GitHub.

git clone https://github.com/jay3dec/PythonFlaskMySQLApp_Part4.git

Once the source code has been cloned, navigate to the project directory and start the web server. 

cd PythonFlaskMySQLApp_Part4
python app.py

Point your browser to https://localhost:5002/ and you should have the application running.

Implementing Pagination

As the list of wishes on the user home page increases, it gets scrolled down the page. So it's important to implement pagination. We'll be limiting the number of items shown on a page to a certain number. 

Modify the Get Wish Procedure

We'll start by modifying the sp_GetWishByUser procedure to return results based on a limit and offset value. This time we'll be creating our stored procedure statement dynamically to return the result set based on the limit and offset value. Here is the modified sp_GetWishByUser MySQL stored procedure.

USE `BucketList`;
DROP procedure IF EXISTS `sp_GetWishByUser`;

DELIMITER $$
USE `BucketList`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetWishByUser`(
IN p_user_id bigint,
IN p_limit int,
IN p_offset int
)
BEGIN
    SET @t1 = CONCAT( 'select * from tbl_wish where wish_user_id = ', p_user_id, ' order by wish_date desc limit ',p_limit,' offset ',p_offset);
PREPARE stmt FROM @t1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt1;
END$$

DELIMITER ;

As seen in the above stored procedure, we created our dynamic SQL query and executed it to get the wish list based on the offset and limit parameters.

Adding Pagination to the UI

First, let's define a few default settings. In app.py add a variable for page limit.

# Default setting
pageLimit = 2

Make the getWish python method accept POST requests.

@app.route('/getWish',methods=['POST'])

Read the offset and limit inside the getWish method and pass it on while calling the MySQL stored procedure sp_GetWishByUser. 

 _limit = pageLimit
 _offset = request.form['offset']


con = mysql.connect()
cursor = con.cursor()
cursor.callproc('sp_GetWishByUser',(_user,_limit,_offset))
wishes = cursor.fetchall()

Modify the GetWishes JavaScript function in userHome.html to make it a POST request and pass the offset value.

function GetWishes() {
    $.ajax({
        url: '/getWish',
        type: 'POST',
        data: {
            offset: 0
        },
        success: function(res) {

            var wishObj = JSON.parse(res);
            $('#ulist').empty();
            $('#listTemplate').tmpl(wishObj).appendTo('#ulist');

        },
        error: function(error) {
            console.log(error);
        }
    });
}

Save all the changes and restart the server. Sign in using a valid email address and password and you should have only two records displayed on the screen.

So the database portion is working well. Next, we need to add the pagination UI to the user home page, which will enable the user to navigate across the data.

We'll use the Bootstrap pagination component. Open up userHome.html and add the following HTML code after the #ulist UL. 

<nav>
    <ul class="pagination">
        <li>
            <a href="#" aria-label="Previous">
                <span aria-hidden="true">&laquo;</span>
            </a>
        </li>
        <li><a href="#">1</a>
        </li>
        <li><a href="#">2</a>
        </li>
        <li><a href="#">3</a>
        </li>
        <li><a href="#">4</a>
        </li>
        <li><a href="#">5</a>
        </li>
        <li>
            <a href="#" aria-label="Next">
                <span aria-hidden="true">&raquo;</span>
            </a>
        </li>
    </ul>
</nav>

Save the changes and restart the server. After successfully signing in, you should be able to see the pagination under the wish list.

Making Pagination Dynamic

The above pagination is how our pagination will look. But to make it functional, we need to create our pagination dynamically based on the number of records in the database.

To create our pagination, we'll need the total number of records available in the database. So let's modify the MySQL stored procedure sp_GetWishByUser to return the total number of records available as an out parameter.

USE `BucketList`;
DROP procedure IF EXISTS `sp_GetWishByUser`;

DELIMITER $$
USE `BucketList`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetWishByUser`(
IN p_user_id bigint,
IN p_limit int,
IN p_offset int,
out p_total bigint
)
BEGIN
    
select count(*) into p_total from tbl_wish where wish_user_id = p_user_id;

SET @t1 = CONCAT( 'select * from tbl_wish where wish_user_id = ', p_user_id, ' order by wish_date desc limit ',p_limit,' offset ',p_offset);
PREPARE stmt FROM @t1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

As seen in the above modified stored procedure, we added a new output parameter called p_total and selected the total count of the wishes based on the user id. 

Also modify the getWish python method to pass an output parameter.

 _limit = pageLimit
 _offset = request.form['offset']
 _total_records = 0


con = mysql.connect()
cursor = con.cursor()
cursor.callproc('sp_GetWishByUser',(_user,_limit,_offset,_total_records))
wishes = cursor.fetchall()

cursor.close()

cursor = con.cursor()
cursor.execute('SELECT @_sp_GetWishByUser_3');

outParam = cursor.fetchall()

As you can see in the above code, once we've called the stored procedure we close the cursor and open a new cursor to select the returned out parameter.

Earlier, we were returning a list of wishes from the Python method. Now, we also need to include the total records count in the returned JSON. So we'll make the wish list dictionary into another list and then add the wish list and record count to the main list. Here is the modified code of the getWish python method.

response = []
wishes_dict = []

for wish in wishes:
    wish_dict = {
        'Id': wish[0],
        'Title': wish[1],
        'Description': wish[2],
        'Date': wish[4]}
    wishes_dict.append(wish_dict)
    
response.append(wishes_dict)
response.append({'total':outParam[0][0]}) 

return json.dumps(response)

In the GetWishes JavaScript function, inside the success callback add a console log.

console.log(res);

Save all the above changes and restart the server. Sign in using a valid email address and password and when on the user home page, check the browser console. You should be able to see a response similar to the one shown below:

[
    [{
        "Date": "Sun, 15 Feb 2015 15:10:45 GMT",
        "Description": "wwe",
        "Id": 5,
        "Title": "wwe"
    }, {
        "Date": "Sat, 24 Jan 2015 00:13:50 GMT",
        "Description": "Travel to Spain",
        "Id": 4,
        "Title": "Spain"
    }], {
        "total": 5
    }
]

Using the total count received from the response, we can get the total number of pages. 

var total = wishObj[1]['total'];
var pageCount = total/itemsPerPage;

Dividing the total items count from itemsPerPage count gives us the number of pages required. But this holds true only when the total is a multiple of itemsPerPage. If that's not the case, we'll have to check for that and handle the page count accordingly.

var pageRem = total%itemsPerPage;
if(pageRem !=0 ){
pageCount = Math.floor(pageCount)+1;
}

So that will give us the correct page count.

Now since we have the total number of pages, we'll create the pagination HTML dynamically. Remove the LI element from the pagination HTML we added earlier.

<nav>
    <ul class="pagination">
        // li we'll create dynamically
    </ul>
</nav>

In the GetWishes success callback, let's create the previous link dynamically using jQuery.

var prevLink = $('<li/>').append($('<a/>').attr({
        'href': '#'
    }, {
        'aria-label': 'Previous'
    })
    .append($('<span/>').attr('aria-hidden', 'true').html('&laquo;')));

$('.pagination').append(prevLink);

In the above code, we just created the previous button link and appended it to the pagination UL.

Save the above changes and restart the server. On successful sign-in you should be able to see the previous link under the list.

Similarly, let's add the pages in the pagination based on the page count.

for (var i = 0; i < pageCount; i++) {
    var page = $('<li/>').append($('<a/>').attr('href', '#').text(i + 1));
    $('.pagination').append(page);
}

Let's also add the Next link after the pages link have been added.

var nextLink = $('<li/>').append($('<a/>').attr({
        'href': '#'
    }, {
        'aria-label': 'Next'
    })
    .append($('<span/>').attr('aria-hidden', 'true').html('&raquo;')));

$('.pagination').append(nextLink);

Save the changes and restart the server. Sign in using a valid email address and password, and once on the user home page you should be able to see the pagination.

Attaching a Click Event to a Page Number

Now comes the main logic that will make our pagination functional. What we're going to do is attach a click event call on each page index to call the GetWishes JavaScript function. Let's first attach a click event to the anchor element displaying the page number.

for (var i = 0; i < pageCount; i++) {

    var aPage = $('<a/>').attr('href', '#').text(i + 1);
  
    $(aPage).click(function() {
        
    });
  
    var page = $('<li/>').append(aPage);
    $('.pagination').append(page);

}

So we just attached an onclick event to the page anchor. On each click we'll call the GetWishes function and pass the offset. So declare the offset outside the for loop.

var offset = 0;

Call the GetWishes function inside the click event call.

GetWishes(offset);

Also increment the offset based on the number of records shown.

offset = offset + 2;

But each time the GetWishes function is called, the value of offset will always be the last one set. So we'll make use of JavaScript Closures to pass the correct offset to the GetWishes function.

var offset = 0;

for (var i = 0; i < pageCount; i++) {

    var aPage = $('<a/>').attr('href', '#').text(i + 1);
  
    $(aPage).click(function(offset) {
        return function() {
            GetWishes(offset);
        }
    }(offset));
  
    var page = $('<li/>').append(aPage);
    $('.pagination').append(page);
    offset = offset + itemsPerPage;

}

Save all the above changes and restart the server. Sign in using valid credentials and once on the user home page, try clicking the pages in the pagination UL. 

Next, we'll implement the previous and next page links. It may seem a bit complicated, so let me explain it a bit before we start with the implementation. 

We'll be displaying five pages at a time. Using the next and previous link the user can navigate to the next five and previous five pages respectively. We'll store the values of the start page and end page and keep updating both on the next and previous button click. So let's start by adding two hidden fields to the userHome.html page.

<input type="hidden" id="hdnStart" value="1" />
<input type="hidden" id="hdnEnd" value="5"/>

In the GetWishes success callback, after we have emptied the .pagination UL, add the following line of code to get the latest start page and end page.

$('.pagination').empty();

var pageStart = $('#hdnStart').val();
var pageEnd = $('#hdnEnd').val();

No previous button link will be shown when displaying pages 1 to 5. If the pages displayed are greater than 5 then we'll display the previous button link.

if (pageStart > 5) {
    var aPrev = $('<a/>').attr({
            'href': '#'
        }, {
            'aria-label': 'Previous'
        })
        .append($('<span/>').attr('aria-hidden', 'true').html('&laquo;'));

    $(aPrev).click(function() {
        // Previous button logic
    });

    var prevLink = $('<li/>').append(aPrev);
    $('.pagination').append(prevLink);
}

When the user clicks the previous button, we'll reset the hdnStart and hdnEnd values and call the GetWishes JavaScript function. 

$(aPrev).click(function() {
    $('#hdnStart').val(Number(pageStart) - 5);
    $('#hdnEnd').val(Number(pageStart) - 5 + 4);
    GetWishes(Number(pageStart) - 5);
});

Next, based on the start page and the end page we'll loop and create the page links and append the .pagination UL.

for (var i = Number(pageStart); i <= Number(pageEnd); i++) {

    if (i > pageCount) {
        break;
    }


    var aPage = $('<a/>').attr('href', '#').text(i);
    
    // Attach the page click event
    $(aPage).click(function(i) {
        return function() {
            GetWishes(i);
        }
    }(i));
    
    var page = $('<li/>').append(aPage);

    // Attach the active page class
    if ((_page) == i) {
        $(page).attr('class', 'active');
    }

    $('.pagination').append(page);


}

By comparing the total page count and the page start value, we'll decide the display of the next button link.

if ((Number(pageStart) + 5) <= pageCount) {
    var nextLink = $('<li/>').append($('<a/>').attr({
            'href': '#'
        }, {
            'aria-label': 'Next'
        })
        .append($('<span/>').attr('aria-hidden', 'true').html('&raquo;').click(function() {
            $('#hdnStart').val(Number(pageStart) + 5);
            $('#hdnEnd').val(Number(pageStart) + 5 + 4);
            GetWishes(Number(pageStart) + 5);

        })));
    $('.pagination').append(nextLink);
}

As seen in the above code, on the next button click we are resetting the hdnStart and hdnEnd button values and calling the GetWishes JavaScript function.

So here is the final GetWishes JavaScript function. 

function GetWishes(_page) {

    var _offset = (_page - 1) * 2;
  
    $.ajax({
        url: '/getWish',
        type: 'POST',
        data: {
            offset: _offset
        },
        success: function(res) {

            var itemsPerPage = 2;

            var wishObj = JSON.parse(res);

            $('#ulist').empty();
            $('#listTemplate').tmpl(wishObj[0]).appendTo('#ulist');

            var total = wishObj[1]['total'];
            var pageCount = total / itemsPerPage;
            var pageRem = total % itemsPerPage;
            if (pageRem != 0) {
                pageCount = Math.floor(pageCount) + 1;
            }


            $('.pagination').empty();

            var pageStart = $('#hdnStart').val();
            var pageEnd = $('#hdnEnd').val();




            if (pageStart > 5) {
                var aPrev = $('<a/>').attr({
                        'href': '#'
                    }, {
                        'aria-label': 'Previous'
                    })
                    .append($('<span/>').attr('aria-hidden', 'true').html('&laquo;'));

                $(aPrev).click(function() {
                    $('#hdnStart').val(Number(pageStart) - 5);
                    $('#hdnEnd').val(Number(pageStart) - 5 + 4);
                    GetWishes(Number(pageStart) - 5);
                });

                var prevLink = $('<li/>').append(aPrev);
                $('.pagination').append(prevLink);
            }



            for (var i = Number(pageStart); i <= Number(pageEnd); i++) {

                if (i > pageCount) {
                    break;
                }


                var aPage = $('<a/>').attr('href', '#').text(i);

                $(aPage).click(function(i) {
                    return function() {
                        GetWishes(i);
                    }
                }(i));
                var page = $('<li/>').append(aPage);

                if ((_page) == i) {
                    $(page).attr('class', 'active');
                }

                $('.pagination').append(page);


            }
            if ((Number(pageStart) + 5) <= pageCount) {
                var nextLink = $('<li/>').append($('<a/>').attr({
                        'href': '#'
                    }, {
                        'aria-label': 'Next'
                    })
                    .append($('<span/>').attr('aria-hidden', 'true').html('&raquo;').click(function() {
                        $('#hdnStart').val(Number(pageStart) + 5);
                        $('#hdnEnd').val(Number(pageStart) + 5 + 4);
                        GetWishes(Number(pageStart) + 5);

                    })));
                $('.pagination').append(nextLink);
            }




        },
        error: function(error) {
            console.log(error);
        }
    });
}

Save all the above changes and restart the server. Sign in using a valid email address and password. You should be able to see the fully functional pagination for the user wish list.

Conclusion

In this part of series, we implemented the pagination functionality for the wish list on the user home page. We saw how to retrieve data using a MySQL stored procedure and create pagination using that data, jQuery and Bootstrap.

In the next part of this tutorial series, we'll implement the file upload functionality into our application.

Source code from this tutorial is available on GitHub.

Do let us know your thoughts in the comments below!