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?

Community · Accepted Answer · 2017-05-23 12:18:52Z

1

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.

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered May 18, 2013 at 23:22

Deanna

24.4k7 gold badges76 silver badges162 bronze badges

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

Comments

Danijel · Accepted Answer · 2013-06-06 12:19:52Z

1

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)

edited Jun 6, 2013 at 12:19

answered May 17, 2013 at 22:23

Danijel

12.8k5 gold badges43 silver badges57 bronze badges

Comments

johnode · Accepted Answer · 2013-05-17 22:05:35Z

0

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

answered May 17, 2013 at 22:05

johnode

7403 gold badges12 silver badges31 bronze badges

2 Comments

Deanna Over a year ago

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.

johnode Over a year ago

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.

Tim Withers · Accepted Answer · 2013-05-17 22:05:54Z

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, 2013 at 22:05

Tim Withers

12.1k5 gold badges45 silver badges68 bronze badges

5 Comments

drenl Over a year ago

<noob> Sounds good... then how do I go about displaying those names? </noob>

Tim Withers Over a year ago

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

drenl Over a year ago

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?)

Ivan Yonkov Over a year ago

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

drenl Over a year ago

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.

medBouzid · Accepted Answer · 2013-05-17 22:20:11Z

0

try this :

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

answered May 17, 2013 at 22:20

medBouzid

8,54611 gold badges62 silver badges94 bronze badges

Collectives™ on Stack Overflow

Php MySQL Nonrepeating Random Data Display

5 Answers 5

Comments

Comments

2 Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related