How to use like query in mysql using nodejs

Question

I have been trying to use like query on MySQL but it won't match the pattern with database and only works when the exact keyword is given(% sign doesn't work)

I have tried Concat method, using backslash for special characters,+ symbol and every other approach I can find but still, it isn't working

connection.query('SELECT * FROM job where domainname like ?', '%' + request.body.domain + '%' ,function(error, results){
            response.render('./results',{rows:results});    

    });
and on front end part
  <tbody>
    <% for(var i=0; i < rows.length; i++) { %>
   <tr>
     <td><%= i %></td>
     <td><%= rows[i].companyname %></td>
     <td><%= rows[i].domainname %></td>
   </tr>
<% } %>

Error: Cannot read property 'length' of undefined it works if I don't use % and don't try pattern matching and then shows the results.

Kanad Chourasia · Accepted Answer · 2019-05-31 12:55:17Z

8

try to keep the values in array

connection.query('SELECT * FROM job where domainname like ?', ['%' + request.body.domain + '%'] ,function(error, results){
        response.render('./results',{rows:results});    

});

print error and results if it is not working

for more info goto - https://www.npmjs.com/package/mysql#performing-queries

answered May 31, 2019 at 12:55

Kanad Chourasia

5514 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pushkar Goel Over a year ago

thanks it worked! I tried it yesterday as well but i think it wanted your attention before completion. Thanks again mate

David J Eddy · Accepted Answer · 2019-05-31 12:44:39Z

Pushkar, welcome to StackOverflow. I might be able to shed a little light on your query. In my experience the 'LIKE' operator in MySQL works as follows:

% at the beginning means ignore character before the match. 'qweradsfzxcv
% at the end means ignore characters after the match. 'qwerasdfzxcv'
%% means the match can be in the middle of a string. 'qwerasdfzxcv'

The length error is due to 'rows' having no value. A good defensive programming habit is to never trust that data from a 3rd part is populated. Just like we never trust user input, never trust 3rd party data. In this case, wrap the SQL select with a check. If 'rows' has no values, do not try to render the the tbody.

Back to the SQL query; try hard coding all the values as string literals. See if the query as formulated return any values. Then pull it apart into the parameterized version.

Collectives™ on Stack Overflow

How to use like query in mysql using nodejs

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related