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
Solved

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

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

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 42

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 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

861 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