Unique Results after fetching

[danielhalawi]

Hi,

I have coded an array for fetchin results from my table.

However when displayin the results results are duplicated.

This is the WHERE for my code

WHERE vmplay_artists.id = vmplay_videos.artistid 
and videoactive = 'yes' and indepic = 'yes' 
ORDER BY RAND( NOW( ) )  LIMIT $max

I don't want to display another result for the same artist id. At the moment it can show four results, but can display two or more reults from the same artist id, I want it so that if one result is shown of artist ID7 then not to produce another result for ID 7 for example.

[lucasrd]

Maybe try GROUP BY artist_id at the end of your query

[danielhalawi]

Try adding GROUP by artistid and got an error?

[Installer]

Is artistid the only column you're selecting?

[lucasrd]

did you try GROUP BY vmplay_videos.artistid

[danielhalawi]

Originally posted by lucasrd
did you try GROUP BY vmplay_videos.artistid

Yea I did, and got the same error

Originally posted by Installer
Is artistid the only column you're selecting?

No, this is the full query.

SELECT vmplay_videos.id, 
vmplay_videos.artistid, 
vmplay_artists.website, 
vmplay_artists.smallerpic1, 
vmplay_artists.smallerpic2, 
vmplay_artists.artistfirst, 
vmplay_artists.artistlast, 
vmplay_videos.songname 
FROM vmplay_artists, vmplay_videos 
WHERE vmplay_artists.id = vmplay_videos.artistid 
and 
videoactive = 'yes' 
and 
indepic = 'yes' 
ORDER BY RAND( NOW( ) )  

LIMIT $max

[Installer]

It would be better to do it in the query, but, frankly, I don't know how. So this post-query code should give you an array with only one sub-array for each artistid:

$arr = array();
$tmp_arr = array();
while ($row = mysql_fetch_assoc($result)) {
    if (!in_array($row['artistid'], $tmp_arr)) {
        $arr[] = $row;
        $tmp_arr[] = $row['artistid'];
    }
}

If you want to do it in the query, you should mark this thread resolved and ask in the mySQL (edit: I mean Database) forum.

[leatherback]

Try:

SELECT distinct vmplay_artists.id ,
vmplay_videos.id, 
vmplay_videos.artistid, 
vmplay_artists.website, 
vmplay_artists.smallerpic1, 
vmplay_artists.smallerpic2, 
vmplay_artists.artistfirst, 
vmplay_artists.artistlast, 
vmplay_videos.songname 
FROM vmplay_artists, vmplay_videos 
WHERE vmplay_artists.id = vmplay_videos.artistid 
and 
videoactive = 'yes' 
and 
indepic = 'yes' 
ORDER BY RAND( NOW( ) )  

LIMIT $max

[lucasrd]

Can you post your mysql error to the board please....

[leatherback]

Hi Lucas,

This is not generating an error. It is more a query-method problem, then a query-error kind of problem.

J.

[lucasrd]

Ok...try this then....

SELECT vmplay_artists.id ,
vmplay_videos.id, 
vmplay_videos.artistid, 
vmplay_artists.website, 
vmplay_artists.smallerpic1, 
vmplay_artists.smallerpic2, 
vmplay_artists.artistfirst, 
vmplay_artists.artistlast, 
vmplay_videos.songname 
FROM vmplay_artists left join vmplay_videos on vmplay_artists.id=vmplay_videos.artistid
WHERE 
vmplay_videos.videoactive = 'yes' 
and 
vmplay_videos.indepic = 'yes' 
ORDER BY RAND( NOW( ) )  

LIMIT $max
GROUP BY vmplay_artists.id

[danielhalawi]

I'm greatful for all the heads up here, but the last change has created an error.

Warning: mysql_fetch_array(): 

supplied argument is not a valid MySQL result resource 

in /var/www/html/******.php on line 9

This is what is on line 9

while($row = mysql_fetch_array($result)){

However I'm assuming its something to do with the query as this is all that was changed?

[leatherback]

Hi,

Is that when runnin my code?

J.

[danielhalawi]

Originally posted by leatherback
Hi,

Is that when runnin my code?

J.

Oh sorry, I should of updated, I tried using 'distinct' and that this didn't work, it was still producing records from the same artist id.
The last update was using the join left.