Is there something wrong with this query?

[Tekkno]

$results = mysql_query("SELECT column1, column2, date, time FROM table ORDER BY RAND() LIMIT 1");
$row_table = mysql_fetch_assoc($results);

For some odd reason this does not return data, however if I change it to:

$results = mysql_query("SELECT column1, column2, date, time FROM table ORDER BY RAND() LIMIT 2");
$row_table = mysql_fetch_assoc($results);

I get data. Any ideas?

[cwarn23]

Maybe the following.

$results = mysql_query("SELECT column1, column2, date, time FROM table LIMIT RAND(),1");
$row_table = mysql_fetch_assoc($results);

If that fails to return a row then rand() is returning a number higher than the number of rows present.

[Tekkno]

That just gives me "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource..." on the line with the while loop. Which just means the query failed right? I'm not sure what you mean by:
If that fails to return a row then rand() is returning a number higher than the number of rows present.

[cwarn23]

To make things easier the following.

$results = mysql_query("SELECT column1, column2, date, time FROM table LIMIT ROUND(RAND(COUNT(*))),1");
$row_table = mysql_fetch_assoc($results);

[Tekkno]

Thanks for your help cwarn,

I get the same error with that query. I also tried:

$results = mysql_query("SELECT column1, column2, date, time FROM table ORDER BY RAND() LIMIT 1");
$row_table = mysql_fetch_row($results);

I do not receive an error with this, but no results. I have another php file that does a similar query to the same database and it works fine. Why would it work with ORDER BY RAND() LIMIT 2 but not ORDER BY RAND() LIMIT 1? Also, I am not sure if I mentioned this, but when I use a LIMIT of 2, it only returns one result. I just don't get it.

[JRM]

Maybe we should start by asking whatt you are trying to accomplish with this query.
"Order BY" refers to how mysql returns the sort order. How does a random floating point between 0 and one do anything useful in this context?
I wonder how how you got any results at all.

[Tekkno]

Maybe we should start by asking whatt you are trying to accomplish with this query. "Order BY" refers to how mysql returns the sort order. How does a random floating point between 0 and one do anything useful in this context? I wonder how how you got any results at all.

JRM,
The query is for recommendations, it is meant to randomize the results to get something different each time. Thus, the order by rand. By default how is a query ordered, by id I would assume? Doesn't do me much good if it displays the same values every time does it? If you have a better way I'm all ears.

[cwarn23]

Try each of the following

$results = mysql_query("SELECT * FROM table ORDER BY RAND() LIMIT 0,1");
$row_table = mysql_fetch_row($results);

$results = mysql_query("SELECT * FROM table ORDER BY RAND() LIMIT 2");
$row_table = mysql_fetch_row($results);

$results = mysql_query("SELECT * FROM table ORDER BY RAND() LIMIT 1,1");
$row_table = mysql_fetch_row($results);

My theory is that the limit 1 statment wasn't designed to be ordered by random. But maybe we can make a work around this bug.

[Tekkno]

Thanks cwarn, I will give that a try. I just don't understand why the same query in a different script and same database, just different data, works fine. I will let you know the results.

[diafol]

Tried back-ticking your fields? I don't know if it will make any difference. I know that DATE and TIME have a particular meaning in MySQL.

[JRM]

OK, let's assume that it is ordering by id which is a primary key.
Primary keys are INTEGERS, not floating point decimals >0 , <=1.
if you want to randomize an integer result you would have to multiply by ten then round up to the nearest integer value. Use that as your selection.
However you are randomizing the sort, not the selection.
If you want a different id every time, why not use WHERE id = $someRandomInteger
You won't need to limit, because the id is unique.

[diafol]

OK, let's assume that it is ordering by id which is a primary key. Primary keys are INTEGERS, not floating point decimals >0 , <=1. if you want to randomize an integer result you would have to multiply by ten then round up to the nearest integer value. Use that as your selection. However you are randomizing the sort, not the selection. If you want a different id every time, why not use WHERE id = $someRandomInteger You won't need to limit, because the id is unique.

This'll work if the Primary Key series is complete. What if some keys have been deleted, e.g. 1,2,3,5,8,...?

Interesting discussion here: http://www.greggdev.com/web/articles.php?id=6

I've seen the offset method used too

The offset keyword tells the query to select the next x record after the first. If offset = 0, the return the first, if offset = 2, return the 3rd etc. So for this, get the random number to be between 0 and no_rows -1, then apply this to the offset. This may mean running 2 queries. Perhaps they could be nested??

[JRM]

JRM, The query is for recommendations, it is meant to randomize the results to get something different each time. Thus, the order by rand. By default how is a query ordered, by id I would assume? Doesn't do me much good if it displays the same values every time does it? If you have a better way I'm all ears.

You asked if there is something wrong with this query,
The entire premise is faulty. As i said, I'm surprised that it works at all albeit "randomly".
Order by is used for making a column as the one for the sort ORDER output, such as ORDER BY name asc, which will sort the output rows by all the names in the query alphabetically. "Order by" implies more than one row!!!
Order and Section criteria are two different things?

If you want to output ONE recommendation, why not select one primary key Randomly?

$results = mysql_query("SELECT * FROM table where id = $someRandomNumber");

      $row_table = mysql_fetch_row($results);

Where you will have to take that floating point output from rand which by the way,is >0 but <= 1 multiply it by 10 and round up to the nearest INTEGER. ( Isn't there something better than rand()???) Use the integer output to select the primary key key on a row in the table.

[diafol]

Here's the OFFSET idea. This prevents having to go through a whole table scan (*I think*).

$n = mysql_query("SELECT COUNT(*) FROM table");
$off = mysql_fetch_array($n);
$max_offset = $off[0] - 1;
$rnd = mt_rand(0,$max_offset);
$rs = mysql_query("SELECT fields FROM table LIMIT 1 OFFSET $rnd");
$data = mysql_fetch_array($rs);

It would be interesting to see how quickly this would run in comparison with the mysql RAND function. I think this may be a preferred method as it doesn't depend on the actual primary key value. i.e. you don't need to know it and your series (min to max) does not have to be continuous (every inbetween integer). Anyway, interesting to hear from others on this.

//EDIT: the last query ran at about 62ms for a db with 5200 records, and worked a treat!

[JRM]

Here's the OFFSET idea. This prevents having to go through a whole table scan (*I think*).

$n = mysql_query("SELECT COUNT(*) FROM table");
$off = mysql_fetch_array($n);
$max_offset = $off[0] - 1;
$rnd = mt_rand(0,$max_offset);
$rs = mysql_query("SELECT fields FROM table LIMIT 1 OFFSET $rnd");
$data = mysql_fetch_array($rs);

It would be interesting to see how quickly this would run in comparison with the mysql RAND function. I think this may be a preferred method as it doesn't depend on the actual primary key value. i.e. you don't need to know it and your series (min to max) does not have to be continuous (every inbetween integer). Anyway, interesting to hear from others on this.

//EDIT: the last query ran at about 62ms for a db with 5200 records, and worked a treat!

...now THAT's what I'm talkin' 'bout!
Makes perfect sense...and no rand()
Very good info,ardav.
I'm going to keep this script in mind if I ever need random sampling in the future.

[Tekkno]

Wow thank you both! Sorry for the delay, I forgot about this thread lol. JRM, I like your suggestion but as ardav mentioned it won't work well for me because I have deleted some rows in the past and the keys are not sequential. ardav I will give that a try and see how it works out, it is a great alternative to order by rand which I have been wanting to get away from for quite a while. Take care.

[Tekkno]

Hey ardav,
Thanks for the suggestion, sorry it took so long to reply. I have implemented it into my code and it works great. Hopefully this will help others out in the future as well.

[diafol]

Yep, took me a while to get to grips with it. I was working on a vague recollection of something I saw a year or two ago! It's a beauty - although I can't claim all the credit - the seed was sown by others. I'm sure that there are other implementations out there somewhere, but I'm blasted if I can find them now.