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

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
LVL 12
KRUNAL TAILORFreelance Mobile DeveloperAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
pcelbaCommented:
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
 
pcelbaCommented:
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
 
KRUNAL TAILORFreelance Mobile DeveloperAuthor Commented:
Guy Hengel Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.