javascript - Node.js SELECT*FROM SQL 数据库中的表

Question

我的问题是我可以将输入框中的数据输入到 SQL 表中，但问题是表中的文本不会显示在我想要的页面上。有效的是，播放列表页面将显示 a href 链接，具体取决于我制作的播放列表数量，而不是文本。

出于安全原因，我省略了数据库详细信息。

播放列表.jade

extends layout

block content
  h1= title
  p Welcome to #{title}

   h1 My Playlists
   br
   a(href='/login') Login
   br
   a(href='/users/createPlaylist') Create a new Playlists
   br
   a(href='/users/playlistCreated') test
   br

   for playlist, i in playlists
    a(href='/users/playlistCreated') | #{playlist.text}
    br

用户.js

var express = require('express');
var mysql = require('mysql');
var router = express.Router();

var dbConnectionInfo = {
  host : '',
  user : '',
  password : '',
  database : 'audio_collections'
};

router.get('/createPlaylist', function(req, res, next) {
  res.render('new_playlist');
});

router.get('/playlistCreated', function(req, res, next) {
  res.render('name_of_created_playlist');
});

router.get('/songCreated', function(req, res, next) {
  res.render('song_page');
});

router.get('/newSong', function(req, res, next) {
  res.render('new_song');
});

router.post('/newPlaylist', function(req, res, next) {

  var dbConnection = mysql.createConnection(dbConnectionInfo);
  dbConnection.connect();

  dbConnection.on('error', function(err) {
    if (err.code == 'PROTOCOL_SEQUENCE_TIMEOUT') {
      // Let's just ignore this
      console.log('Got a DB PROTOCOL_SEQUENCE_TIMEOUT Error ... ignoring ');
    } else {
      // I really should do something better here
      console.log('Got a DB Error: ', err);
    }
  });

  var playlist = {
    text:  req.body.thePlaylist
  };


  dbConnection.query('INSERT INTO Playlists (playlist_name) VALUES(?)',     [playlist.text], function(err, results, fields) {
// error will be an Error if one occurred during the query
// results will contain the results of the query
// fields will contain information about the returned results fields (if  any)
    if (err) {
      throw err;
    }

    // notice that results.insertId will give you the value of the AI (auto-increment) field
    playlist.id = results.insertId;

    // Going to convert my joke object to a JSON string a print it out to the console
    console.log(JSON.stringify(playlist));

    // Close the connection and make sure you do it BEFORE you redirect
    dbConnection.end();

    res.redirect('/');
  });

  router.post('/newSongAdded', function(req, res, next) {

  var dbConnection = mysql.createConnection(dbConnectionInfo);
  dbConnection.connect();

  dbConnection.on('error', function(err) {
    if (err.code == 'PROTOCOL_SEQUENCE_TIMEOUT') {
      // Let's just ignore this
      console.log('Got a DB PROTOCOL_SEQUENCE_TIMEOUT Error ... ignoring ');
    } else {
      // I really should do something better here
      console.log('Got a DB Error: ', err);
    }
  });

  var song = {
    text: req.body.theSong,
    url: req.body.theSongURL
  };

  dbConnection.query('INSERT INTO Songs (song_name, song_url) VALUES(?,?)',[song.text, song.url], function(err, results,fields) {
    // error will be an Error if one occurred during the query
    // results will contain the results of the query
    // fields will contain information about the returned results fields (if any)
    if (err) {
      throw err;
    }

    // notice that results.insertId will give you the value of the AI (auto-increment) field
    song.id = results.insertId;

    // Going to convert my joke object to a JSON string a print it out to the console
    console.log(JSON.stringify(song));

    // Close the connection and make sure you do it BEFORE you redirect
    dbConnection.end();


    res.redirect('/');
  });

 });
});

module.exports = router;

index.js

var express = require('express');
var mysql = require('mysql');
var router = express.Router();

var dbConnectionInfo = {
  host : '',
  user : '',
  password : '',
  database : 'audio_collections'
};

/* GET home page. */
router.get('/login', function(req, res, next) {
  res.render('login');
});

router.post('/login', function(req, res, next) {
  var username = req.body.username;

  username = username.trim();

  if (username.length == 0) {
    res.redirect('/login');
  }
  else {
    req.session.username = username;
    res.redirect('/');
  }
});

router.get('/', function(req, res, next) {
  var dbConnection = mysql.createConnection(dbConnectionInfo);
  dbConnection.connect();

  dbConnection.on('error', function(err) {
    if (err.code == 'PROTOCOL_SEQUENCE_TIMEOUT') {
      // Let's just ignore this
      console.log('Got a DB PROTOCOL_SEQUENCE_TIMEOUT Error ... ignoring ');
    } else {
      // I really should do something better here
      console.log('Got a DB Error: ', err);
    }
  });

  dbConnection.query('SELECT * FROM Playlists', function(err, results, fields){
    if (err) {
      throw err;
    }

    var allPlaylists = new Array();

    for (var i = 0; i < results.length; i++) {
      var playlist = {
        id: results[i].id,
        text: results[i].text
      };

      console.log(JSON.stringify(playlist));

      allPlaylists.push(playlist);
    }

    dbConnection.end();

    res.render('playlists', {playlists: allPlaylists});
  });

  router.get('/users/playlistCreated', function(req, res, next) {
    var dbConnection = mysql.createConnection(dbConnectionInfo);
    dbConnection.connect();

    dbConnection.on('error', function(err) {
      if (err.code == 'PROTOCOL_SEQUENCE_TIMEOUT') {
        // Let's just ignore this
        console.log('Got a DB PROTOCOL_SEQUENCE_TIMEOUT Error ... ignoring ');
      } else {
        // I really should do something better here
        console.log('Got a DB Error: ', err);
      }
    });

    dbConnection.query('SELECT * FROM Songs', function(err, results, fields){
      if (err) {
        throw err;
      }

      var allSongs = new Array();

      for (var i = 0; i < results.length; i++) {
        var song = {};
        song.id = results[i].id;
        song.text = results[i].text;
        song.url = results[i].url;

        console.log(JSON.stringify(song));

        allSongs.push(song);
      }

      dbConnection.end();

      res.render('name_of_created_playlist', {songs: allSongs});
    });
  });
});

module.exports = router;

new_playlist.jade

extends layout

block content

    form(method='POST', action='/users/newPlaylist')
      div
        label(for='thePlaylist') Name of Playlist
        div
          textarea(type='text', name='thePlaylist')
          br
          input(type='submit', name='Add new Playlist')

    a(href='/') Cancel

这是数据库和表设置

database and table setup

我真的很感谢您的帮助，我已经在这个问题上坚持了一个星期了。

Answer 1

解决了，我不需要数组中的 for 循环

只需要 var full Playlist = results;由于结果已经是一个数组