Solved

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

Posted on 2014-03-12
4
379 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
  • 2
4 Comments
 
LVL 41

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 41

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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now