CODEWITHSUNDEEP
mysqlnode.js
Social Code
Social Code

Reputation: 315

SELECT query from mysql with node js (LIKE query)

I'm using node js to develope my project, i use mysql to store my data.

I have a problem when i select with like query, and it give an error like this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test'%' ORDER BY create_time DESC LIMIT 0,10' at line 1

SELECT * FROM user WHERE fullname LIKE N'%'test'%' ORDER BY create_time DESC LIMIT 0,10

I know error here is 'test' in query, but it's a string, i can't remove it, my code here:

data = {};
data.fullname = 'test';
data.start    = 0;
data.limit    = 10;
let getFullname = function (data, callback) {
   return db.query("SELECT * FROM user WHERE fullname LIKE N'%?%' ORDER BY create_time DESC LIMIT ?,? ", [data.fullname,data.start,data.limit], callback);
}

How can i solve my problem, thank you!

Upvotes: 4

Views: 8209

Answers (2)

LookWorld
LookWorld

Reputation: 41

CONCAT("%", ? , "%")

:)

this is part of my code :

########## 

where += ` AND ( titre LIKE CONCAT("%", ? , "%") OR resume LIKE CONCAT("%", ? , "%") ) ` ; 
vals.push(dataRes.rech) ; 
vals.push(dataRes.rech) ; 

############# 

sql = `SELECT #########  WHERE 1=1 ${where}` ;  

connection.query( sql , vals , async function(err, services, fields) { 
if(err) rej({er : 1 , code : err , sql: sql , vals : vals}) ; 
else{ res({er : 0 , data : services }) }

Upvotes: 2

Arif Khan
Arif Khan

Reputation: 5069

You are right, problem is mysql package add single quote ', you can use following manner

   return db.query(`SELECT * FROM user WHERE fullname LIKE N'%${data.fullname}%' ORDER BY create_time DESC LIMIT ?, ? `, [data.start,data.limit], callback);

or

data.fullname = '%' + data.fullname + '%';
return db.query("SELECT * FROM user WHERE fullname LIKE N? ORDER BY create_time DESC LIMIT ?,? ", [data.fullname,data.start,data.limit], callback);

Upvotes: 5

Related Questions