通过使用php将随机数插入mysql表，将条目分组为5个随机组

Question

I have a MySQL table with columns including id, names of people and a random number. What I would like to do is every week, collect the names into random groups of 5 each. It's for 5 aside football tournaments and it's getting too big to do it by hand now and I'd like it to be automatic.

The way I think it'll work will be to get the num_rows, divide by 5 to get the total number of groups ($divide). Then make a loop where 5 rows are selected at random and a random number between 1 and $divide is given to them. It's got to change every week so it can't be a one off task. I'd also like it to accommodate situations where the num_rows doesn't exactly divide by 5 and creates a last group of the remaining number.

This is as far as I've got -

$num_rows = mysql_num_rows($data);

$divide = $num_rows / 5;

$rannum = RAND (1, $divide);

$sql = mysql_query("UPDATE allpeep SET rannumber = $rannum")
or die(mysql_error());

but as you may have guessed, this just inserts the same random number into all the rows. I think this requires a while loop, but I can't quite work out how it'll work.

Answer 1

SELECT * FROM TABLE ORDER BY RAND() LIMIT 5 

But this still isn't going to protect from duplicate if multiple queries are made, so ...

SELECT * FROM TABLE ORDER BY RAND()

Get the whole set, then parse into 5's in your server script. I'm assuming you want these static until next week, so for each 5 set update the sql server telling them what 'group' they are in this week.

In the alternative, you could use the former syntax if you use a seed for the rand() function. Using the year/week of year combination will give you a reproducable rand sequence

SELECT * FROM TABLE ORDER BY RAND(YEARWEEK(CURDATE()))

Then you avoid having to update anything on the server, and only have to output the results

$result = mysql_query("SELECT * FROM {tablename} ORDER BY RAND(YEARWEEK(CURDATE()))")
or die(mysql_error());

$group = 1;
$row = true;
while ($row) {
  echo "Group $group";
  for ($i=0; $group++, $i<5 && $row=mysql_fetch_assoc($result); $i++) {
    // output current row
    echo "team $row['team']";
  }
}