5

I am trying to do a select query on MySQL with phpMyAdmin or PHP with PDO.

SELECT 'uid' FROM 'clusters' WHERE 'lat'<='47.21125' AND 'lat'>='39.21125' AND 'lng'<='32.90243' AND 'lng'>='22.90243'

However, phpMyAdmin says:

#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 ''clusters' WHERE 'lat'<='47.21125' AND 'lat'>='39.21125' AND 'lng'<='32.90243' A' at line 1

What is wrong with it?

ajreal
  • 46,720
  • 11
  • 89
  • 119
Hristo
  • 6,382
  • 4
  • 24
  • 38

1 Answers1

10

'' creates a string literal in MySQL, so your query is selecting the literal "uid" from the literal "clusters," which is invalid. Use backtics (or nothing)

SELECT Uid FROM clusters WHERE lat <= 47.21125 AND lat >= 39.21125
AND lng >= 22.90243
Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
  • i just want to say, is not me who give a down vote. however, you should not using quote for the double value as well. – ajreal Apr 07 '13 at 17:09
  • @ajreal I don't think that it makes a difference, but I edited my answer to remove them – Explosion Pills Apr 07 '13 at 17:10
  • the quote will force mysql to perform unnecessary casting, which by no mean ... the index could be ignored (however, the SQL by OP is not using index) – ajreal Apr 07 '13 at 17:14
  • @ajreal that's only if it's a decimal and not a string. It's possible that the column is a string, but I think it is more likely that it is a decimal so I changed my answer anyway. Can you cite the fact that MySQL ignores indices on numeric columns when compared to a string value? – Explosion Pills Apr 07 '13 at 17:30
  • http://bugs.mysql.com/bug.php?id=34384 , http://bugs.mysql.com/bug.php?id=43319 .. for both bug, the example are using really BIG numeric number, however, I believe, the core issue is caused by using the "quote" as it mentioned in the details. – ajreal Apr 07 '13 at 18:08