Each row has the same random number being inserted

Question

I'm looking to insert random numbers into each column in a database.

The issue that is occuring is that each column for some reason has the same random generated value being passed inside.

$sql = "SELECT * FROM players";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // output data of each row
  $targScore = 3;
  while($row = $result->fetch_assoc()) {

    $target = 3; // The target number the sequence is adding up to
    $n = 4; // Max numbers within sequence
    
    while ($n) {
        if (1 < $n--) {
            $addend = rand(0, $target - ($n - 1));
            $target -= $addend;
            $num[] = $addend;
        } else {
            $num[] = $target;
        }
    }

    /* Rows to update */
    /* Output looks like:
        [0] = 1
        [1] = 2
        [0] = 0
        [0] = 1
    */
    $changeScore = "UPDATE players SET doja='$num[0]' WHERE score='$targScore'";
    $conn->query($changeScore);

    $changeScore = "UPDATE players SET 7acres='$num[1]' WHERE score='$targScore'";
    $conn->query($changeScore);

    $changeScore = "UPDATE players SET tweed='$num[2]' WHERE score='$targScore'";
    $conn->query($changeScore);

    $changeScore = "UPDATE players SET bickel='$num[3]' WHERE score='$targScore'";
    $conn->query($changeScore);
  }
}

The data looks like: https://imgur.com/a/nhhZDVo

What I need is that each loop, it will run the randomizer again but change the numbers its inserting so that each row will have different numbers.

Barmar · Answer

You need to first get a list of all the rows that meet the score condition. Then you have to update them one by one, so you can set different random values in each.

$select_stmt = $conn->prepare("SELECT id FROM players WHERE score = ?");
$select_stmt->bind_param("i", $targScore);
$select_stmt->execute();
$res = $select_stmt->get_result();
$update_stmt = $conn->prepare("UPDATE players SET doja = ?, 7acres = ?, tweed = ?, bickel = ? WHERE id = ?");
$update_stmt->bind_param("iiiii", $doja, $acres, $tweed, $bickel, $id);

$target = 3;

while ($row = $res->fetch_assoc()) {
    $n = 4;
    $num = [];
    while ($n) {
        if (1 < $n--) {
            $addend = rand(0, $target - ($n - 1));
            $target -= $addend;
            $num[] = $addend;
        } else {
            $num[] = $target;
        }
    }
    [$doja, $acres, $tweed, $bickel] = $num;
    $id = $row['id'];
    $update_stmt->execute();
}

There's also no need to use four UPDATE statements to update 4 columns in the same rows.

userncg4 · Answer

MySQL has a rand() function https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_rand

(Note the warning that it's not perfectly random / security secure, but nor is PHPs rand()).

If you do not need to know what the values are, you can simply do UPDATE myCol=FLOOR(RAND() * 200) WHERE myConditionalField=MyCondition

This will allocate a random number from 0 to 199; examples in the MySQL manual (linked above) as to how to set a range, e.g. FLOOR(7 + (RAND() * 5)) gives an integer from 7 to 11.

Each row has the same random number being inserted

2 Answers