Complicated MySQL Query?

Question

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 3* While waiting for responses, I worked out this code, which partially works!

<? session_start();
$user = $_SESSION['username'];

mysql_connect("localhost", "xxxxx", "xxxx") or die(mysql_error());
mysql_select_db("xxxxxx") or die(mysql_error());

$q1data = mysql_query("SELECT * FROM friends WHERE (f1='$user') OR (f2='$user') AND status=1 ORDER BY RAND() LIMIT 1") ;
    if(mysql_num_rows($q1data)===1)
            {
                $q1result = mysql_fetch_assoc($q1data);
                $q1f1 = $q1result['f1'];
                $q1f2 = $q1result['f2'];
                    if($q1f1==$user) {
                        $oq2un1 = $q1f2; 
                        } 
                    if($q1f2==$user) { 
                    $oq2un1 = $q1f1;
                    }
            } 
$q2data = mysql_query("SELECT * FROM friends WHERE (f1='$user') OR (f2='$user') AND status=1 ORDER BY RAND() LIMIT 1") ;
    if(mysql_num_rows($q2data)===1)
            {
                $q2result = mysql_fetch_assoc($q2data);
                $q2f1 = $q2result['f1'];
                $q2f2 = $q2result['f2'];
                    if($q2f1==$user) {
                        $oq2un2 = $q2f2; 
                        } 
                    if($q2f2==$user) { 
                    $oq2un2 = $q2f1;
                    }
            } 
$q3data = mysql_query("SELECT * FROM friends WHERE (f1='$user') OR (f2='$user') AND status=1 ORDER BY RAND() LIMIT 1") ;
    if(mysql_num_rows($q3data)===1)
            {
                $q3result = mysql_fetch_assoc($q3data);
                $q3f1 = $q3result['f1'];
                $q3f2 = $q3result['f2'];
                    if($q3f1==$user) {
                        $oq2un3 = $q3f2; 
                        } 
                    if($q3f2==$user) { 
                    $oq2un3 = $q3f1;
                    }
            } 

/************************************* SECOND SET OF QUERIES ******************************************/

$q4data = mysql_query("SELECT * FROM rates WHERE username='$oq2un1' AND plus=1 ORDER BY id LIMIT 1");
if(mysql_num_rows($q4data)===1)
            {
                $q4result = mysql_fetch_assoc($q4data);
                $finalusername1 = $q4result['username'];
                $q4songid = $q4result['song_id'];
                $q4songname = $q4result['songname'];
            }
$q5data = mysql_query("SELECT * FROM rates WHERE username='$oq2un2' AND plus=1 ORDER BY id LIMIT 1");
if(mysql_num_rows($q5data)===1)
            {
                $q5result = mysql_fetch_assoc($q5data);
                $finalusername2 = $q5result['username'];
                $q5songid = $q5result['song_id'];
                $q5songname = $q5result['songname'];
            }
$q6data = mysql_query("SELECT * FROM rates WHERE username='$oq2un3' AND plus=1 ORDER BY id LIMIT 1");
if(mysql_num_rows($q6data)===1)
            {
                $q3result = mysql_fetch_assoc($q6data);
                $finalusername3= $q6result['username'];
                $q6songid = $q6result['song_id'];
                $q6songname = $q6result['songname'];
            }
$socialmuze_string1 = $finalusername1." recently <font color='#00FF00'>+1'd</font> <a href='song?id=".$q4songid."'>".$q4songname."</a><br>";
$socialmuze_string2 = $finalusername2." recently <font color='#00FF00'>+1'd</font> <a href='song?id=".$q5songid."'>".$q5songname."</a><br>";
$socialmuze_string3 = $finalusername3." recently <font color='#00FF00'>+1'd</font> <a href='song?id=".$q6songid."'>".$q6songname."</a><br>";

echo $finalusername1." ".$q4songname."<br>";
echo $finalusername2." ".$q5songname."<br>";
echo $finalusername3." ".$q6songname."<br>";

?>

Answer 1

I think it might help modifying the friends query such that it only returns the friends. Picking up portions from your code example, this is how I'd suggest doing it:

session_start();
$user = $_SESSION['username'];

// retrieve random 3 friends
$rsFriends = mysql_query('SELECT `id`,
        (CASE
        WHEN `f1` = \'' . mysql_real_escape_string($user) . '\' THEN `f2`
        WHEN `f2` = \'' . mysql_real_escape_string($user) . '\' THEN `f1`
        END) AS `friend`
    FROM `friends`
    WHERE `status` = 1
    AND (`f1` = \'' . mysql_real_escape_string($user) . '\' 
         OR `f2` = \'' . mysql_real_escape_string($user) . '\')
    ORDER BY RAND()
    LIMIT 3');
while ($row = mysql_fetch_assoc($rsFriends)) {
    // retrieve the most recent rate entry where plus = 1
    $rsRates = mysql_query('SELECT `id`, `username`, `songname`, `songid`, `plus`, `minus`
        FROM `rates`
        WHERE `username` = \'' . mysql_real_escape_string($row['friend']) . '\'
        AND `plus` = 1
        ORDER BY `id` DESC
        LIMIT 1');
    while ($row1 = mysql_fetch_assoc($rsRates)) {
        // $row1 is the array that contains the most recent rate where plus = 1
    }

}

The benefit with modifying the friends query is that it will always give you the name of the friend in a single column. There are other ways to write the friends query, like using UNION may be, but I think this one is simple and should work as nicely.

I haven't tested the code above, so please feel free to fix it if in case I've used wrong table or column names. Also, though the above code sample is keeping in lines with your example, you might as well do the above in a single query using JOINs.

Hope this helps!

Answer 2

Let's work on the SQL part first.

In your comment, you said you are using

SELECT * FROM friends WHERE (f1='$user') OR (f2='$user') LIMIT 3

You also said you wanted to get a random sample. MySQL has the construct ORDER BY RAND() so that your first 3 will be randomized.

Let's figure this out from the inside out.

If you issued this Select statement:

SELECT * FROM friends where status=1 and (f1='$user' OR f2='$user') order by RAND() limit 3

Then I think you would get 3 random rows from the friends table where the status=1.

(This would be much easier if friends were normalized and f1 and f2 were a single column with an additional column indicating how f1 and f2 were different. As it stands, there is a computationally expensive way to get these two columns into one, assuming they are the same type, say varchar(13).)

SELECT f1 FROM friends where status=1 and f1='$user' order by RAND() limit 3
union all
SELECT f2 FROM friends where status=1 and f2='$user' order by RAND() limit 3

That will get you 6 usernames that are called $user.

Working your way out, you will need a query to surround the first, making a subselect statement. To do the next step, you might consider:

SELECT * from rates where username in (
  SELECT f1 FROM friends where status=1 and f1='$user' order by RAND() limit 3
  union all
  SELECT f2 FROM friends where status=1 and f2='$user' order by RAND() limit 3
)

Again, this would not be quite so complex if you were to normalize the friends table.