Hi I have some problem on nodejs to get data on mysql
My question is 'How can I add var n into "%drawing eye%" to get data on database?'
function showResult(req, res){
var n = req.query.query
mysql_conn.query('SELECT query_text FROM catalogsearch_query WHERE query_text LIKE "%drawing eye%" ORDER BY popularity DESC LIMIT 0 , 10', function(error, rows){
res.render('result.html',{result:n , related: rows.map(row => row.query_text)})
})
}
do something like this LIKE "%' + n + '%" ORDER BY
function showResult(req, res) {
var n = req.query.query
mysql_conn.query('SELECT query_text FROM catalogsearch_query WHERE query_text LIKE "%' + n + '%" ORDER BY popularity DESC LIMIT 0 , 10', function(error, rows) {
res.render('result.html', {
result: n,
related: rows.map(row => row.query_text)
})
})
}
The mysql module supports queries with placeholders, where those placeholders get replaced by variables you pass (properly-escaped, to prevent SQL injections):
function showResult(req, res) {
var n = req.query.query;
mysql_conn.query(`
SELECT query_text
FROM catalogsearch_query
WHERE query_text LIKE ?
ORDER BY popularity DESC
LIMIT 0, 10`,
'%' + n + '%',
function(error, rows) {
if (err) return res.sendStatus(500);
res.render('result.html',{result:n , related: rows.map(row => row.query_text)})
}
)
}
The ? after LIKE is the placeholder, which will get replaced by an escaped version of '%' + n + '%'.