0

I'm inserting data into a MySQL Table using a for loop in PHP. I'm generating a random ID for $botid and then inserting it based on the amount of $botsToAdd.

The loop itself works but the $botid is always inserted with the same value. So on the first iteration of the for loop if it generated an ID of 2147483647 then all subsequent inserts are inserting with 2147483647 instead of the new $botid.

My code:

if ($botsToAdd > 0) {

    //Disable autocommit
    mysqli_autocommit($con, FALSE);

    //For loop for $botsToAdd
    for ($i = 0; $i < $botsToAdd; $i++) {
        //Generate a unique bot ID (integer) that is 11 digits long
        $botid = rand(10000000000, 99999999999);

        //Insert the bot into the cube_checkin table
        $stmt = $con->prepare('INSERT INTO cube_checkin (cube_id, user_id, is_bot) VALUES (?, ?, 1)');
        $stmt->bind_param('ii', $cubeid, $botid);
        $stmt->execute();
        $stmt->close();
    }

    //Commit the transaction
    if (mysqli_commit($con)) {
        http_response_code(200);
        header('Content-Type: application/json');
        echo json_encode(array("success" => "Bots have been added to the cube!"));
        exit();
    } else {
        http_response_code(400);
        header('Content-Type: application/json');
        echo json_encode(array("error" => "There has beeen an error adding bots to the cube."));
        exit();
    }

}

I really cannot understand why it's behaving like this as I am closing the statement within the loop.

GenesisBits
  • 364
  • 2
  • 23
  • Is it always `2147483647` on the first iteration? What type of column is `user_id`? – brombeer Jan 18 '23 at 11:01
  • I was going to say no, that was just an example but upon checking, yes. For some reason it's always `2147483647`. `user_id` is `int(11)` – GenesisBits Jan 18 '23 at 11:03
  • `2147483647` isn't even any of the values that are being generated. I'm assuming I am hitting a limit of sorts and 2147483647 is being given as the max value? – GenesisBits Jan 18 '23 at 11:04
  • I suggest you run https://www.php.net/manual/en/function.getrandmax.php – Simon Goater Jan 18 '23 at 11:20
  • As per @Simon Goater, running `getrandmax()` in PHP gives me `2147483647` which explains exactly why it was trying to insert with that ID. I wouldn't have ever even considered that! – GenesisBits Jan 18 '23 at 11:22
  • You could maybe try 10000000000 + ((rand() + (1+getrandmax())*rand()) % 90000000000) instead. – Simon Goater Jan 18 '23 at 11:41

0 Answers0