ashjack ashjack -4 years ago 137
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 )
LIMIT 1");

The value for
is currently
, 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 Source

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.

FROM numfacts 
WHERE number = '".(string)$number."' 
    AND id >= (SELECT FLOOR( MAX(id) * RAND()) 
               FROM numfacts
               WHERE number = '$number' ) 

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download