List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 12 1999 5:32pm
Subject:Re: question: fetching a random row
View as plain text  
At 11:21 AM -0500 6/12/1999, Kyle Cronan wrote:
>Hi everyone,
>  I'm working on a flash card program which stores its cards in a MySQL
>database of a few thousand cards.  The routines accessing this database
>are written in C.  I was wondering if there is a way for me to execute a
>select query on all entries, call mysql_use_result, and then fetch one of
>the rows at random.  Obviously I can't do a fetch of all the rows and then
>seek to one of those, as that would take up way too much memory.

With mysql_use_result() you are constrained to processing the rows in
order as they are returned from the server.  You have "random access"
into the rows of the result set only if you use mysql_store_result(),
since then you have the entire result set on the client side.

However, you could run a query to get a count of the number of rows
in the table (SELECT COUNT(*) FROM tbl_name).  Then pick a random
number and use that to construct a SELECT statement containing a
LIMIT clause (SELECT ... FROM tbl_name ... LIMIT n, 1), where n is
a random number between 0 and (the number of rows in the table minus one).

That way you only have to retrieve a single row, and you get to decide
on the client side which one it is.

--
Paul DuBois, paul@stripped
Northern League Chronicles: http://www.snake.net/nl/
Thread
question: fetching a random rowKyle Cronan12 Jun
  • question: fetching a random rowMichael Widenius12 Jun
Re: question: fetching a random rowPaul DuBois12 Jun