Php MySQL Nonrepeating Random Data Display

Question

Let's say I've got a mySQL database: two columns: ID and names, with 100 rows. What I'd like to do is pick out say 10 names - with no repeats - and display them in random order.

Using this code, I can pick out just one name...

<?php
include('connection.php');

$rand = rand(1, 100);
$sql = "SELECT * FROM names WHERE ID=$rand";
$result = mysql_query($sql, $sandbox);

while ($row = mysql_fetch_array ($result)) 
{
$name1 = $row['Name'];
echo $name1 . "<br>";
}

?>

... what would be the best code to get my 10 random nonrepeating names?

Danijel · Answer 1 · 2013-06-06T12:19:52.730

The easiest way (and fastest for the database) would be to generate ten unique numbers using range and shuffle functions, and then create a query with the IN clause:

$numbers = range(1, 100);
shuffle($numbers);
$numbers = implode( ', ', array_slice($numbers, -10) );

$sql = "SELECT * FROM names WHERE ID IN ($numbers)";
// example query: 
// SELECT * FROM names WHERE ID IN (63, 76, 69, 59, 9, 84, 60, 18, 23, 62)

score 1 · Answer 2 · edited May 23 '17 at 12:18

Depending on the number of rows in the table, you probably don't want to do ORDER BY RAND() as suggested by 4 other people for performance reasons:

The ORDER BY RAND() operation actually re-queries each row of your table, assigns a random number ID and then delivers the results. This takes a large amount of processing time for table of more than 500 rows.

The recommended approach extends your original idea to get 10 IDs and do a simple select query for those.

If they're sequential, you can just generate a random in that range, or retrieve all the IDs, shuffle them and request the first 10 values.

There are some other suggestions in response to this question.

score 0 · Answer 3 · answered May 17 '13 at 22:05

0

$sql = "SELECT DISTINCT id, name FROM names ORDER BY RAND() LIMIT 10";

answered May 17 '13 at 22:05

johnode

720
3
12
31

1

Rather than posting just an SQL snippet, can you provide a description of what it's doing and why? As it stands, this question is flagged as low quality. – Deanna May 17 '13 at 22:27
It's doing exactly what Gregory Tippett asked for: get 10 random no-repeating records from database. I think it's clearly, so no need in repeating question in my answer. – johnode May 18 '13 at 14:10

score 0 · Answer 4 · answered May 17 '13 at 22:05

0

You will want to use ORDER BY RAND()

$sql = "SELECT * FROM names ORDER BY RAND() LIMIT 10";
$result = mysql_query($sql, $sandbox);

while ($row = mysql_fetch_array ($result)){

answered May 17 '13 at 22:05

Tim Withers

12,072
5
43
67

Sounds good... then how do I go about displaying those names? – drenl May 17 '13 at 22:10
Just like you were: `while ($row = mysql_fetch_array ($result)) { $name1 = $row['Name']; echo $name1 . "
"; }` – Tim Withers May 17 '13 at 22:16
Ah, woops, missed a semicolon. Thanks. How would you recommend I integrate this with html if I wanted to style each name individually? (each name getting a unique CSS ID?) – drenl May 17 '13 at 22:24
Why would you do that? There should be some pattern of designing? Styling each record different, will make your page not-so-dynamic, since each new inserted record should have new css styles, so instead of just inserting a new `name` in the database, you will need to open the code again, and style this name. It seems to me a bad practice – Royal Bg May 17 '13 at 22:35
The desired final appearance here is 10 individually styled boxes each with a randomly selected name in it. Those names should not repeat. So if you have another suggestion for how to code that I'd be happy to hear it. – drenl May 17 '13 at 22:43

score 0 · Answer 5 · answered May 17 '13 at 22:20

0

try this :

 SELECT DISTINCT * FROM names ORDER BY RAND() LIMIT 10

answered May 17 '13 at 22:20

medBouzid

7,484
10
56
86

Php MySQL Nonrepeating Random Data Display

5 Answers5