Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

What's wrong in the mysql query with node.js?

Posted on 2014-03-12
4
Medium Priority
?
391 Views
Last Modified: 2014-03-12
Hi all,

I have node.js api with mysql. I am using the query below and it is working perfect,

"SELECT id, name, downloads, description, Price, extra2, extra3, smaatzImage,smaatzUrl,category.categoryId as category FROM smaatz LEFT OUTER JOIN smaatzcategory ON smaatz.id = smaatzcategory.SmaatzId  LEFT OUTER JOIN category  ON smaatzcategory.CategoryId = category.categoryId where "+ (category?"category.categoryId = "+category+" and":"")+" name like '%"+keyword+"%'"

when i try to modify it like this it is not working, what is wrong with this, any syntax mistake.

"SELECT id, name, downloads, description, Price, extra2, extra3, smaatzImage,smaatzUrl,category.categoryId as category FROM smaatz LEFT OUTER JOIN smaatzcategory ON smaatz.id = smaatzcategory.SmaatzId  LEFT OUTER JOIN category  ON smaatzcategory.CategoryId = category.categoryId where extra2="dbid_private_demo_cars" AND "+ (category?"category.categoryId = "+category+" and":"")+" name like '%"+keyword+"%'"

The bold characters above added in syntax did i made wrong query?
Any kind of help appreciated.

Thanks & Regards,
Krunal T Tailor
0
Comment
Question by:KRUNAL TAILOR
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 43

Expert Comment

by:pcelba
ID: 39923092
If the extra2 is a character string then you have to enclose the dbid_private_demo_cars  into quotes:

"SELECT id, name, downloads, description, Price, extra2, extra3, smaatzImage,smaatzUrl,category.categoryId as category FROM smaatz LEFT OUTER JOIN smaatzcategory ON smaatz.id = smaatzcategory.SmaatzId  LEFT OUTER JOIN category  ON smaatzcategory.CategoryId = category.categoryId where extra2='" + dbid_private_demo_cars + "' AND "+ (category?"category.categoryId = "+category+" and":"")+" name like '%"+keyword+"%'"

extra2 could also be qualified by table name, e.g.  smaatz.extra2  (this is valid for all columns in the query)
0
 
LVL 43

Expert Comment

by:pcelba
ID: 39923107
If extra2 does not represent character string but e.g. number then depends on dbid_private_demo_cars data type.

Let suppose dbid_private_demo_cars contains a string which represents data in the numeric extra2 column. In other words if it contains some number as a string then you may simply use + operator:

"SELECT id, name, downloads, description, Price, extra2, extra3, smaatzImage,smaatzUrl,category.categoryId as category FROM smaatz LEFT OUTER JOIN smaatzcategory ON smaatz.id = smaatzcategory.SmaatzId  LEFT OUTER JOIN category  ON smaatzcategory.CategoryId = category.categoryId where extra2=" + dbid_private_demo_cars + " AND "+ (category?"category.categoryId = "+category+" and":"")+" name like '%"+keyword+"%'"

If dbid_private_demo_cars is of the numeric data type then you have to convert it into a string before adding it to the query.

And the last possibility - the extra2 contains the string "dbid_private_demo_cars"... Then you just need to enclose the hardcoded string to quotes:

"SELECT id, name, downloads, description, Price, extra2, extra3, smaatzImage,smaatzUrl,category.categoryId as category FROM smaatz LEFT OUTER JOIN smaatzcategory ON smaatz.id = smaatzcategory.SmaatzId  LEFT OUTER JOIN category  ON smaatzcategory.CategoryId = category.categoryId where extra2= 'dbid_private_demo_cars'  AND "+ (category?"category.categoryId = "+category+" and":"")+" name like '%"+keyword+"%'"
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39923252
where extra2="dbid_private_demo_cars" AND

should be:
where extra2='dbid_private_demo_cars' AND

single quotes instead of double quotes as posted above
0
 
LVL 12

Author Closing Comment

by:KRUNAL TAILOR
ID: 39925424
Guy Hengel Thank you very much.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
What we learned in Webroot's webinar on multi-vector protection.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question