Php Query Help

golfinggod

Can anyone help im trying to select a random banner that has a rating of 1. I need all the data from the banner table, where the Rating in the website table is less than 2.

$Fetchbanner = "SELECT * FROM banners , website WHERE  Bannersize = '$Bannersize' and website.Rating < 2 order by rand() limit 1"; 

$Result = mysql_query($Fetchbanner); 

$Row = mysql_fetch_array($Result);

The problem is it is still ignoring the rating part and returning any banner.

Thanks

nemonoman

This because you have not stated the join condition between banners and website tables. For example

SELECT * FROM banners , website WHERE banners.websiteid=website.id
AND Bannersize = '$Bannersize' and website.Rating < 2 order by rand() limit 1";

If you don't indicate some sort of join condition between the two tables, you return a 'cartesian product'...all teh banners, all the websites

jpmoriarty

and also you might want to try rand(now()) instead of just rand()

i looked at the mysql manual but, well, you try and make sense of this!

You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. From version 3.23 you can 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. RAND() is not meant to be a perfect random generator, but instead a fast way to generate ad hoc random numbers that will be portable between platforms for the same MySQL version.

nemonoman

Not to worry: order by rand() limit 1 will generally produce a random answer in very little time (i.e., microseconds). The problem is the cartesian product created by the unrelated join.