Enter the same random number into only 5 rows of mysql, and loop through to do this so that enteries are randomly grouped into 5

[toastiepie]

Hi All,

 

I'm looking to update a mysql column weekly, where by 5 randomly selected rows are given a new random number, from between 1 and the num_rows / 5. This cycle is looped again until all the entries have a new random group for the week. It is to help mix up the players for teams for 5 aside. It needs to be able to accomodate the posibillity of the num_rows not dividing exactly by 5, using ceil perhaps?

 

As an example

 

Name Group 

a 1

b 2

c 1

d 1

e 1

f 3

g 2

h 1

i 2

j 2

k 2

 

Then Next week

Name Group 

a 2

b 1

c 2

d 2

e 1

f 2

g 3

h 1

i 2

j 1

k 1

 

I hope that makes sense. It'll be automated via cron job.

I've been really struggling with it, this is as far as I have got.

 

$totalgroupsraw = $num_rows /5;

 

$totalgroups = ceil ($totalgroupsraw); 

 

 

$i = 1;

 

while ($i<$totalgroups) {

 

$pie = mysql_query("UPDATE table SET columnname=5  order by rand() LIMIT 5");

 

$i++;

 

};

 

But as you will no doubt be able to see, this doesn't work well at all. I think I am close, but I just can't seem to sort out a good way to do it. Any help much appreciated. Thanks, Matt

 

 

[Psycho]

ORDER BY RAND() is highly discouraged. If you have a small amount of data and it is not being run on page requests by users you can probably get away with it. But, it builds bad habits.

 

Getting more than 1 'random' row from MySQL is not a simple task as you've found. I have an idea that should work, although others may have a better idea.

 

1. Insert a random number into all the records with a single query. This value will be less than 1, e.g. 0.79399610850401

 

2. Create a loop with an incrementing value starting at 1. In that loop run a query to UPDATE 5 records using the random value above as the ORDER BY and in the WHERE condition. Continue the loop as long as there are affected rows

 

Sample code

 

//Update team values to random number
$sql = "UPDATE players SET team = RAND()";
$mysqli->query($sql);
 
//Create var to hold team number
$teamNo = 0;
 
do
{
    //Increment team number
    $teamNo++;
 
    //Run query to update 5 rows based on random value
    $sql = "UPDATE players
            SET team = {$teamNo}
            WHERE team < 1
            ORDER BY team
            LIMIT 5";
    $mysqli->query($sql);
 
  //Continue loop as long as there were rows updated
} while($mysqli->affected_rows);

 

I hate running queries in loops, but don't see a good way to do this otherwise. You would have performance issues if you were to have a LOT of data.

[Barand]

try

$db = new mysqli(HOST,USERNAME,PASSWORD,'test');

$res = $db->query("SELECT COUNT(DISTINCT name) FROM toastie");
list($count) = $res->fetch_row();
$N = ceil($count/5);

$db->query("UPDATE toastie SET groupnumber = FLOOR(1 + RAND() * $N)");

Gives

 

 

 

[Psycho]

@Barand,

 

If I understand the requirements correctly, each group number should have exactly 5 records, except for the last group number which will have from 1-5 records (based on the total number). I don't believe the query you've provided has any logic to enforce the distribution in that manner.

[Barand]

Oops! You're right. I was trying to avoid running queries in loops.

 

if the table is this:

CREATE TABLE `players` (
  `player_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `team` int(11) DEFAULT NULL,
  PRIMARY KEY (`player_id`)
)

then when running your first query

mysql> SELECT *  FROM players;
+-----------+------+------+
| player_id | name | team |
+-----------+------+------+
|         1 | A    | NULL |
|         2 | B    | NULL |
|         3 | C    | NULL |
|         4 | D    | NULL |
|         5 | E    | NULL |
|         6 | F    | NULL |
|         7 | G    | NULL |
|         8 | H    | NULL |
|         9 | I    | NULL |
|        10 | J    | NULL |
|        11 | K    | NULL |
+-----------+------+------+
11 rows in set (0.00 sec)

mysql> UPDATE players SET team = RAND();
Query OK, 11 rows affected (0.00 sec)
Rows matched: 11  Changed: 11  Warnings: 0

mysql> SELECT *  FROM players;
+-----------+------+------+
| player_id | name | team |
+-----------+------+------+
|         1 | A    |    0 |
|         2 | B    |    0 |
|         3 | C    |    1 |
|         4 | D    |    1 |
|         5 | E    |    0 |
|         6 | F    |    1 |
|         7 | G    |    1 |
|         8 | H    |    1 |
|         9 | I    |    1 |
|        10 | J    |    1 |
|        11 | K    |    0 |
+-----------+------+------+
11 rows in set (0.00 sec)

So running the rest of your code updates the zeros to ones, putting everyone in team 1.

 

See you back at the drawing board

[Barand]

Plan B

$sql = "SELECT player_id FROM players";
$res = $mysqli->query($sql);
$players = array();
while ($row = $res->fetch_row() ) {
    $players[] = $row[0];
}
shuffle($players);
$teams = array_chunk($players, 5);

foreach ($teams as $k => $team) {
    $tm = $k+1;
    $members = join(',', $team);
    $mysqli->query("UPDATE players SET team = $tm WHERE player_id IN ($members)");
}

If you needed a single-query update because of volume you could generate a query like this using the above chunks

UPDATE players 
    SET team =
    CASE WHEN player_id IN (11,2,4,5,7) THEN 1
    CASE WHEN player_id IN (10,9,8,6,1) THEN 2
    CASE WHEN player_id IN (3) THEN 3
    END;

Edited December 3, 2014 by Barand

[kicken]

Something like this seems to work:

 

Assign a random number 0..1 to each record. Used to order the results in the next query

UPDATE players SET randOrder = RAND()

Generate a team number by just using a counter / 5. Order by the previously generated random number in order to randomize the players when generating the team number.

UPDATE players 
INNER JOIN (
    SELECT player_id, FLOOR(@row/5)+1 as team, @row := @row + 1
    FROM players
    CROSS JOIN (SELECT @row := 0) counter
    ORDER BY
        randOrder
) teamGen ON teamGen.player_id = players.player_id
SET 
    players.team = teamGen.team

I used this test script to generate some random data and try it out:

<?php

$generateRecords = isset($argv[1])?intval($argv[1]):0;
$db = new PDO('mysql:host=localhost;dbname=', '', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if ($generateRecords > 0){
    $db->exec('DROP TABLE IF EXISTS players');
    $db->exec('
    CREATE TABLE players (
      player_id int(11) NOT NULL AUTO_INCREMENT,
      name varchar(45) NULL,
      team int(11) NULL,
      randOrder NUMERIC(10,10) NULL,
      PRIMARY KEY (player_id)
    )
    ');

    $sql = 'INSERT INTO players (name) VALUES (?)';
    $stmt = $db->prepare($sql);

    function randomName($len){
        $letters = range('a','z');
        shuffle($letters);
        return implode('', array_slice($letters, $len));
    }

    for ( $i=0; $i < $generateRecords; $i++){
        $params = array(randomName(6));
        $stmt->execute($params);
    }
}


//Assign a random number 0..1 to each record
$db->exec('UPDATE players SET randOrder = RAND()');

//Assign a team based on an increasing counter
$db->exec('
UPDATE players 
INNER JOIN (
    SELECT player_id, FLOOR(@row/5)+1 as team, @row := @row + 1
    FROM players
    CROSS JOIN (SELECT @row := 0) counter
    ORDER BY
        randOrder
) teamGen ON teamGen.player_id = players.player_id
SET 
    players.team = teamGen.team
');

[toastiepie]

All three of these read like good answers, but in practice, I'm struggling to get any to work. Thank you for your quick responses, I've gone with Phycho's answer as it reads like the most familiar to what I'm used too at the moment, but I am getting a sytax error ' [1/1] Syntax error, unexpected '{' on Line 15 ' this is the '{' just after the do command.

$generateRecords = isset($argv[1])?intval($argv[1]):0;
$db = new PDO('mysql:host=localhost;dbname=databasename,user,password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


/Update team values to random number
$sql = "UPDATE players SET team = RAND()";
$mysqli->query($sql);
 
//Create var to hold team number
$teamNo = 0;


do
{
    //Increment team number
    $teamNo++;
 
    $sql = "UPDATE players
            SET team = {$teamNo}
            WHERE team < 1
            ORDER BY team
            LIMIT 5";
    $mysqli->query($sql);
 
  //Continue loop as long as there were rows updated
} while($mysqli->affected_rows);

The only thing I've changed is the table name, user and pass to my own, but having always done mysql_connect / mysql_queary functions, I'm having a little trouble getting used to the new PDO style. I'm happy my table is good for all examples, I've altered my existing table to match the one suggest by Kicken.

 

To give you an idea of what I do, I c+p from your suggestions to a php file, make the alterations to table name etc then go to this file in my browser and refresh it. I get, as you imagine, a blank page, but nothing changes in the table. I've cleared the cache and tried on a couple of comps in case it was storing the old setting for some reason. Sorry if I am coming across as a super noob, but i've tried for a good 1/2 day with these solutions and can't crack what the issues are.

 

Any further help much appreciated.

 

Thanks,

Toastie

[Psycho]

Look at the 'comment' on Line 6

[toastiepie]

I'm afraid that was just a poor piece of C+P action, sorry. I've been again trying over the weekend, but to no avail. Again, any help most welcome.

[Psycho]

I've been again trying over the weekend, but to no avail. Again, any help most welcome.

 

How are we supposed to continue to help when you are not providing any information. I pointed out what would cause a syntax error and you state that it was a Copy/Paste error. I don't see how you can get a copy/paste error in the middle of the content unless you are copy/pasting only sections of the code. If so, it is impossible to help. A syntax error is typically on a line before the line on which the error is reported. The reason is that the PHP parser is not a human, it will continue reading/processing code until it come to something that it cannot make sense of. For example, if you forget the closing quote mark when defining a string, the PHP parser will assume all the content following it is part of that string.

 

$foo = "This is a string
echo $foo;
$bar = "this is another string";

 

In that code, the syntax error would be reported on line 3, even though the real error is on line 1. The reason is that since the string was not terminated the PHP parser will continue to read the following code as part of that string. It is not until line three that it finds a "closing" quote mark. Then, after that quote it would attempt to parse the remaining content as PHP code

 

this is another string";

That would cause the syntax error on line 3

[toastiepie]

$sql = "SELECT player_id FROM players";
$res = $mysqli->query($sql);
$players = array();
while ($row = $res->fetch_row() ) {
    $players[] = $row[0];
}
shuffle($players);
$teams = array_chunk($players, 5);

foreach ($teams as $k => $team) {
    $tm = $k+1;
    $members = join(',', $team);
    $mysqli->query("UPDATE players SET team = $tm WHERE player_id IN ($members)");
}

Reet, we are sorted! Thank you so much all. Final code I went for is above - sorry for all the problems, the issue lies with my host not supporting the new PDO connection. I tried mysqli and am happy with the results - Barand's answer worked for me. Thanks for being so patient and sorry again for the confusion.