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

问题描述：

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.

我有一个MySQL表，其中的列包括id，人名和随机数。我想做的是每周，将名字收集成5个随机组。这是5场橄榄球锦标赛的现在，现在手动做得太大了，我希望它是自动的。 p>

我认为它的工作方式将是获得 num_rows，除以5得到组的总数（$ divide）。然后创建一个循环，随机选择5行，并给出1和$ divide之间的随机数。它必须每周更换，因此它不能是一次性的任务。我也希望它适应num_rows没有完全除以5并创建剩余数字的最后一组的情况。 p>

这是我所拥有的 - p>

  $ num_rows = mysql_num_rows（$ data）; 
 
 $ divide = $ num_rows / 5; 
 
 $ rannum = RAND（1，$ divide）;  
 
 $ sql = mysql_query（“UPDATE allpeep SET rannumber = $ rannum”）
ot die（mysql_error（））; 
  code>  pre> 
 
 但是你可能有 猜测，这只是在所有行中插入相同的随机数。 我认为 em>这需要一个while循环，但我无法弄清楚它是如何工作的。 p> 
  div>

答

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']";
  }
}

答

Here's a similar thread that should help you. Assign a Unique Random Number to Each Row

If you setup a script to run this query, you can run it using a cronjob anytime you want.

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

相关推荐