NamePros.Com - View Single Post - Randomingly choosing a quote from Database, PHP

Rand is not the best way but it works.

If its a small database then go for it, but if its more than 100 records dont use that way, the reason is ORDER BY RAND() forces Mysql to generate alot of random numbers.

Quote from below resource

Quote:

What happens when you run such a query? Let’s say you run this query on a table with 10000 rows, than the SQL server generates 10000 random numbers, scans this numbers for the smallest one and gives you this row. Generating random numbers is relatively expensive operation, scaning them for the lowest one (if you have LIMIT 10, it will need to find 10 smallest numbers) is also not so fast (if quote is text it’s slower, if it’s something with fixed size it is faster, maybe because of need to create temporary table).

I code ASP and dont code PHP so I cant give you any code but I think you would be better off with this type of thing

SELECT COUNT(*) FROM DBNAME
get the total records here
Close recordset

calculate your random number between 1 and the total records.
I dont know PHP dont take this literaly
srand((double)microtime()*1000000);
$iRandom = rand(0,$totalrecords);

Now that you have a random number you can use the LIMIT in the Query properly.

SELECT [field] FROM [table] LIMIT $randomvariable, 1

The above limits the rows to one, and starts at the random number

Please dont cut paste my code here as i stated I code ASP and my PHP here is most likely wrong.

For more info on this
http://www.titov.net/2005/09/21/do-...ows-from-table/