Express+MySQL implements the demo of login and registration

MySQL5.7.20

demo preparation

Install mysql, add system environment variable to start service in cmd after installation: net start mysql57, if MySQL 8.0 is installed, mysql80 is the default service name, test whether MySQL installation is successful: mysql -V
Node is installed when installing express. NPM is carried by node itself

demo start
  1. First, create a new folder with the name in English as much as possible
  2. Quick project creation: npm init -y
  3. Create a new app.js file, which is not the main writing place of Node server
  4. Start service first
const express = require('express');
const app = express()
app.get('/',function(req,res){
	res.send('Node Service started successfully') // Return data to front end
})
app.listen(3001)
  1. Enter localhost:3001 on the browser, as shown in the following figure:
  2. Create a new views folder to store the display page, for example: index.html
  3. Establish mysql connection
 const mysql = require('msyql')
 var connsql = mysql.createConnection({
	host:'127.0.0.1', 
	port:'3306', 
	user:'root', 
	password:'root123',
	database:'db' // data base
})
connsql.connect() // Open database connection
  1. Because the address is displayed with fake data, we need to display HTML pages with forms here
app.engine('html',require('express-art-template')) // Rendering html engine
app.get('/',(req,res){
	res.render('index.html') // Default login page
})
app.get('/',(req,res){
	res.render('register.html') // Registration page! [insert picture description here] (https://img-blog.csdnimg.cn/202002011933500.png)
})
  1. index.html page
<body>
    <h1>Login page!</h1>
        <div><span>Account number:</span><input type="text" name="user"></div>
        <div><span>Password:</span><input type="password" name="pwd"></div>
        <div>
         	<input type="submit" value="Sign in" id="btn">
        	<a href="/register"><button>register</button></a>
        	<span class="spa1"><span class="asd"></span></span>
        </div> 
</body>


10. The registration page is almost the same as the login page

11. login

app.use('/login', (req, res)=> {
    var login = {
        "user": req.body.user, // Get the user value in input
        "pwd": req.body.pwd // Get pwd value in input
    }

	// Define query sql statement
    var loginsql = "select username,password from db_table where username='" + login.user + "'and password='" + login.pwd + "'" 
    
    // Execution query
    connsql.query(loginsql,  (err, result)=> {
        if (err) {
            console.log('err message:', err)
            return
        }
        if (result == '') {
            console.log('Wrong user name or password!')
            res.json({ code: -1, msg: 'Wrong user name or password!' })

        } else {
            console.log('User name and password match succeeded!')
            res.json({ code: 1, msg: 'Login successfully' })

        }
    })
})
  1. register
app.use('/regs', (req, res) => {
    var regs = {
        "user": req.body.user,
        "pwd": req.body.pwd
    }
    // Insert sql statement
    var regssql = "insert into db_table(username,password) values('" + regs.user + "','" + regs.pwd + "')";
    // Query sql statement
    var selsql = "select username from db_table where username='" + regs.user + "'"
	
	//Functions to insert a user name and password into a data table
    function regfun() {
        connsql.query(regssql,  (err, result)=> {
            if (err) {
                console.log(err);
                return 
            }
            res.json({code:1,msg:"login was successful"})
            console.log('login was successful')
        })
    }
	// First, check whether the user name exists, and then insert the user name and password when deciding to register
    connsql.query(selsql,  (err, result)=>{
        if (err) {
            console.log(err)
            return
        }
        if (result == '') {
            regfun() // Execute insert function
        } else { 
            res.json({code:-1,msg:"Registration failed, user name already exists"})
            console.log(regs.user + 'User name already exists')
       }

    })
})
  1. ajax request for login
 $(function(){
     $('#btn').click(function(){
         var user=$('input[type=text]')
         var pwd=$('input[type=password]')
         var logindata={user:user.val(),pwd:pwd.val()}
         if(user.val().length==0&&pwd.val().length==0){
            $('.spa1').text('User name and password cannot be empty')
         }else{
             $.ajax({
                 type:'post',
                 url:'/login',
                 dataType:'json',
                 data:logindata,
                 success:function(data){
                     if(data.code>1){
                        $('.asd').text(data.msg)
                     }
                        $('.asd').text(data.msg)
                 },
                 error:function(error){
                     console.log(error)
                 }
             })
         }
     })

 })
</script>
  1. Registered ajax requests
<script>
    $(function () { 
        $('#btn').click(function () {
            var user = $('input[type=text]')
            var pwd1 = $('input[name=pwd1]')
            var pwd2 = $('input[name=pwd2]')
            var msg=$('.msg')
            var datas = { user: user.val(), pwd: pwd1.val() }

            if (user.val().length == 0 || pwd1.val().length == 0 || pwd2.val().length == 0) {
                msg.text('Please enter user name and password')
                return false
            } else if (pwd1.val() !== pwd2.val()) {
                msg.text('Two password entries are inconsistent')
                return false
            } else {
                $.ajax({
                    type: 'POST',
                    url: '/regs',
                    dataType: 'json',
                    data: datas,
                    success: function (data) {
                        if(data.code>0){
                            $(".asd").text(data.msg)
                        }
                            $(".asd").text(data.msg)
                    },
                    error: function (error) {
                        console.log(error)
                    }
                })
            }
        })
        $('button').click(function(){
            location.href='/'
        })
    })
</script>
  1. At this point, the login and registration are finished. Because it's Xiaobai, there may be some problems in it. Welcome to exchange and learn. Finally, attach the complete code
  2. app.js
const express = require('express')
const mysql = require('mysql')
const app = express()
var bodyParser = require('body-parser')

var connsql = mysql.createConnection({
    host: '127.0.0.1',
    port: '3306',
    user: 'root',
    password: '2232723904',
    database: 'db'

})
connsql.connect()

app.engine('html', require('express-art-template'))
app.use('/public/', express.static('./public/'))

app.use(bodyParser.urlencoded({ extended: false }))
app.use(bodyParser.json())

app.get('/', (req, res)=> {
    // res.sendFile( __dirname +"/views/"+ "index.html" );
    res.render('index.html')
})

app.get('/register', (req, res)=> {
    // res.sendFile( __dirname +"/views/"+ "register.html" );
    res.render('register.html')
})

app.use('/login', (req, res)=> {
    var login = {
        "user": req.body.user,
        "pwd": req.body.pwd
    }

    var loginsql = "select username,password from db_table where username='" + login.user + "'and password='" + login.pwd + "'"
    connsql.query(loginsql,  (err, result)=> {
        if (err) {
            console.log('err message:', err)
            return
        }
        if (result == '') {
            console.log('Wrong user name or password!')
            res.json({ code: -1, msg: 'Wrong user name or password!' })

        } else {
            console.log('User name and password match succeeded!')
            res.json({ code: 1, msg: 'Login successfully' })

        }
    })
})

app.use('/regs', (req, res) => {
    var regs = {
        "user": req.body.user,
        "pwd": req.body.pwd
    }
    var regssql = "insert into db_table(username,password) values('" + regs.user + "','" + regs.pwd + "')";
    var selsql = "select username from db_table where username='" + regs.user + "'"
    function regfun() {
        connsql.query(regssql,  (err, result)=> {
            if (err) {
                console.log(err);
                return 
            }
            res.json({code:1,msg:"login was successful"})
            console.log('login was successful')
        })
    }
    connsql.query(selsql,  (err, result)=>{
        if (err) {
            console.log(err)
            return
        }
        if (result == '') {
            regfun() 
        } else { 
            res.json({code:-1,msg:"Registration failed, user name already exists"})
            console.log(regs.user + 'User name already exists')
       }

    })
})

app.listen(3000)

// connsql.end()


  1. index.html
<!DOCTYPE html>
<html lang="en">
<script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.min.js"></script>

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Login page</title>
    <style>
        .spa1{color: #ff160e;}
        .asd{color: #0bc52a;}
    </style>
</head>

<body>
    <h1>Login page</h1>
        <div><span>Account number:</span><input type="text" name="user"></div>
        <div><span>Password:</span><input type="password" name="pwd"></div>
        <div><input type="submit" value="Sign in" id="btn"><a href="/register"><button>register</button></a><span class="spa1"><span class="asd"></span></span></div>
</body>

</html>
<script>
 $(function(){
     $('#btn').click(function(){
         var user=$('input[type=text]')
         var pwd=$('input[type=password]')
         var logindata={user:user.val(),pwd:pwd.val()}
         if(user.val().length==0&&pwd.val().length==0){
            $('.spa1').text('User name and password cannot be empty')
         }else{
             $.ajax({
                 type:'post',
                 url:'/login',
                 dataType:'json',
                 data:logindata,
                 success:function(data){
                     if(data.code>1){
                        $('.asd').text(data.msg)
                     }
                        $('.asd').text(data.msg)
                 },
                 error:function(error){
                     console.log(error)
                 }
             })
         }
     })

 })
</script>
  1. register.html
<!DOCTYPE html>
<html lang="en">
<script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.min.js"></script>

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Registration page</title>
    <style>
        .msg{color: #ff160e;}
        .asd{color: #0bc52a;}
    </style>
</head>

<body>
    <!-- <form action="" method="post"> -->
        <h1>Registration page</h1>
        <div><span>Account number:</span><input type="text" name="user"></div>
        <div><span>Password:</span><input type="password" name="pwd1"></div>
        <div><span>Password:</span><input type="password" name="pwd2"></div>
        <div><input type="submit" value="register" id="btn"><button>Sign in</button><span class="msg"></span><span class="asd"></span></div>
    <!-- </form> -->
</body>

</html>
<script>
    $(function () { 
        $('#btn').click(function () {
            var user = $('input[type=text]')
            var pwd1 = $('input[name=pwd1]')
            var pwd2 = $('input[name=pwd2]')
            var msg=$('.msg')
            var datas = { user: user.val(), pwd: pwd1.val() }

            if (user.val().length == 0 || pwd1.val().length == 0 || pwd2.val().length == 0) {
                msg.text('Please enter user name and password')
                return false
            } else if (pwd1.val() !== pwd2.val()) {
                msg.text('Two password entries are inconsistent')
                return false
            } else {
                $.ajax({
                    type: 'POST',
                    url: '/regs',
                    dataType: 'json',
                    data: datas,
                    success: function (data) {
                        if(data.code>0){
                            $(".asd").text(data.msg)
                        }
                            $(".asd").text(data.msg)
                    },
                    error: function (error) {
                        console.log(error)
                    }
                })
            }
        })
        $('button').click(function(){
            location.href='/'
        })
    })
</script>
  1. ps: when installing a third-party package, you need to add - S or - D, for example: cnpm i express -S, which is saved in the configuration file package.json. It is convenient for others to download the package directly, saving time and effort.
  2. Successfully registered mysql data table
  3. Source address: https://github.com/dillonleader/login-demo
Published 2 original articles, praised 0, visited 79
Private letter follow

Tags: JSON MySQL JQuery Database

Posted on Sun, 02 Feb 2020 00:47:28 -0500 by fareasd