ashjack ashjack - 26 days ago 8
PHP Question

PHP & MYSQL - ORDER BY id sometimes returns nothing

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
FROM numfacts
WHERE number = '".(string)$number."'
AND id >= (SELECT FLOOR( MAX(id) * RAND()) FROM numfacts )
ORDER BY id
LIMIT 1");


The value for
$number
is currently
12
, and there are two rows containing this in the database. Roughly 2/3 times the code returns a value, and the other 1/3 of times it returns 0 results.

If you need more code, I will provide it.

Answer

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.