Why does this SELECT DISTINCT querie not work?

[JamieWarren09]

Hi Guys,

For some reason this isnt working and wont display any results...

Any ideas?
<?
$query1 = mysql_query ( "SELECT DISTINCT `artist` FROM `Artists` WHERE `Genre1` = '$usr->Genre1' OR `Genre2` = '$usr->Genre1' OR `Genre3` = '$usr->Genre1' OR `Genre1` = '$usr->Genre2' OR `Genre2` = '$usr->Genre2' OR `Genre3` = '$usr->Genre2' OR `Genre1` = '$usr->Genre3' OR `Genre2` = '$usr->Genre3' OR `Genre3` = '$usr->Genre3' ORDER BY RAND() LIMIT 0,1" );
while ( $art = mysql_fetch_object ( $query1 ) )
{
?>


Basically, I need it to select an artist and it will display the artist picture, but at the moment it sometimes shows the same artist more than once.

Regards,
Jamie

[JamieWarren09]

Any one able to help?

[Nightwalker83]

When you need a distinct object that when you use "where" if you just want to find everything you use

$query1 = mysql_query ( "SELECT`*` FROM `Artists`

for distinct select use

$query1 = mysql_query ( "SELECT `artist` FROM `Artists` WHERE `Genre1` = '$usr->Genre1' OR `Genre2` = '$usr->Genre1' OR `Genre3` = '$usr->Genre1' OR `Genre1` = '$usr->Genre2' OR `Genre2` = '$usr->Genre2' OR `Genre3` = '$usr->Genre2' OR `Genre1` = '$usr->Genre3' OR `Genre2` = '$usr->Genre3' OR `Gen

That is remove the "DISTINCT" from the select statement.

[dee-u]

Wouldn't your sql just return 1 record everytime by its "LIMIT 0,1"?

@Nightwalker83: How would that return a DISTINCT record?

[Nightwalker83]

How would that return a DISTINCT record?

Isn't a distinct value just a matter of using "Where" in the search?

[dee-u]

No, distinct would return unique records. There could be 2 or more records returned by a query that satisfies a WHERE clause and using DISTINCT would eliminate the duplicate records. We could also just use GROUP BY which is the same as using DISTINCT.

[Nightwalker83]

No, distinct would return unique records. There could be 2 or more records returned by a query that satisfies a WHERE clause and using DISTINCT would eliminate the duplicate records. We could also just use GROUP BY which is the same as using DISTINCT.

Ah ok! I just looked at a Java example of mine dealing with coloured pencils and it uses.

"SELECT colour, size "+
            "FROM tblPencils " +
            "WHERE colour = ? "+
            "ORDER BY colour";

by as you say that example is using "ORDER BY".