I am trying to wrote a PHP script that will pull a random row from a MYSQL table. So far, the code successfully grabs a random row, but sometimes it will return nothing, and I don't understand why.
$result = $conn->query("SELECT fact
WHERE number = '".(string)$number."'
AND id >= (SELECT FLOOR( MAX(id) * RAND()) FROM numfacts )
ORDER BY id
The part of the
WHERE clause with
id >= (SELECT FLOOR( MAX(id) * RAND()) FROM numfacts ) restricts the query to looking in a subset of the table. If the two rows with
number = 12 are not in this subset, the query doesn't return anything. And since the subset is specified randomly, this will only happen some of the time.
To ensure that the subset includes at least one of the rows with
number = 12, you can include that criteria in the subquery.
SELECT fact FROM numfacts WHERE number = '".(string)$number."' AND id >= (SELECT FLOOR( MAX(id) * RAND()) FROM numfacts WHERE number = '$number' ) ORDER BY id LIMIT 1
Note that this query doesn't provide very fair selection of rows. Because it uses
ORDER BY id, it's biased towards low-numbered rows. This method is reasonable when you're just trying to select a random row with no other filtering, but when you add the
number = 12 criteria it becomes unfair.