Generating random numbers from set of array elements from database

Question

I want to generate non-repeated random numbers from a set of array elements. My array elements are from database. Like this

$b1 = "SELECT id FROM datacards WHERE id<>".$dcard."";
$b2 = mysqli_query($con, $b1) or die (mysqli_query($con));
while($b3 = mysqli_fetch_array($b2))
{

$ids[] = $b3['id']; 
}
$Myarray=implode("," , $ids);

I have to generate 2 non repeated random numbers from Myarray. Myarray contains (ids) integer numbers from 1 to 60 except 1 number which is

$b1 = "SELECT id FROM datacards WHERE id<>".$dcard."";

How can i do it?

WHERE NOT IN ($alreadyFetchedNums) ORDER BY RAND()

Ivan Yonkov
– Ivan Yonkov

2018-04-24 06:54:26 +00:00
Commented Apr 24, 2018 at 6:54 — Ivan Yonkov
– Ivan Yonkov, Commented Apr 24, 2018 at 6:54
Is $dcard user-supplied data? (from GET / POST)

mickmackusa
– mickmackusa ♦

2018-04-24 07:33:13 +00:00
Commented Apr 24, 2018 at 7:33 — mickmackusa
– mickmackusa ♦, Commented Apr 24, 2018 at 7:33
Yes it Is user supplied data

meenal
– meenal

2018-04-24 07:37:23 +00:00
Commented Apr 24, 2018 at 7:37 — meenal
– meenal, Commented Apr 24, 2018 at 7:37

mickmackusa · Accepted Answer · 2018-04-24 11:26:01Z

1

$b1 = 'SELECT `id` FROM `datacards` WHERE `id` <> ' . (int)$dcard . ' ORDER BY RAND() LIMIT 2';

This should exclude the one id and select 2 random ids.

Or with an oop prepared statement (assuming $_GET['dcard']):

if (!empty($_GET['dcard']) && ctype_digit($_GET['dcard'])) {
    $db = new mysqli('localhost', 'username', 'password', 'database');
    if ($stmt = $db->prepare('SELECT `id` FROM `datacards` WHERE `id` <> ? ORDER BY RAND() LIMIT 2')  &&
        $stmt->bind_param('i', $_GET['dcard']) &&
        $stmt->execute() &&
        $stmt->bind_result($id)
    ) {
        while($stmt->fetch()){
            $ids[] = $id;
        }
        $stmt->close();
    }
} else {
    echo 'Missing/Invalid input data.';
}

Or you could use GROUP_CONCAT() in your query and retrieve a single object.

edited Apr 24, 2018 at 11:26

answered Apr 24, 2018 at 7:36

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Meloman Over a year ago

To increase PHP speed, use simple quotes in combination with . to concate. Because double quotes is for in chain vars. like ...`id` <> '$dcard' ORDER...

mickmackusa Over a year ago

That's fine. No user will ever notice the speed increase. If you were going to say this on all similar cases on StackOverflow, you would be leaving tens of thousands of comments.

Ultimater · Accepted Answer · 2018-04-24 07:42:19Z

1

Untested, but hopefully it will get you using prepared statements, and uses ORDER BY RAND() that was suggested by Royal Bg:

$query = 'SELECT id FROM datacards WHERE id <> ? ORDER BY RAND() LIMIT 2';
$stmt = mysqli_prepare($con,$query) or die( mysqli_error($con) );
mysqli_stmt_bind_param($stmt, 's', $dcard);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
while($row = mysqli_fetch_array($result))
{
    $ids[] = $row['id']; 
}
$Myarray = implode("," , $ids);

Note that ORDER BY RAND() is very slow with large data due to having to reorder all the rows matched. It's better to first count the rows in the table, then generate a number between 0 and the number of rows minus one. This number will represent the index of the record in the database. Then generate another random number between 0 and the number of rows minus two. This will represent another record index in the database. If the second index is equal to or greater than the first index, then add one to its value. With this algorithm, you'll have two completely random non-repeating indexes from the database. Then you'd ORDER BY field(id, ?, ?) LIMIT 2 with the two question marks being the ids you want to select, and the order you want to select them. You can find the FIELD function of MySQL in the manual here:
https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_field

answered Apr 24, 2018 at 7:42

Ultimater

4,7482 gold badges32 silver badges44 bronze badges

3 Comments

mickmackusa Over a year ago

I didn't know when posting if prepared statements were necessary, but I appreciate and share your concern. I am using (int) to hedge my bets. The table is not huge so there is little impact from using RAND().

mickmackusa Over a year ago

You will need i in your bind instead of s.

Ultimater Over a year ago

@mickmackusa MySQL works with integers treated like strings just fine. The situations where i becomes mandatory are pretty slim. But yeah, the OP did mention he is using integers so i would make sense here. Would it have any differences in this context? Nah. In fact I can't remember the last time i ever had to bind with i.

Collectives™ on Stack Overflow

Generating random numbers from set of array elements from database

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related