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.

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.

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

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