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
– GregHBushnell, Commented Sep 16, 2016 at 16:47
Possible duplicate of MySQL select 10 random rows from 600K rows fast — Dana Cartwright
– Dana Cartwright, Commented Sep 16, 2016 at 18:19

degers · Accepted Answer · 2016-09-16 16:37:51Z

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, 2016 at 16:37

degers

1089 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GregHBushnell Over a year ago

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

Indrasis Datta · Accepted Answer · 2016-09-16 16:37:42Z

0

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

answered Sep 16, 2016 at 16:37

Indrasis Datta

8,6182 gold badges18 silver badges32 bronze badges

Comments

chimericdream · Accepted Answer · 2016-09-16 16:43:22Z

0

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']);

answered Sep 16, 2016 at 16:43

chimericdream

4421 gold badge3 silver badges15 bronze badges

Collectives™ on Stack Overflow

php mysql random rows no duplicates

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related