php mysql random rows no duplicates

Question

Hi im trying to get 5 random rows from a database and then display them. i currently do this but it does result in duplicates.

i need to change the limit to 5 and then store them in an array but how do i do that? or is there a better way?

function GetPlayer($link){
    if (isset($_SESSION['username'])) {
    $x = 0; 
    while($x <= 5) {
        $sql = "SELECT * FROM userstats ORDER BY RAND() LIMIT 1; ";
        $result = mysqli_query($link,$sql);
        $row = mysqli_fetch_assoc($result);
        if($row['username'] !== $_SESSION['username']){//add so it dosent put duplicates
            echo ("<tr>");
            echo ("<th>".$row['username']." </th>");
            echo ("<th>Level: ".$row['Level']." </th>");
            echo ("<th>Player Stats:".$row['Attack']."/".$row['Defence']." </th>");
            echo ("<th>Win Chance: ");
            echo CalculateWinChance($link,$row['Defence']);
            echo ("<th><input type ='submit' name = 'Attack_Btn' value ='Attack'></th>");
            echo ("</tr>");
            $x++;
        }
    }
    }
}

can i ask another question while im here. how would i make the submit button unique to each row? so i can use it to pass variables — GregHBushnell, Sep 16 '16 at 16:47
Possible duplicate of [MySQL select 10 random rows from 600K rows fast](http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast) — Dana Cartwright, Sep 16 '16 at 18:19

score 2 · Accepted Answer · answered Sep 16 '16 at 16:37

2

Why dont't you try to request 5 results (LIMIT 5) AND loop this? It will no return any duplicates. Four queries less would be a side effect.

$sql = "SELECT * FROM userstats ORDER BY RAND() LIMIT 5; ";
while($row = mysqli_fetch_assoc($result)){
...
}

answered Sep 16 '16 at 16:37

degers

108
9

1

thank you exactly what i was looking for. i was under the impression i would dhave to change the $row = mysqli_fetch_assoc($result) to something else. but now i know it works that way aswell i can use it for other things aswell – GregHBushnell Sep 16 '16 at 16:41

score 0 · Answer 2 · answered Sep 16 '16 at 16:37

Instead of calling the query 5 times within the loop, you should have a single query. That's the most optimal approach.

You can use GROUP BY clause to select unique rows.

Your query should be like the following:

SELECT *
FROM userstats 
GROUP BY username
ORDER BY RAND() 
LIMIT 5

score 0 · Answer 3 · answered Sep 16 '16 at 16:43

You'll want to sanitize the inputs in the query and/or use a prepared statement, but this should get you pretty close to what you want:

$sql = 'SELECT * FROM userstats WHERE username != ? GROUP BY username ORDER BY RAND() LIMIT 5';
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('s', $_SESSION['username']);

php mysql random rows no duplicates

3 Answers3