Re: Running a 'Distinct' query returns some results TWICE !?
Hi Ram and thanks for helping.
My create table code (as retrieved by MySQLcc) is:
CREATE TABLE `photos` (
`ID` smallint(6) unsigned NOT NULL auto_increment,
`Title` varchar(100) default '',
`Description` blob,
`Country` varchar(100) default '',
`Place` varchar(100) default '',
PRIMARY KEY (`ID`)
) TYPE=MyISAM;
Now, the query:
SELECT distinct(country)
FROM `photos`
this works perfectly well [for country]. However, when I substiture 'country' with 'place', then the problem with the double results occurs.
The content of this photo-database is simply the photo's ID, a Title for the photo, a brief description, then the Country and Place (town) where it was taken (overseas holiday).
The reason for wanting the distinct to work is that on my webpage (php) I can allow users to pick from two drop down menus. One to select a Country or the other to select a Place. Then, when they click on Submit, the database only shows photos based on the selection. (this actually works fine, except that where TWO results are returned in the drop down menu, some photos will be available under the first entry and the others under the second one.
Some more info in case it helps your diagnosis (but not sure if relevant):
At some stage during [trying] to resolve the issue I noticed that when I made changes to the title or description fields, then that particular photo would then be displayed under the second result as opposed to the first one. I continued to make changes to the text of the remining photos and sure enough, they all started to show under the second entry of the place (eg. London). Once I made changes to the last remaing photo, the second entry dissapeard completely. However, a) I cannot even replicate that behaviour anymore with other places that show twice, and b) I'm dying to know what actually goes on ... :-)
Again, I appreciate you looking into it
Thanks
Rob