时间戳

首页 > 解决方案 > 在哪里放置代码以显示从 MySQL 到 Handlebars 的数据？

mysql - 在哪里放置代码以显示从 MySQL 到 Handlebars 的数据？

问题描述

目标：

我的目标是自学如何使用 Node JS、MySQL 和 express。

我正在努力理解将 MySQL 数据加载到 HTML 中的代码的放置位置。

让我向您展示整个代码。

应用程序.js

var express = require('express');

var mysql = require('mysql');
var dotenv = require('dotenv');
var path = require('path');
var cookieParser = require('cookie-parser');

dotenv.config({path: './.env'});


var app = express();

// Connection to MySQL
var db = mysql.createConnection({
  host: process.env.DATABASE_HOST,
  user: process.env.DATABASE_USER,
  password:  process.env.DATABASE_PASSWORD,
  database: process.env.DATABASE
});

db.connect(function(error) {
  if(error) {
    console.log(error);
  }
  else{
    console.log("Connected");
  }
});

// Parse URL-Encoded bodies
app.use(express.urlencoded({extended: false}));
// Parse JSON bodies
app.use(express.json());
// Initialize a cookie
app.use(cookieParser());

// View engine to control HTML
app.set('view engine', 'hbs');

// Public dir
var publicDir = path.join(__dirname, './public');
app.use(express.static(publicDir));

// Define routes
app.use('/', require('./routes/pages'));
app.use('/auth', require('./routes/auth'));

app.listen(3000, function() {
  console.log("Server is running on port 3000");
});

路线/pages.js

var express = require('express');
var authController = require('../controllers/auth');

var router = express.Router();

// Home
router.get("/", authController.isLoggedIn, function(req,res) {
  res.render("index", {
    user: req.user
  });
});

// Register
router.get("/register", function(req, res) {
  res.render("register");
});

// Login
router.get("/login", function(req, res) {
  res.render("login");
});

// Profile
router.get('/profile', authController.isLoggedIn, function(req, res) {
  if(req.user) {
    res.render('profile', {
      user: req.user
    });
  }
  else {
    res.redirect('login');
  }

});

// Forum
router.get('/forums', authController.isLoggedIn, function(req, res) {
  if(req.user) {
    res.render('forums');
  } else {
    res.redirect('login');
  }
});

// English Division //

// Premier League
router.get('/Leagues/EnglishDivision', authController.isLoggedIn,  function(req, res) {
  if(req.user) {
    res.render('PremierLeague');

  } else {
    res.redirect('../../login');
  }
});


module.exports = router;

路线/auth.js

var express = require('express');
var authController = require('../controllers/auth');

var router = express.Router();

// Register
router.post("/register", authController.register);

// Login
router.post("/login", authController.login);

// Logout
router.get('/logout', authController.logout);

module.exports = router;

控制器/auth.js

var mysql = require('mysql');
var jwt = require('jsonwebtoken');
var bcrypt = require('bcryptjs');
var {promisify} = require('util');


// Connection to MySQL
var db = mysql.createConnection({
  host: process.env.DATABASE_HOST,
  user: process.env.DATABASE_USER,
  password:  process.env.DATABASE_PASSWORD,
  database: process.env.DATABASE
});

// Register function
exports.register = function(req, res) {
  console.log(req.body);

  var {name, email, password, passwordConfirm} = req.body;

  db.query("SELECT email FROM users WHERE email = ?", [email], function(error, result) {
    if(error){
      console.log(error);
    }

    if(result.length > 0) {
      return res.render('register', {
        message: 'That email is already in use'
      })
    } else if(password !== passwordConfirm) {
      return res.render('register', {
        message: 'Passwords do not match'
      });
    }

    let hashedPassword = bcrypt.hashSync(password, 8);
    console.log(hashedPassword);

    // Insert user details into MySQL
    db.query('INSERT INTO users set ?', {name: name, email: email, password: hashedPassword, dateJoined: new Date()}, function(error, result) {
      if(error) {
        console.log(error);
      } else {
        console.log(result);
        return res.render('register', {
          message: 'User registered'
        });
      }
    });
  });
}


// Login function

exports.login = function(req, res) {
  try {

    var {email, password} = req.body;

    if(!email || !password) {
      return res.status(400).render('login', {
        message: 'Please provide an email and password'
      });
    }

    db.query('SELECT * FROM users WHERE email = ?', [email], async function(error, result) {

      console.log(result);

      if(!result.length > 0 || !(await bcrypt.compare(password, result[0].password))) {
        res.status(401).render('login', {
          message: 'The email or password is incorrect'
        });
      }


      else {

        var id = result[0].id;

        // Create a token
        var token = jwt.sign({id}, process.env.JWT_SECRET, {
          expiresIn: process.env.JWT_EXPIRES_IN
        });

        console.log("The token is " + token);

        // Create a cookie
        var cookieOptions = {
          expires: new Date(
            Date.now() + process.env.JWT_COOKIE_EXPIRES * 24 * 60 * 60 * 1000
          ),
          httpOnly: true
        }

        // Set up a cookie
        res.cookie('jwt', token, cookieOptions);
        res.status(200).redirect("/");

      }
    });


  } catch (error) {
    console.log(error);
  }
}

// Check if logged in
exports.isLoggedIn = async function(req, res, next) {
  console.log(req.cookies);
  if(req.cookies.jwt){
    try {
      // Verify the token
      var decoded = await promisify(jwt.verify)(req.cookies.jwt, process.env.JWT_SECRET);

      console.log(decoded);

      // Check if user exist
      db.query("SELECT id, name, email, password, date_format(datejoined, '%d/%m/%Y') as dateJoined FROM users WHERE id = ?", [decoded.id], function(error, result) {
        console.log(result);

        // If no result
        if(!result) {
          return next();
        }

        req.user = result[0];
        return next();

      });
    }
  catch (e) {
    console.log(e);
    return next();
  }
} else{
    next();
  }
}

// Logout function
exports.logout = async function(req, res) {
  res.clearCookie('jwt');
  res.status(200).redirect('/');
}

问题

在我的.hbs文件中，PremierLeague我想以 HTML 格式加载 MySQL 数据。我需要在下面的代码中从哪里开始？

期望的目标：

这是用户点击查看英超联赛的时间

在此处输入图像描述

MySQL 中的 Foreach 记录我想为每条记录添加一张新卡。我知道如何使用 HandleBars {{some.data}}。

标签： mysqlnode.js

解决方案

您不需要任何其他特定的控制器，编码查询的正确位置实际上是路由本身。

但在进入问题的核心之前，让我们先谈谈您的代码。

我可以看到您不止一次执行与数据库的连接，您可以添加数据库专用控制器，例如：

控制器/db.js

var mysql = require('mysql');
var dotenv = require('dotenv');

dotenv.config({path: './.env'});

// Connection to MySQL
var db = mysql.createConnection({
  host:     process.env.DATABASE_HOST,
  user:     process.env.DATABASE_USER,
  password: process.env.DATABASE_PASSWORD,
  database: process.env.DATABASE
});

function connect(done) {
  db.connect(done);
}

module.exports = { db: db, connect: connect };

这使您只需一行即可从每个文件访问数据库实例：

var db = require('./controllers/db').db;

比您可以connect在您的应用程序中使用该功能：

应用程序.js

var express = require('express');
var db = require(./controllers/db);
var path = require('path');
var cookieParser = require('cookie-parser');

// set up your server
var app = express();

// Parse URL-Encoded bodies
app.use(express.urlencoded({extended: false}));
// Parse JSON bodies
app.use(express.json());
// Initialize a cookie
app.use(cookieParser());

// View engine to control HTML
app.set('view engine', 'hbs');

// Public dir
var publicDir = path.join(__dirname, './public');
app.use(express.static(publicDir));

// Define routes
app.use('/', require('./routes/pages'));
app.use('/auth', require('./routes/auth'));

// finally run your server only if you can connect to the database
db.connect(function(error) {
  if(error) return console.log("Error connecting to the database:", error);

  app.listen(3000, function() {
    console.log("Server is running on port 3000");
  });
});

您还可以简化您的控制器/auth.js删除数据库连接的东西并仅使用该行来要求您的数据库控制器。

最后，您可以对查询进行编码：

路线/pages.js

var express = require('express');
var authController = require('../controllers/auth');
var db = require('../controllers/db').db;

var router = express.Router();

// Omissis... other routes

// Premier League
router.get('/Leagues/EnglishDivision', authController.isLoggedIn, function(req, res) {
  // a good practice is first to handle possible exit cases to reduce nesting levels
  if(! req.user) return res.redirect('../../login');

  // this is actually the right place to perform queries
  db.query('SELECT ...', [...], function(error, results) {
    // once again first possible exit cases
    if(error) return res.status(500).end(error.message)

    res.render('PremierLeague', { results: results });
  });
});

module.exports = router;

最后在PremierLeague.hbs文件中，您可以处理resultsin#foreach指令。

推荐阅读