-1

I have the below data in my MySQL table "categories":

id    Name
-----------------
1     Books & CDs
2     Dress

When I try to get the value from table it works fine with below SQL.

SELECT * FROM `categories` WHERE `name` = 'Books & Cds';

But when using in PHP, it gives me some SQL error.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

Where can I find the actual reason for this? How to debug this?

fthiella
  • 48,073
  • 15
  • 90
  • 106
Purus
  • 5,701
  • 9
  • 50
  • 89
  • Look at this link http://stackoverflow.com/questions/11704233/the-best-way-to-store-ampersand-in-mysql-database, maybe this is your solution. Wouter – wsplinter Feb 18 '13 at 14:41
  • are you sure with that? i just tested and it worked: http://www.sqlfiddle.com/#!2/703fa/1 – John Woo Feb 18 '13 at 14:41
  • 3
    can you show your php code? – John Woo Feb 18 '13 at 14:50
  • I am using a PHP framework and not plain PHP. I am new to Stackoverflow. I am stil learning how it works. I see that this question is down voted. What does that mean? – Purus Feb 18 '13 at 16:35
  • 1
    The downvote (wasn't me) is because you didn't do a complete job of describing your problem - as other people have said, you need to include the php code that generates the query that is giving you the syntax error (the problem is probably in how you escape or quote things in PHP, since the SQL query you quote doesn't have any problems). – D Mac Feb 19 '13 at 17:17
  • Sorry for that. I am very very new to this website and still learning to master the rules of this site. I will learn it soon. – Purus Feb 22 '13 at 17:03

1 Answers1

0

If you want to search for rows in your Category table in your PHP code, I recommend that you add a column in your table for a code to use instead of searching on the name that you use for the end users. The potential problem that you're facing is that if you want to change the name of a category, you'd have to find everywhere in your code that you referred to that category by name and change it too. But if your table looked like this:

ID   code   name
1    BCDS   Books & CDs
2    DRS    Dress

then your code can do things like "where code = 'BCDS'" and you can call that category "Books & CDs", "CDs and Books", or anything else you like.

Now, as far as fixing your syntax problem, you'll have to post the PHP that you use to generate the query that fails. As another poster said, you're probably escaping something incorrectly and MySQL isn't getting the query you think it's getting.

D Mac
  • 3,727
  • 1
  • 25
  • 32
  • Sorry, this does not look like a feasible and easiest solution to me. – Purus Feb 18 '13 at 16:37
  • It isn't the easiest in the short term, but in the long term you'll be glad you did it. I can't tell you how much time I've wasted fixing bugs in queries because something that was displayed to the user and therefore needed to be changed was also used in constructing the query. – D Mac Feb 19 '13 at 17:16