Php random row help

Question

I've created some code that will return a random row, (well, all the rows in a random order) But i'm assuming its VERY uneffiecent and is gonna be a problem in a big database...

Anyone know of a better way?

Here is my current code:

$count3 = 1;
$count4 = 1;
//Civilian stuff...
$query = ("SELECT * FROM `*Table Name*` ORDER BY `Id` ASC");
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
$count = $count + 1;
$civilianid = $row['Id'];
$arrayofids[$count] = $civilianid;
//echo $arrayofids[$count];
}

while($alldone != true) {
$randomnum = (rand()%$count) + 1;
//echo $randomnum . "<br>";
//echo $arrayofids[$randomnum] . "<br>";
$currentuserid = $arrayofids[$randomnum];
$count3 += 1;

while($count4 < $count3) {
$count4 += 1;
$currentarrayid = $listdone[$count4];
//echo "<b>" . $currentarrayid . ":" . $currentuserid . "</b> ";
if ($currentarrayid == $currentuserid){
$found = true;
//echo " '" .$found. "' ";
}
}

if ($found == true) {
//Reset array/variables...
$count4 = 1;
$found = false;
} else {
$listdone[$count3] = $currentuserid;
//echo "<u>" . $count3 .";". $listdone[$count3] . "</u> ";
$query = ("SELECT * FROM `*Tablesname*` WHERE Id = '$currentuserid'");
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$username = $row['Username'];
echo $username . "<br>";
$count4 = 1;
$amountdone += 1;
if ($amountdone == $count) { //$count
$alldone = true;
}
}
}

Basically it will loop until its gets an id (randomly) that hasnt been chosen yet. -So the last username could take hours :P

Is this 'bad' code? :P :(

If your confused why all variables start at 1, and not 0 like the array would, I don't know, I just decided they all should :P — Skillman
– Skillman, Commented Jun 10, 2010 at 10:29
Ignore most of the comments, they were just helping me identify problems when I previewed it... — Skillman
– Skillman, Commented Jun 10, 2010 at 10:33

Community · Accepted Answer · 2017-05-23 10:33:15Z

1

You could delegate that to MySQL:

SELECT * FROM table_name ORDER BY RAND() LIMIT 1;

It will return a random row from your table, which I guess should be more efficient than your php solution. Nevertheless, it would still be slow within a large database.

You may be interested in checking out the following Stack Overflow posts for alternative solutions:

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Jun 10, 2010 at 10:32

Daniel Vassallo

346k72 gold badges514 silver badges447 bronze badges

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

3 Comments

Skillman Over a year ago

Is that 'effecient' -or whatever, -in a big database, will that run slow? Edit: Nvm, you've updated

Daniel Vassallo Over a year ago

It's not efficient, because it requires a full table scan. Check out this post and this one.

Rik Heywood Over a year ago

@Skillman It is waaaaaay more efficient than the method you are using.

Rik Heywood · Accepted Answer · 2010-06-10 10:38:09Z

0

You could either change the query to give you all the results in a random order...

$query = ("SELECT * FROM `*Table Name*` ORDER BY RAND()");

Then just display them all in a simple loop.

Or you could get all the results into an array, then randomise the order in the array. You can use the php shuffle() function for that. http://www.php.net/manual/en/function.shuffle.php

I would assume that the first option would give you the best results, but the correct solution will be to measure the performance and try to optimise if it is "too slow".

However, picking a random element from your array, seeing if you have done that one already and trying again if you have is horrific. Do anything except that.

answered Jun 10, 2010 at 10:38

Rik Heywood

14k10 gold badges65 silver badges82 bronze badges

1 Comment

Skillman Over a year ago

Shuffle() sounds good for me, but yes, I could always try both. Thanks,

Collectives™ on Stack Overflow

Php random row help

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related