PHP and Mysql to much adding data

Question

i'm generating random team array.All players are stored in DB without team. I need to assign 20 players from database to a team and get their info into array. I wrote the code, but somehow code assigns 60 players instead of 20 in database. Where did I went wrong?

 $Team = array();
   $i=0;
   while($i < 20)
   {
     $rand = rand(1,100);
     $sql = $con->query("SELECT * FROM players where id='$rand'");
     if($sql->num_rows > 0)
     {
       $data = $sql->fetch_array();
       if($data['teamid'] == NULL)
       {
         $con->query("UPDATE players SET teamid='$teamid' WHERE id='$rand'");
         $Team [$i]['name'] = $data['name'];
         $Team [$i]['surfname'] = $data['surfname'];
         $Team [$i]['rating'] = $data['rating'];
         $Team [$i]['position'] = $data['position'];
         $i++;
       }
     }
   }

Could you change the line `$sql->num_rows > 0` to `$sql->num_rows == 1` , you may have duplicate ids? — ndrwnaguib, Dec 05 '18 at 01:14
How many players do you have? And will it scale? You are making 20 or more DB calls right now.. — Ice76, Dec 05 '18 at 01:17
Replace query with `"SELECT * FROM players where id='$rand' AND teamid IS NULL"` — M A, Dec 05 '18 at 01:19
Additionally, as `id` and `teamid` are probably integers, there is no need to surround them with single quote in your SQL statement. — Raptor, Dec 05 '18 at 02:08

Derek · Accepted Answer · 2018-12-05T01:30:40.250

There's a simpler way to do this (forgive me if my SQL or PHP is not exactly correct, it's been a while).

// Generate 20 unique random numbers between 1 and 100
// see https://stackoverflow.com/a/5612704/910328
$randomPlayerIds = range(1, 100);
shuffle($randomPlayerIds);
$randomPlayerIds = array_slice($randomPlayerIds, 0, 20);

// In your query, get 20 records where id is in the list of random numbers AND
// teamid is null, and set results to the team array.
$players = $con->query(
  "select * from players where id in $randomPlayerIds and teamid is null"
);
$team = $players->fetch_assoc();

// Update your database.
$teamid = ???; // wherever you're getting this value from
$con->query("UPDATE players SET teamid='$teamid' WHERE id in $randomPlayerIds");

score -1 · Answer 2 · answered Dec 05 '18 at 02:03

Try this. This assigns 20 people to a team.

$Team = array();
for($i=0;$i<20;$i++)
{
    $rand = rand(1,100);
    $sql = $con->query("SELECT * FROM players where id='$rand'");
    if($sql->num_rows > 0)
    {
        $data = $sql->fetch_array();
        if($data['teamid'] == NULL)
        {
            $con->query("UPDATE players SET teamid='$teamid' WHERE id='$rand'");
            $Team [$i]['name'] = $data['name'];
            $Team [$i]['surfname'] = $data['surfname'];
            $Team [$i]['rating'] = $data['rating'];
            $Team [$i]['position'] = $data['position'];
        }
    }
}

PHP and Mysql to much adding data

2 Answers2