Solved

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

Posted on 2014-03-12
4
378 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 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

25 Experts available now in Live!

Get 1:1 Help Now