CODEWITHSUNDEEP
phpmysql
frosty
frosty

Reputation: 2851

Select random row from table with deleted records

If I have a table with 3 rows with IDs 1,3,5 because rows with ID 2 and 4 were deleted, how do I make sure I select a row that exists?

$stmt = $db->prepare("SELECT COUNT(*) FROM table");
$stmt->execute();
$stmt->bind_result($numRows);
$stmt->fetch();
$stmt->close();

$random = mt_rand(1,$numRows);

$stmt = $db->prepare("SELECT link FROM table WHERE id=$random");

This won't ever select row with id 5, and also will select one that doesn't exist (2).

Upvotes: 1

Views: 63

Answers (5)

jolmos
jolmos

Reputation: 1575

You could select one, randomly order, like this:

SELECT link FROM table ORDER BY RAND() LIMIT 1

UPDATE: You should benchmark the different solutions you have, but I'm thinking this one could be nice with large amount of rows:

$stmt = $db->prepare("SELECT COUNT(*) FROM table");
$stmt->execute();
$stmt->bind_result($numRows);
$stmt->fetch();
$stmt->close();

$random = mt_rand(1,$numRows);

$stmt = $db->prepare("SELECT link FROM table WHERE id>=$random LIMIT 1");
$stmt->execute();
$stmt->bind_result($link);
if(!$link){
    $stmt = $db->prepare("SELECT link FROM table WHERE id<$random LIMIT 1");
    $stmt->execute();
    $stmt->bind_result($link);
}

Upvotes: 0

Monty Khanna
Monty Khanna

Reputation: 1120

If you want to follow your approach then you have to do some changes in your query. 

1.) Query one : select id from table. // It will give you array of existing id.

2.) You have to use array_rand(). and use your second query.

Example : 

$stmt = $db->prepare("SELECT ID FROM table");
$result = $stmt->fetchAll();
$random = array_rand(array_flip($result), 1);
$stmt = $db->prepare("SELECT link FROM table WHERE id=$random");

Upvotes: 0

mikeyq6
mikeyq6

Reputation: 1148

If you just want a random row and don't care about the id, then you could use:

SELECT link FROM table
ORDER BY RAND()
LIMIT 1

For large numbers of rows (10000+), then you may need to implement another solution, as this query can be slow. This site has a good explanation and alternative solutions

Upvotes: 0

ahmetunal
ahmetunal

Reputation: 3960

If the number of rows are small (and you are sure that it will stay that way), you can use ORDER BY RAND() (Please note that this will create performance problems with big tables).

Other way is first counting how many rows are there

SELECT COUNT(*) AS total FROM table;

then pick a random number

$rand = rand(1, $total);

and select that row with limit

SELECT * FROM table LIMIT $rand, 1;

Upvotes: 2

rdn87
rdn87

Reputation: 724

U can use a SQLstatement with EXISTS

SELECT link
FROM table
WHERE EXISTS (SELECT link
              FROM table
              WHERE id = $random);

Upvotes: 0

Related Questions