There are 2 different ways to select and display 15 friends' picture randomly. 1 is using mysql ORDER BY RAND(). Another is select and save all usernames into a php array then select 15 value randomly from the array.

1 - Using mysql ORDER BY RAND(). I saw many people said don't use ORDER BY RAND() from google search. The problem of ORDER BY RAND() is that as sql explain tells you the "Using temporary" and the "Using filesort". For each request a temporary table is created and sorted. Thats a pretty heavy operation. It will probably not matter when your database is not under heavy load but it will cost a lot of performance. I have discuss about it on Why don't use mysql ORDER BY RAND()?. Anyway, if you still want to use mysql ORDER BY RAND(), here is the codes :
    $friendsArray = array();
    $sql_output = array();
    $frenusernameArray = array();
    $picmediumArray = array();
    $query120 = "SELECT frenusername FROM friendship WHERE username='{$username2}' UNION SELECT username FROM friendship WHERE frenusername='{$username2}' ORDER BY RAND() LIMIT 15";
    $result120 = mysql_query($query120,$connection) or die (mysql_error());
    confirm_query($result120);
    while($userinfo120 = mysql_fetch_array($result120)){
        $friendsArray[] = $userinfo120['frenusername'];
    }
   
    foreach($friendsArray as $friend) {
        $sql_output[] = "'".mysql_real_escape_string($friend)."'";
    }
    $sql_output = join(', ',$sql_output);
   
    $query120 = "SELECT username, picturemedium FROM users WHERE username IN ($sql_output)";
    $result120 = mysql_query($query120,$connection) or die (mysql_error());
    confirm_query($result120);
    while($userinfo120 = mysql_fetch_array($result120)){
        $frenusernameArray[] = $userinfo120['username'];   
        $picmediumArray[] = $userinfo120['picturemedium'];   
    }
   
    for ($i = 0; $i < count($frenusernameArray); $i++) {   
        echo "<a $frenusernamearray[$i]="" .="" \="" href="http://www.blogger.com/%5C%22user-profile.php?username=%22" target="\"_blank\""><img $picmediumarray[$i]="" .="" \="" class="\"frenpic\"" src="%5C%22images/users/%22" /></a>";
    }


2 - Using shuffle values in php array. Although this way avoid using mysql ORDER BY RAND() to prevent database heavy load problem, but it still have problem with php array memory heavy load problem. I have discuss it on confusing foreach loop show values of arrays. Here is the codes :
    $friendsArray = array();
    $sql_output = array();
    $frenusernameArray = array();
    $picmediumArray = array();
    $query120 = "SELECT frenusername FROM friendship WHERE username='{$username2}' UNION SELECT username FROM friendship WHERE frenusername='{$username2}' LIMIT 30";
    $result120 = mysql_query($query120,$connection) or die (mysql_error());
    confirm_query($result120);
    while($userinfo120 = mysql_fetch_array($result120)){
        $friendsArray[] = $userinfo120['frenusername'];
    }
   
    shuffle($friendsArray);
    $rand = array_slice($friendsArray, 0, 15);


Both ways also have its own problem. To reduce the problem, we can use precompute 15 fixed sets, each set has 15 random friends, then save 15 fixed sets into database. So next time we just need to random select from the 15 fixed sets, don't need to select from 5000 records anymore. Here is the codes :
$query120 = "SELECT COUNT(*) FROM friendship WHERE username='{$username2}' OR frenusername='{$username2}'";
$result120 = mysql_query($query120,$connection) or die (mysql_error());
confirm_query($result120);
while($userinfo120 = mysql_fetch_array($result120)){
    $friendshipTotalRows = $userinfo120['COUNT(*)'];
}
if ($friendshipTotalRows < "50" AND $fixedSetLabel != "50"){
    //precompute 15 fixed sets, each set has 15 ramdom friends, save the 15 fixed sets to database, insert value "50" to label field too.
}elseif ($friendshipTotalRows > "100" AND $friendshipTotalRows < "200" AND $fixedSetLabel != "100"){
     //precompute again
}elseif.....


Posted by Zac1987 on 08 July, 2011

0 comments








Enter your email address:

Subscribe in a reader

Follow zac1987 on Twitter

Chatting Box

Donation

If you feel my website is informative and it is useful for you, please donate some money to support me or buy me a drink. I will continues this good works. Thank you.