I have the following script:
// Query
$STH = $DB->prepare('SELECT * FROM players WHERE game = ?');
$STH->execute(array($_POST['game']));
// Get the stored photos
$Used = array();
while ($row = $STH->fetch())
$Used[] = intval($row['photo']);
// (1)
// Get a random photo that is not stored already
$Photo = array_rand(array_diff(range(1, 6), $Used));
// (2)
// Insert the photo
$STH = $DB->prepare('INSERT INTO players (id, user, game, name, family, photo) VALUES (?, ?, ?, ?, ?, ?)');
$STH->execute(array($Id, $User->id, intval($_POST['game']), $Name, $Family, $Photo));
However, this is giving me duplicated results in the database. Specifically, the 1
and the 5
are getting repeated many times (not the others for the test I performed of about 30 elements to force the 6 elements range.
Furthermore, if I write var_dump(array_diff(range(1, 6), $Used));
in (1) after many inserts, it always outputs array(2) { [1]=> int(2) [5]=> int(6) }
. So, apparently, the numbers 2 and 6 are not being considered. And if I do echo $Photo;
in (2), the numbers are always 1
or 5
, so it's not a problem of the insert apparently.
So, why are the entries in the database getting duplicated? What's wrong with my PHP code? I will definitely put a 2 field unique constraint in the future, but that won't fix the PHP which is saving the random photos' ids.
Silly me, I just solved it by re-reading what I thought I knew: array_rand (of course) returns the KEY of the array. Therefore, it works like:
// Query
$STH = $DB->prepare('SELECT * FROM players WHERE game = ?');
$STH->execute(array($_POST['game']));
// Get the stored photos
$Used = array();
while ($row = $STH->fetch())
$Used[] = intval($row['photo']);
// Get a random photo that is not stored already
$Array = array_diff(range(1, 6), $Used);
$PhotoKey = array_rand($Array);
$Photo = $Array[$PhotoKey];
// Insert the photo
$STH = $DB->prepare('INSERT INTO players (id, user, game, name, family, photo) VALUES (?, ?, ?, ?, ?, ?)');
$STH->execute(array($Id, $User->id, intval($_POST['game']), $Name, $Family, $Photo));