CODEWITHSUNDEEP
mysqlnode.jsnode-mysql
user9850863
user9850863

Reputation: 103

NodeJS MySQL query not working as expected

NodeJS code is shown here:

    app.get('/search', function(req, res){
    var keyword = req.query.q;
    con.query("SELECT Post_Title, Post_Icon, Post_Cont, Post_Author, Post_Date FROM Posts WHERE Post_Title LIKE '" + keyword + "' OR Post_Icon LIKE '" + keyword + "' OR Post_Cont LIKE '" + keyword + "' OR Post_Author LIKE '" + keyword + "' OR Post_Date LIKE '" + keyword + "' ORDER BY Post_Date ASC", function (err, result) {
    if (err){
    console.log("Error on DB SELECT.");
    console.log(err);
    tellSelectError(req, res);
}else{
    console.log("Database selected");
    console.log(result);
    /*res.render('index', {
        info: info,
        result: result
    });*/
    res.json(result);
    }
});
});

It sends an empty json to the client browser.

Screenshot is uploaded at: https://i.sstatic.net/kpSDA.jpg

Please help.....

This code is working:

SELECT * 
FROM Posts 
WHERE Post_ID = " + keyword

but I want to use LIKE with all columns of Posts excluding Post_ID.

console.log(err); logs no error.

Got news: when I change the SQL to

SELECT * 
FROM Posts

it correctly returns all rows, but

SELECT Post_Title, Post_Icon, Post_Cont, Post_Author, Post_Date 
FROM Posts 
WHERE Post_Title LIKE '" + keyword + "' 
   OR Post_Icon LIKE '" + keyword + "' 
   OR Post_Cont LIKE '" + keyword + "' 
   OR Post_Author LIKE '" + keyword + "' 
   OR Post_Date LIKE '" + keyword + "' 
ORDER BY Post_Date ASC

is not working as expected.

Upvotes: 1

Views: 54

Answers (1)

Lelio Faieta
Lelio Faieta

Reputation: 6688

You need to wrap the values you pass to the query in quotes. So the correct syntax for you should be:

"SELECT Post_Title, Post_Icon, Post_Cont, Post_Author, Post_Date 
FROM Posts 
WHERE Post_Title LIKE '" + keyword + "' OR Post_Icon LIKE '" + keyword + "' OR Post_Cont LIKE '" + keyword + "' OR Post_Author LIKE '" + keyword + "' OR Post_Date LIKE '" + keyword + "' ORDER BY Post_Date ASC"

Note: LIKE is an operator that is used instead of = to search for a value inside a field. = will try to match the full field. To do so LIKE use a wildcard (%) in three different options:

  • %keyword the value ends with keyword;
  • keyword% the value begins with keyword;
  • %keywords% the value contains somewhere the keyword

If you don't use the wildcard it is useless to use LIKE

Upvotes: 0

Related Questions