How to fetch a random record from SQLite database?

Question

I am working on PHP. I was working with MySQL before. Here is the code I used -

 $offset_result = mysqli_query($con, " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM students ");
 $offset_row = mysqli_fetch_object( $offset_result );
 $offset = $offset_row->offset;
 $result = mysqli_query($con, " SELECT name FROM students LIMIT $offset, 1 " );      
 $row = mysqli_fetch_row($result);
 mysqli_free_result($result);

What will be the corresponding set of statements for SQLite?

Answer 1

Those SQL queries should work in SQLite if you just change RAND to RANDOM and FLOOR(x) to CAST(x AS INTEGER).

A slightly simpler way to do it is to order by a random number:

SELECT name
FROM students
ORDER BY RANDOM()
LIMIT 1

The way you are doing it is more efficient if the table is very large.