[RESOLVED] Distinct in MYSQL

nextgengames

Right, Im building a small event list manager. When you enter a new event you have a dropdown for previous locations to use again or a text box to enter a new one.

The problem i have is MYSQL is giving every location multiple times eg just dumping the whole column not ignoring duplicates. The SQL im using is below :

SELECT DISTINCT location FROM vk_cal WHERE 1 ORDER BY location ASC

Thanks Kevin

tomhath

The bug is probably somewhere else in your page.

If you can reproduce the problem from the command line or something like phpmyadmin, post the table schema and data that shows it.

nextgengames

Right, i tried the sql in phpmyadmin it had the same results. Screens attached :/

tomhath

Very odd. I suppose the first thing I'd do is drop the table and recreate it. Something isn't adding up.

nextgengames

Attempting now

nextgengames

That didnt seem to work still same error :/

bradgrafelman

What version of MySQL?

Also, can you dump this table to a .sql file and attach it? (Might have to rename to .sql.txt)

nextgengames

MYSQL 4.1, It is indeed very odd.

tomhath

Look carefully at your INSERT statement. You have an extra blank at the beginning of a couple of 'location' values:

INSERT INTO `vk_cal` VALUES (..., 'Vale Karate Beddau', '9:00am');
INSERT INTO `vk_cal` VALUES (..., ' Vale Karate Beddau', '00:00am');
INSERT INTO `vk_cal` VALUES (..., 'Vale Karate Beddau', '18:00');
INSERT INTO `vk_cal` VALUES (..., 'Vale Karate Cyncoed', '9:00am');
INSERT INTO `vk_cal` VALUES (..., ' Vale Karate Cyncoed', '00:00am');
INSERT INTO `vk_cal` VALUES (..., 'Cardiff ', '00:00am');

nextgengames

Right, what a stupid mistake