Bruce O. Bruce O. - 5 months ago 17
SQL Question

Multiple columns in a WHERE clause

I'm trying to make a url query string where you enter a parameter and it searches my MySQL database(stored quotes from an irc channel) for one random quote with a variable entered in LIKE at a time but having trouble getting it to search more than one column with the query I'm using. The two fields in the table I want it to search in were "title" and "quote". Is there a better way of doing this? Right now i just have it going to a page to confirm output for now for testing purposes. The code below works fine as is if just searching the "title" or "quote" field alone.

app.get("/api", function(req, res){

var search = "%" + req.query.search + "%";
var queryString = "SELECT * FROM quotes WHERE title LIKE ? ORDER BY RAND() LIMIT 1";
var queryString = mysql.format(queryString, search);
mysqlPool.getConnection(function(err, connection) {
if(err) throw err;
connection.query(queryString, function(err, rows, fields){
if (!err){
res.send('<xmp>' + JSON.stringify(rows[0], null, 2) + '</xmp>');
connection.release();
} else {
console.log('Error while performing Query.');
connnection.release();
}
});
});
});


I tried doing this below but obviously isn't working:

var queryString = "SELECT * FROM quotes WHERE (title OR quote) LIKE ? ORDER BY RAND() LIMIT 1";


example of the output with just "title" searched here just to get an idea:

{
"id": 3947,
"nick": "o_O",
"host": "o_O``!~o_O@*.ma.cable.rcn.com",
"title": "gix.flesh.eating.bacteria",
"quote": "<gix> omg my face is like 10lbs lighter",
"channel": "#motorcycles",
"timestamp": 1205791192
}

Answer

You can not use LIKE like that, try this;)

var queryString = "SELECT * FROM quotes WHERE title LIKE ? OR quote LIKE ? ORDER BY RAND() LIMIT 1";