mysql - Node.js和mysql公告板搜索功能问题

Question

公告板是使用node.js和mysql实现的。这次我要实现一个搜索功能。这就是搜索表单在主页中的显示方式，并在 Controller 中处理后最终显示为搜索页面。但是，该表应该与数据库和搜索表单字符串相同。如果不准确，就不会出来。

表格

    form(action=routers.search, method="get")
        input(type="text" placeholder="searh", name="result")
        input(type="submit" value="searh")

Controller

export const search = (req, res) => {
  try {
    let result_value = req.query.result;
    let sql =
      "SELECT id, folder, title, nicName, date_format(writeDate, '%Y/%m/%d %T') as writeDate FROM board  where title=? order by id desc ";
    dbConnection.query(sql, [result_value], (err, result) => {
      if (err) {
        console.log(err);
      } else {
        console.dir(result);
        res.render("search", {
          homeName: "watchingame",
          pageName: "search",
          routers,
          result_value,
          result
        });
      }
    });
  } catch (err) {
    console.log(err);
  }
};

查看

extends layouts/layout
block content
    div
        h2 #{result_value}
    table
        each board in result
            tr
                td=board.id
                td
                    a(href=`/board${routers.boardDetail(board.id)}`)=board.title
                td=board.nicName
                td=board.writeDate
    a(href=`/board${routers.boardCreate}`) write

Answer 1

However, the table should be the same as the database and search form string. If it's not accurate, it won't come out.

我猜这意味着仅当查询中的标题与表中项目的标题完全匹配时才会显示结果。

如果你想进行部分匹配，例如query=dog、matches=dogs、the dog、hotdog等...)，那么你应该使用LiKE并且% 运算符。

下面是 SQL 语句示例。

SELECT 
  id, 
  folder, 
  title, 
  nicName, 
  date_format(writeDate, '%Y/%m/%d %T') as writeDate 
FROM board where title like %dog% order by id desc 

然后它会尝试查找标题中包含 dog 的任何内容。

所以你的sql字符串应该看起来像

'SELECT 
  id, 
  folder, 
  title, 
  nicName, 
  date_format(writeDate, '%Y/%m/%d %T') as writeDate 
FROM board where title LIKE ' + connection.escape('%' + result_value + '%')

如果您想了解有关 SQL Like Operator 的更多信息，请查看 link .