getting random data

[Anon]

Hi,

I am trying to get an sql call that will search for products that are on special offer and display 3 of them randomly.

I am trying to use the following code with no luck. any pointers would be great.

$sql = "select product.* where On_Special='1' order by Rand() limit 3";

$result = mysql_query($special_sql);

while ($special = mysql_fetch_array($result)) {

...........

[Anon]

Looks like you left out the FROM clause.

Try this:
$sql = "select * from product where On_Special='1' order by Rand() limit 3";

[Anon]

Sorry i should have pointed out i cut down the sql for the post. there is actually table joins in there.

"select product., manufacturers. from product left join manufacturers on product.Manu_ID=manufacturers.ID where On_Special='1' order by rand(), limit 3";

I am running MySQL 3.22.34. just for information.

[Anon]

That's better 🙂

I take it the comman after "rand()" is a typo?

And what exactly is the error you get? is the data not random?

[Anon]

yes the comma was a typo.

The error message I am getting is

Warning: Supplied argument is not a valid MySQL result resource in C:\Inetpub\wwwroot\bed-e-buys\specials2.php3 on line 15

and line 15 is where the "where" statement starts

$special_sql = "select product., manufacturers. from product left join manufacturers on product.Manu_ID=manufacturers.ID where On_Special='1' order by rand() limit 3";
$result = mysql_query($special_sql);

while ($special = mysql_fetch_array($result)) {

[Anon]

The error means that your sql statement is bad.

I guess there could be a problem with the

where On_Special='1'

because you don't specify which table this field is in.

[Anon]

the On_Special is in the product table.

taking out the rand and limit and it works fine but obviosly without limiting the results, I know the sql is fine.

Do i need to seed the rand comman before the sql call, if so how?

[Anon]

i've had to do something similar to this: it's a bit of a hack, but read all the results into an array, and then randomise the array. don't use shuffle() because it's terrible: i used the following..

mt_srand(microtime()*1000000);
$i=0;
while ($result=mysql_fetch_object($results)) {
$sorter[$i]=$result;
$seed[$i]=mt_rand();
$i++;
}
asort($seed);
while (list($j, $junk) = each($seed)) {
echo $sorter[$j]->columnname;
}

like i say, a bit of a hack.

if you /do/ get the SQL version working then please post how: i'd like to use it too!

[Anon]

I got it working with trial and error,

here it is for your beneffit

$count_sql = "select * from product where On_Special='1'";
$cresult =mysql_query($count_sql);
$count = mysql_num_rows($cresult);

// seed with microseconds since last "whole" second
srand((double)microtime()*1000000);

$random=(rand (1,$count));

$special_sql = "select product., manufacturers. from product left join manufacturers on product.Manu_ID=manufacturers.ID where On_Special='1' order by $random limit 3 ";
$result = mysql_query($special_sql);

while ($special = mysql_fetch_array($result)) {

[Anon]

Or, you could do something like:

execute: select count(*) from table;
$max=result from above statement
(Do your favorite srand or mt_srand here)
$num=rand(0,$max-1)
execute: select stuff from table limit 1 offset $num

Now the randomness is determined by your program, no the database.

[Anon]

Darn, upon reading the manual (far too late)
I found this line:

You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. In MySQL 3.23, you can
however do: SELECT FROM table_name ORDER BY RAND() This is useful to get a random sample of a set SELECT FROM table1,table2 WHERE
a=b AND c<d ORDER BY RAND() LIMIT 1000. Note that a RAND() in a WHERE clause will be re-evaluated every time the WHERE is executed.

I think that implies that pre 3.23 does not allow you to order by rand().

[Anon]

yup, that's a cool way of randomising
arrays. I used it for our project, thanx alot for posting.

However. I allways get the same order when doing a

mt_srand(microtime()*1000000);

on top of the page.
If I comment it out each page reload shows a different order.
Posting that in case others have the same problem (took me a while to find out)

greetings, Moritz

[Anon]

Since posting, i found a better way of doing it, which makes it a more efficient way of randomising:

$result = mysql_query("SELECT ID FROM table where ....");
$numrows = mysql_num_rows($result);

while ($ID = mysql_fetch_array($result)) {
$count[] =$ID["ID"];
}
$set = (rand (0,$numrows-1));
$random = $count[$set];

$sql = mysql_query("select * from table where ID =$random");

Dont forget to seed the random first!