SELECT DISTINCT returning all values

Question

i have problem with SQL SELECT DISTINCT, and PHP/jQuery. I'm using autocomplete function, it is working, but not as expected. I have Database with some rows:

Lukoil..
Lukoil..
Statoil..
Statoil..
Statoil..
Neste..
Neste..

And I have SQL query :

SELECT DISTINCT name FROM poi_example WHERE name LIKE '%$text%' ORDER BY name ASC

But when I typing a name in search input, I don't get distinct values:

enter image description here

So I need your offers what to do,

Database output:

INSERT INTO `poi_example` (`id`, `name`, `description`, `lat`, `lon`, `city`, `rajonas`) VALUES
(24, 'Statoil', 'VeiveriÅ³ pl. 49a, Kaunas   tel.: 8-37 39 10 62   DegalinÄ—s darbo laikas VisÄ… parÄ…  PlovyklÅ³ darbo laikas: VisÄ… parÄ…', '54.88111', '23.89360', 'Kaunas', 'Aleksotas'),
(25, 'Statoil', 'Karaliaus Mindaugo pr.34a, Kaunas   tel.: 8-37 42 37 29      DegalinÄ—s darbo laikas VisÄ… parÄ…  PlovyklÅ³ darbo laikas: VisÄ… parÄ…', '54.89398', '23.91332', 'Kaunas', 'Naujamiestis'),
(26, 'Statoil', 'TvirtovÄ—s al. 33A, Kaunas  tel.: 8-37 33 71 53      DegalinÄ—s darbo laikas VisÄ… parÄ…', '54.91333', '   23.92631', 'Kaunas', 'Å½aliakalnis'),
(27, 'Lukoil', 'Darbo laikas: 00-24', '54.77708', '     24.11988', 'Kaunas', 'PetraÅ¡iÅ«nai'),
(28, 'Lukoil', 'Darbo laikas: 06-22', '54.85523', '     24.44175', 'KaiÅ¡iadiorys', 'Autostrada'),
(32, 'Neste', 'Dirba visÄ… parÄ… 24/7', '55.665701', '21.175737', 'KlaipÄ—da', 'KlaipÄ—dos'),
(33, 'Neste', 'Dirba 24/7', '55.948191', '25.588700', 'RokiÅ¡kis', 'RokiÅ¡kio');

Did you actually check the result of your query before stating your problem is related to your autocomplete? — Boris Guéry, Oct 17 '12 at 08:15
Similar question: http://stackoverflow.com/questions/6127338/sql-select-distinct-but-return-all-columns — gorzan, Oct 17 '12 at 08:17
I agree with John Woo (deleted answer) - you have a picture but it doesn't prove that the values are identical - if there were trailing spaces, they would be invisible in that screenshot. — Jeffrey Kemp, Oct 17 '12 at 08:21
on Database, my sql query working well, feels like problem in php? — Gntvls, Oct 17 '12 at 08:23
@MariusGentvilas you should paste your application code. Query seems fine. — Ertunç, Oct 17 '12 at 08:45
Thanks everyone for the help, i made the most stupid mistake, i was havin two similar files, but one was on the desktop, ant other in localhost server, so i was editing file o the desktop. sorry for your wasted time, thanx to everyone — Gntvls, Oct 17 '12 at 08:57

score 5 · Accepted Answer · answered Oct 17 '12 at 08:19

5

You could try to use an aggregate function.

SELECT name
FROM poi_example
WHERE name LIKE '%$text%'
GROUP BY name
ORDER BY name ASC

answered Oct 17 '12 at 08:19

gorzan

674
1
5
11

3

That should not make any difference (but then it's MySQL...) – Oct 17 '12 at 08:22
this query returns the same values as in my question image on top – Gntvls Oct 17 '12 at 08:24
There should not be a need for aggregate – Ertunç Oct 17 '12 at 08:44

score 1 · Answer 2 · answered Oct 17 '12 at 08:39

1

its obvious that the problem is on your application. No way a distinct would return duplicate values, specially because you say that the query works on your Database.

Are tou sure you are executing that exact query? Maybe if you post some code we can help you

answered Oct 17 '12 at 08:39

Diego

34,802
21
91
134

Than you man for your help, i found the solution, because i was editing duplicated file. – Gntvls Oct 17 '12 at 08:59
so you should add an answer yourself explaining what you did and mark that as answer. – Diego Oct 17 '12 at 11:59

SELECT DISTINCT returning all values

2 Answers2