Pagination with Node.js and MySQL

Question

can someone help me to do a pagination? I am using node.js and mysql, and I have not found a material to help me, when I search I only find material for MongoDB. I am trying to make a page for a CRUD, but I can't find the way to do it. I have a little idea on the server side, but I have absolutely no idea how to implement it in the buttons on the client side. Thanks a lot! This is a fragment of my code where i consult all users to display them, this fragment is in a controller.

controller.list = function(req, res, next) {
    req.getConnection((err, conn) => {
        conn.query('SELECT * FROM usuarios', (err, usuario) => { 
            if(err) {
                res.json(err);
            }
            if(req.session.loggedin) {
                res.render('usuarios', {
                    data: usuario,
                    login: true,
                    nombre: req.session.nombre
                });
            }else{
                return res.redirect('/admin');
            }
        });
    });
};

see [pagination](http://mysql.rjweb.org/doc.php/pagination). — danblack, Apr 02 '21 at 01:20

score 0 · Answer 1 · answered Apr 02 '21 at 01:08

The LIMIT clause would be perfect for your use case.

From the MySQL documentation:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

Thanks Andy, but now, I have the limit of what it returns to me, but how can I do the iteration so that by pressing the "next" button I advance to the other 10 for example — Adrian A, Apr 02 '21 at 01:24
The example from the documentation got the 10 rows from 6 to 15, so if you want the next 10, you can change LIMIT 5,10 to LIMIT 15,10. The first value after LIMIT specifies the offset (tells the database where to start) and the second one specifies how many rows to return — Andy K, Apr 02 '21 at 03:43

Pagination with Node.js and MySQL

1 Answers1