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.

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

Point your browser to http://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.

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.

Make the getWish python method accept POST requests.

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

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

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.

User Home with Limited records

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. 

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

Pagination in User Home Page

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.

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.

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.

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

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:

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

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.

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.

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

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.

Previous link in the Pagination

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

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

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.

Pagination in User Home Page

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.

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.

Call the GetWishes function inside the click event call.

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

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.

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.

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.

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.

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

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

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

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. 

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! 

Tags:

Comments

Related Articles