How to Use Random or Suffle Data on MySQL using PHP?

Question

Hello i have tables like this :

Employee

EmployeeID  EmployeeName 
1234        Suho    
1235        Kai    
1236        Baekhyun    
1237        Lay  
1238        D.O      
1239        Chen
1240        Chanyeol
1241        Xiumin
1242        Sehun

i used this source code to make random data :

<?php

mysql_connect("localhost", "root", "1234") or die(mysql_error());
mysql_select_db("Employee") or die(mysql_error());

$employees = mysql_query("select p.*
from (select (@c := CHAR(ASCII(@c) + 1)) as c, EmployeeName
      from Employee cross join
           (select @c := 'A') params
      order by EmployeeId
     ) p order by rand();") 
or die(mysql_error());  

$letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
$position = 0;
$position2 = 0;
$toomany = '';

while($row = mysql_fetch_array( $employees )) {
    echo "<DIV>" . $toomany.substr($letters, $position, 1) . " = " . $row['EmployeeName'] . " </div>";
      $position ++;
    if($position > 25) {
        $position = 0;
        $position2 ++;
        if($position2 > 25) { echo "We need to rethink this idea."; break; }
        $toomany = substr($letters, $position2, 1);
    }
}

?>

After random the data, the result is like this :

A  = Sehun     
B  = Suho     
C  = Kai     
D  = D.O     
E  = Chen       
F  = Chanyeol
G  = Baekhyun
F  = Lay
H  = Xiumin

or like this :

A  = Kai     
B  = Suho     
C  = Sehun    
D  = D.O     
E  = Chen       
F  = Xiumin
G  = Lay
F  = Chanyeol
H  = Baekhyun

The problem is i want to random that data like this (From the database before, EmployeeID changed by alphabet sequentially):

A = Suho     
B = Kai    
C = Baekhyun    
D = Lay  
E = D.O      
F = Chen
G = Chanyeol
H = Xiumin
I = Sehun

into this one, the data have with the same value but with a different sequence (Only their potition change, not only their names were randomized) :

G = Chanyeol  
C = Baekhyun   
B = Kai  
D = Lay  
I = Sehun
E = D.O      
F = Chen
A = Suho
H = Xiumin

or like this :

I = Sehun
E = D.O  
C = Baekhyun
G = Chanyeol    
B = Kai 
H = Xiumin 
D = Lay        
F = Chen
A = Suho

May you know where is the problem? Thank you for your help.

Why don't you shuffle in PHP? You can store the unshuffled results in an associative array, using the letter as KEY, and the name (Sehun, etc) as value. For a simple associative array shuffle, see first comment here: php.net/manual/en/function.shuffle.php — Erwin Moller
– Erwin Moller, Commented Jul 9, 2015 at 8:18
How is that supposed to work with more than 26 rows? I see you wrote support for that. For example only ABC in $letters, give an example of output with 7 rows. — OIS
– OIS, Commented Jul 9, 2015 at 8:19
Just a notice: Don't use ORDER BY RAND() on large datasets. They tend to get very slow very quick. Better to select a random range in php and randomize in php rather than let MySQL do it... — Raphioly-San
– Raphioly-San, Commented Jul 9, 2015 at 8:20

SpongePablo · Accepted Answer · 2015-07-09 08:20:57Z

0

Get all from the DB and keep it into a PHP Array, then apply one of the randomize PHP Functions. Shuffle changes the keys, so I saw someone who posted a nice function in the PHP.net website which keeps the indexes:

<?php
    function shuffle_assoc(&$array) {
        $keys = array_keys($array);

        shuffle($keys);

        foreach($keys as $key) {
            $new[$key] = $array[$key];
        }

        $array = $new;

        return true;
    }
?>

answered Jul 9, 2015 at 8:20

SpongePablo

88810 silver badges24 bronze badges

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

1 Comment

TARA Over a year ago

i have tried this one too, but nohing happened you can check in here stackoverflow.com/questions/31282438/… thank you

Collectives™ on Stack Overflow

How to Use Random or Suffle Data on MySQL using PHP?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related