Distinct query problem

[overflo]

I have a table that with the fields ID, workerID and photo. Each worker can have multiple photos but I need a query to grab one of each worker randomly, so that only one photo is shown of each worker in a gallery, and in random order. I have this code but it is only grabbing the first instance of each worker and then displaying the photos in that order.

$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

Any suggestions would be greatly appreciated

 

[jpadie]

your code looks wrong in that you are using this line

list($workerID, $photo) = mysql_fetch_row($get);

to return two items from the fetch_*. but your query contains only one item (photo). and you already know the workerID from the first query so i'm not sure why you'd need that again.

i would also be surprised that the while loop worked in the way that you expect as list() does not return a value. maybe php is flexible enough to test the while loop against the right hand side of the evaluation but i would rather be safe and do something like this

while ($row = mysql_fetch_array($sql)){
$workerID = $row[0];
//...
}

 

[overflo]

that was s'posed to be

list($photo)=mysql_fetch_row($get);
(Thanks for pointing that out!)

I tried your way and got the same result but I just realised what makes it work

$sql = mysql_query("SELECT ID
                   FROM tblworker
                   ORDER BY RAND()");
      while(list($workerID)=mysql_fetch_row($sql)){
                     
      
         $get = mysql_query("SELECT photo
                        FROM tblphoto
                        WHERE workerID = '$workerID'
                        ORDER BY RAND() LIMIT 1");
         while(list($photo)=mysql_fetch_row($get)){
            $content = "<img src=$photo />";

         }//end while
               
      }//end while

Works like a dream!!

Thanks for your input.

Cheers