CODEWITHSUNDEEP
javascriptmysqlnode.jsexpressejs
ArunPratap
ArunPratap

Reputation: 5020

Server side pagination in node js using ejs Template and MYSQL

As I have to render large data and when I am doing client-side pagination then it's taking time and I know Server side pagination is best for

But I don't have any idea how to do Server Side pagination using NODE.JS EJS and MYSQL here is my Routes and.EJS

Routes

app.get('/',function(req,res,next){
    req.getConnection(function(error, conn) {
        let sql = `SELECT *FROM studiestable
                   WHERE ReceivingDate >= ( CURDATE() - INTERVAL 35 DAY )       
                   ORDER BY  Bckup DESC,
                   ReceivingDate DESC`;

        conn.query(sql,function(err,rows,fields){
            if (err) {
                req.flash('error', err)
                res.render('patient/dashboard', {
                    title: 'Dashboard', 
                    data: ''
                })
            } else {
                res.render('patient/dashboard', {
                    title: 'Dashboard', 
                    data: rows
                })
            }
        })
    })
})

EJS

<table id="pattTab" class="table small">
            <tr style="background-color: rgb(122, 135, 160);color: white"> 
                <th>ID</th> 
                <th>Patient Name</th>               
                <th>Age</th>
                <th>Modality</th>                   
                <th>Images</th>                         
            </tr>
            <% if (data) { %>
                <% data.forEach(function(Patient){ %>
                    <tr>      
                        <td><%= Patient.PatientID %></td>
                        <td><%= Patient.PatientName %></td>                                   
                        <td><%= Patient.Age %></td>
                        <td><%= Patient.Modality %></td>                        
                        <td><%= Patient.Images %></td>                                                     
                    </tr>
                <% }) %>
                <% } %>        
        </table>

Upvotes: 2

Views: 6087

Answers (2)

user6184932
user6184932

Reputation:

Pagination in NodeJS, EJS, and MongoDB, using Mongoose ODM

If you are trying to do Pagination the do it the following way. Your Node.JS file

const ITEMS_PER_PAGE = 4;

exports.getIndex = (req, res, next) => {

    // start constants
    const page = +req.query.page || 1; // pagination
    let totalItems; // pagination
    // end constants

    Hotel.find()
    .countDocuments()
    .then(numberOfProducts => {
        totalItems = numberOfProducts;
        return Hotel.find()
                    .skip((page-1) * ITEMS_PER_PAGE)
                    .limit(ITEMS_PER_PAGE);
    }).then(hotels => {
        res.render('pages/hotel', {
            hotels: hotels,
            currentPage: page,
            hasNextPage: (ITEMS_PER_PAGE * page) < totalItems,
            hasPreviousPage: page > 1,
            nextPage: page + 1,
            previousPage: page - 1,
            lastPage: Math.ceil(totalItems / ITEMS_PER_PAGE)
        });
    }).catch(err => {
        console.log(err);
    });
}

And then write your ejs in the following manner:

<body>
    <div class="jumbotron">
        <div class="container">
            <h1 class="display-4">Hotels</h1>
        </div>
    </div>
    <div class="container">
        <%if (hotels.length > 0) {%>
        <div class="row">
            <%for (let hotel of hotels) {%>
            <div class="col-sm-4">
                <div class="card mt-4" style="width: 18rem;">
                    <div class="card-body">
                        <h5 class="card-title mt-2"><%=hotel.title%></h5>
                        <p class="card-text"><%=hotel.description%></p>
                    </div>
                </div>
            </div>
            <%}%>
            <div class="container mt-4">
                <nav aria-label="Page navigation example">
                    <ul class="pagination justify-content-center">
                        <% if (hasPreviousPage) { %>
                        <li class="page-item">
                            <a class="page-link" href="?page=1">First</a>
                        </li>
                        <li class="page-item">
                            <a class="page-link" href="?page=<%= previousPage %>"><%= previousPage %></a>
                        </li>
                        <% } %>
                        <li class="page-item active">
                            <a class="page-link" href="?page=<%= currentPage %>"><%= currentPage %></a>
                        </li>
                        <% if (hasNextPage) { %>
                        <li class="page-item">
                            <a class="page-link" href="?page=<%= nextPage %>"><%= nextPage %></a>
                        </li>
                        <li class="page-item">
                            <a class="page-link" href="?page=<%= lastPage %>">Last</a>
                        </li>
                        <% } %>
                    </ul>
                </nav>
            </div>
        </div>
        <%} else {%>
        <div class="text-center">
            <h5>No products found.</h5>
            <div class="mt-4">
                <a href="/" class="btn btn-outline-primary">Go Home</a>
            </div>
        </div>
        <%}%>
    </div>
</body>

Have used bootstrap for CSS.

This is how you can do it. It will fetch a few items from database and paginate the UI.

Upvotes: 3

ionizer
ionizer

Reputation: 1721

You might want to try using SQL's LIMIT and OFFSET for pagination. So, it'll be something like this for your Node's SQL string:

`SELECT * FROM studiestable
    WHERE ReceivingDate >= ( CURDATE() - INTERVAL 35 DAY )       
    ORDER BY Bckup DESC,
    ReceivingDate DESC`
    LIMIT 30 OFFSET ${req.query.page*30};`

I know, passing such variable unsanitized is very unsecure, but just to give a rough idea.

So what happens in this one is, you receive a query string called page (example url: api.example.com/?page=0). We'll assume our page numbers start from 0. So, when page is equal to 0, it returns first 30 rows (0 offset). Then when page is equal to 1 (30 offset), it returns the next 30 rows. And as page increases, it returns the next 30 set of data depending on your page.

Upvotes: 1

Related Questions