nodeJS and MySQL implement pagination data and reverse order data

When you are working on a project, you will definitely encounter list data. If you display it all at once in the front desk, the speed will definitely be very slow. Then we can display it in pages, for example, 100 pieces of data, one page per 10 pieces, and one page is loaded when needed, so the speed will definitely get faster.
So here I will introduce to you how to implement paging using mysql in the nodejs environment.

I won't go into details about the necessary configurations above. Here we mainly talk about the configuration of the address pool.

// Database informationvar connection = ({
 host   : 'localhost',
 port:3306,
 user   : 'root',
 password : '',
 database : 'url',
 multipleStatements: true //Be sure to add this here.  Don't worry about what's the use, you must add it.});

Because I use the koa framework, I use async and await. Ignore this here, the following code is important.

// Get url('/csdnurl', async (ctx, next) =&gt; {
  var start = ( - 1) * 10;
  var sql = 'SELECT COUNT(*) FROM csdnurl ORDER BY id DESC; SELECT * FROM csdnurl ORDER BY id DESC limit ' + start + ',10';
  let results = await query2(sql);
   = results
});

const query2 = function (sql) {
  return new Promise((resolve, reject) =&gt; {
    (sql, function (error, results) {
      if (error) {
        reject(error);
      } else {
         var allCount = results[0][0]['COUNT(*)'];
         var allPage = parseInt(allCount) / 10;
         var pageStr = ();
         if (('.') &gt; 0) {
           allPage = parseInt(('.')[0]) + 1;
           (allPage)
         }
         var List = results[1];
        resolve(List)
      }
    });
  })

1. This is the number of pages sent from the front desk.

2. You can see that there are two SQL statements, haha, this is the key. This is the reason for configuring multipleStatements: true in the address pool. If you do not add, an error will be reported.

3. ORDER BY id DESC This is the meaning of reverse order (according to reverse order according to id).

4. Then I used await asynchronous operation to encapsulate a function in order to get the results value. Of course, you don’t have to consider this when you use Express. Why is this happening? as follows:

Regarding the problem of using koa routing and mysql module, the value cannot be obtained

var Koa = require('koa');
var Router = require('koa-router' );
var bodyParser = require('koa-bodyparser');
var mysql = require('mysql');
var app = new Koa();
var router = new Router();
(bodyParser());

var connection = ({
 host   : 'localhost',
 port:3306,
 user   : 'root',
 password : 'xxx',
 database : 'url'
});
();


//The first type of packaging is generally used to not pass multiple parametersconst query = function (sql) {
  return new Promise((resolve, reject) =&gt; {
    (sql, function (error, results) {
      if(error){
        reject(error);
      }else{
        resolve(results)
      }
    });
  })
}
//The second type of packaging is generally used to pass multiple parametersconst query1 = function (userStr,name,passwd,token1) {
  return new Promise((resolve, reject) =&gt; {
    (userStr, function (error, result) {
      if(error){
        reject(error);
      }else{
        if ( &gt; 0) {
          json['message'] = 'The user already exists';
          json['resultCode']= 1;
        } else {
          json['message'] = 'Registered successfully';
          json['token'] = token1;
          json['resultCode'] = 200;
          var insertStr = `insert into login (username, password,token) values ("${name}", "${passwd}","${token1}")`;
          (insertStr)
          (insertStr, function (err, res) {
            if (err) throw err;
          })
        }
        resolve(json)
      }
    });
  })
}


// Use the first type of packaging( '/csdnurl', async(ctx, next) =&gt; {
 var sql = "select * from csdnurl";
  let results = await query(sql);
  =results
});
// Use the first type of packaging('/login', async (ctx, next) =&gt; {
  var name = ;
  var passwd = ;
  var userStr = `select username,password,token from login where username="${name}" and password="${passwd}"`;
  let results = await query(userStr);
   = results
});

// Use the second type of packaging('/register', async (ctx, next) =&gt; {
  var name = ;
  var passwd = ;
  var token1 = ({
    username: name
  }, secretkey, {
    expiresIn: 60 * 8
  });
  var userStr = `select * from login where username="${name}"`;
  let results = await query1(userStr,name,passwd,token1);
   = json
});

5. allPage is the number of all pages. Because it may encounter decimals, round down and add one.

6. Resolve(List) Return to the foreground data

This is the article about nodeJS and MySQL implementing pagination data and reverse order data. For more related nodeJS MySQL paging and reverse order content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!