Set value in groups of 25

Question

I am trying to assign a value to a field in a database in groups of 25. For example, the first 25 are assigned the value of 1, the next 25 a value of 2 etc. I have tried to do this but I end up just looping through the whole thing giving everyone the same number.

Here is what I have so far...

$i = 1;
    while(($count > 0) && ($i < 10)){
        $sql = "UPDATE tbl_studentModules SET groupID = $i WHERE groupID = '' LIMIT 25";
        $result = mysql_query($sql) or die(mysql_error());
        $count = mysql_num_rows($result);
        $i++;
    }

Any help will be gratefully received.

Thanks

I'd use the OFFSET in combination with LIMIT – jtheman Nov 28 '12 at 22:24 — jtheman, Nov 28 '12 at 22:24

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

3

$sql = "UPDATE tbl_studentModules SET groupID = MOD(rowId, 25) WHERE groupID = ''";
$result = mysql_query($sql) or die(mysql_error());

Try using MOD like above, it will assign every 25 users with a groupID ranging from 0 - 24 based on their rowId (assuming that is the field name of the row's ID).

No need for the while loop.

Also important:

Please, don't use mysql_* functions in new code. They are no longer maintained and the deprecation process has begun on it. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 28 '12 at 22:23

Naftali

144,921
39
244
303

Hi, thanks for that, unfortunately the unique ID in this table are varchar so the code doesn't work. Thanks for the advice re the mysqli – tatty27 Nov 28 '12 at 22:43
@tatty27 please use a INTEGER id.... Do not rely on VARCHAR, it is not always reliable. – Naftali Nov 28 '12 at 22:45
The database is from an external source that I am not allowed to change – tatty27 Nov 28 '12 at 23:03

Set value in groups of 25

1 Answers1

Also important: