I'm working on a 'social sharing' kind of thing for music, and I just ran in to a problem. My friends table is structured like this:

|   id   |     f1    |    f2   |   status   |
----------------------------------------------
| 000001 | username1 |username2|     0      |
| 000002 | username4 |username7|     1      |

My rates table is structured like this:

| id | username | songname | songid | plus | minus |
----------------------------------------------------
|0001| username1| Songname | 000001 | 0001 | 00000 |
|0002| username3| Song2222 | 000002 | 0000 | 00001 |

And so what i'm trying to do, is to get a random list of 3 friends where the status is 1, and then query the rates table for each random friend's most recent rate where plus = 1. 'Recency' (if you will) is based on the ID which is auto incremental.

That part isn't the really tough bit, lol. The tough bit, is that the user could be in EITHER f1 OR f2, so the query needs to contain an OR, and if the user is in f1, it'll need to get the corresponding f2, and vice versa.

Then check if they are friends and status=1, then query rates for the most recent rates by the 3 random friends. Then download those data bits, then write 3 strings similar to the example below

<a href="profile.php?u=username3">username 3</a> +1'd <a href="song.php?id=2">Song2222</a>

If anyone would know how to write a script/query like this in PHP/MySQL, I'd be really grateful! Haha

Thanks! :)

Edit* So far I've got virtually just the first bit, returning 3 random users and choosing the other user.

    <?
session_start();
$user = $_SESSION['username'];
$socialmusicquerydata = mysql_query("SELECT * FROM friends WHERE (f1='$user') OR (f2='$user') LIMIT 3");
    if(mysql_num_rows($socialmusicquerydata)!=0)
        {
            while($row = mysql_fetch_array( $socialmusicquerydata )) {
    if($row['f1']==$user) {
        $queryuser = $row['f2'];
        }
        if($row['f2']==$user) { 
        $queryuser = $row['f1']; 
        }
        }
        }

Originally asked by: Karan K on Stack Overflow