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.

CodePudding user response：

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.

CodePudding user response：

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.