overflo Posted September 9, 2008 Share Posted September 9, 2008 I have a table (tblphoto) with 3 fields. ID (pk), workerID (fk) and photo. The table can hold mutliple photos of each worker. What I need a query to do is to randomly choose only one photo for each worker. $get = mysql_query("SELECT DISTINCT workerID, photo FROM tblphoto ORDER BY RAND()"); but it isn't getting only one row for each worker. I am guessing that it is getting the DISTINCT workerID, photo combination but they will all be distinct. Any ideas will be greatly appreciated. Cheers Link to comment https://forums.phpfreaks.com/topic/123383-distinct-problem/ Share on other sites More sharing options...
The Little Guy Posted September 9, 2008 Share Posted September 9, 2008 after RAND() place this: LIMIT 1 Link to comment https://forums.phpfreaks.com/topic/123383-distinct-problem/#findComment-637275 Share on other sites More sharing options...
overflo Posted September 9, 2008 Author Share Posted September 9, 2008 That just gives me one result Link to comment https://forums.phpfreaks.com/topic/123383-distinct-problem/#findComment-637334 Share on other sites More sharing options...
redarrow Posted September 9, 2008 Share Posted September 9, 2008 that rite 1 random photo u asked for from the three fields........ Link to comment https://forums.phpfreaks.com/topic/123383-distinct-problem/#findComment-637337 Share on other sites More sharing options...
overflo Posted September 9, 2008 Author Share Posted September 9, 2008 Yeah, I need one photo for each worker. Have just got it working like this $sql = mysql_query("SELECT ID FROM tblworker"); while(list($workerID)=mysql_fetch_row($sql)){ $get = mysql_query("SELECT photo FROM tblphoto WHERE workerID = '$workerID' ORDER BY RAND() LIMIT 1"); list($workerID, $photo)=mysql_fetch_row($get); }//end while But if anyone has better idea please tell. Cheers Link to comment https://forums.phpfreaks.com/topic/123383-distinct-problem/#findComment-637339 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.