pulling random from database list

Anon

I could use some advise, maybe some sample code. I am using PHP and mysql. I have a table of songs in my database. I need to setup this table so that it can pull a random row from the table and display its contents. I had implemented this with an auto_increment ID number as the primary key, but I ran into trouble when I would delete songs from the database, because it left gaps which screwed up my function I had for picking the random row.

Any advise on how I could implement this?

kparker

as long as you're running mysql version 3.23, you can say ... order by rand() limit 1

Anon

I'm not sure what you mean... is "order by" a MySql function? How would I use it.

Also, if I am going to be adding/deleting songs from the database on a daily basis, how would I keep from getting gaps in the database? Thanks.

kparker

ORDER BY is standard SQL. LIMIT is a frequent extension, supported by mysql and many others.

The thing I referred to is recommended by the mysql manual itself, so my assumption is that gaps don't matter. Here's my (somewhat conjectural) understanding of what happens. Each matching row (or each row, if
there is no where-clause) is retrieved, and then sorted by a random value. The value of rand() called for each row has no relation to any particular column value, so gaps in number sequence do not affect anything.

dstockto

If you are using AUTO_INCREMENT and regularly deleting stuff from the database, there is not a way to automatically prevent gaps from appearing in the database as far as the AUTO_INCREMENT field is concerned. You could write queries that search for an opening in the database and automatically use that empty id in your insert query, but it is probably more of a hassle than it is worth.

As far as how to use the ORDER BY rand() it's like this:

SELECT * FROM songs ORDER BY rand() LIMIT 1;

Like Kirk said though, that is a mySQL 3.23 option. If you are using pre 3.23, you can do this:

SELECT , (id 0 + rand()) AS randcol FROM songs ORDER BY randcol LIMIT 1;

This creates a column with a random number between 0 and 1 in it called randcol. The ORDER BY sorts by that column. For some reason you have to have a field from your database in there. You will notice that id*0 would be 0. Unfortunately, just using rand() AS randcol doesn't seem to work. This way will.

Hope this helps.

Dave

Anon

Ok, this will work good! Thank you both for explaining this to me, I really appriciate it!

kparker

For some reason you have to have a field from your database in there.

Perhaps because if mysql doesn't see the column in there, it thinks it can just call rand() once?

dstockto

Perhaps, but if you don't limit it to one, the rand_col seems to have different numbers in each record. I don't know why you have to do the column in there too.

Dave

kparker

What happens when you do say:

  select some_field, rand() as randy from table limit 10?

Does each row get the same value for 'randy' or not?

And don't confuse the current case with one that a clever query optimizer can reduce to a simple index lookup. That is, given a table containing 'id' that is a primary key, you can say something like

  select * from table order by id desc limit 1;

and a clever optimizer can simply read the index on 'id' for the highest value, and return that row, without any further work. (I make no claim as to whether or not mysql's optimizer is that smart.) But when you do something that is generating a separate random value for each row, there is no around retrieving all matching rows and actually sort them to find out which one qualifies as the first one.

dstockto

Here's the weird part... From the looks of it, each row will have a different value in "randy" but, it doesn't seem to want to sort by that field unless you put in the whole formula mess. I do not know why this is.

I just noticed I am running 3.22 (time to upgrade I guess). When I run the query you mentioned, I get different results:

+-----------+-------+--------+
| name | votes | RND |
+-----------+-------+--------+
| Sue | 0 | 0.0451 |
| Calvin | 0 | 0.1138 |
| Rudolph | 0 | 0.4336 |
| Tyrone | 54 | 0.8268 |
| Shawakita | 0 | 0.8332 |
| Dave | 200 | 0.6854 |
| Zamboni | 0 | 0.9276 |
| Fish | 0 | 0.5818 |
| Goat | 0 | 0.1260 |
+-----------+-------+--------+

amd for the next run:

+-----------+-------+--------+
| name | votes | RND |
+-----------+-------+--------+
| Sue | 0 | 0.8848 |
| Calvin | 0 | 0.0459 |
| Rudolph | 0 | 0.5753 |
| Tyrone | 54 | 0.7388 |
| Shawakita | 0 | 0.9679 |
| Dave | 200 | 0.6232 |
| Zamboni | 0 | 0.2122 |
| Fish | 0 | 0.1916 |
| Goat | 0 | 0.3214 |
+-----------+-------+--------+

The numbers are all different, and the query is select *, rand() as RND from baby_names order by RND;

The names are all in the same order each time. When I use the formula trick I get

+-----------+-------+--------+
| name | votes | RND |
+-----------+-------+--------+
| Fish | 0 | 0.8791 |
| Tyrone | 54 | 0.6575 |
| Zamboni | 0 | 0.6501 |
| Shawakita | 0 | 0.2782 |
| Goat | 0 | 0.4406 |
| Sue | 0 | 0.3682 |
| Calvin | 0 | 0.5194 |
| Dave | 200 | 0.4922 |
| Rudolph | 0 | 0.9028 |
+-----------+-------+--------+

using the query
select , (votes0 + rand()) as RND from baby_names order by RND;

Again, the RND column doesn't appear to be in any particular order, but the results are seemingly randomized.

I'm off to upgrade mysql now I think... Anything I should be wary of in the upgrade?

Thanks,
Dave