[Resolved] random generate from DB help

[davieboy]

hi folks

im using the following code

$query = "SELECT * FROM clients WHERE banner = '1' AND displays > '0' ORDER BY rand() LIMIT 1";
$result = mysql_query($query);
while ($r=mysql_fetch_array($result))
{
$id=$r["id"];
$pname=$r["pname"];
$area=$r["area"];
$star=$r["star"];
$displays++;

$update = "UPDATE clients SET displays=displays-1 WHERE id=$id";
$updatesql = mysql_query($update);
}

as my query and it is working brill

What i wish to do is display ideally 4 results from the DB but dont want to double display any of the results.
EG

id numbers in DB
1,2,3,4,5

want to dispay 4 chosen at random
1,5,2,3

dont want doubles like
1,4,2,1

Anyone able to help me at all?

David

[mrhappiness]

Originally posted by davieboy
dont want to double display any of the results

you should normalise your database so that your tables only contain distinct values

or do

SELECT DISTINCT

[davieboy]

is that database side of webpage side?

all the tables in the db do all have a unique id number.

[edcellgavin]

i'm not sure but try this

Select distinct(id_number) ...

or

... where statement group by (id_number) order by ...

[davieboy]

i edited this bit:

 ORDER BY rand() LIMIT 1

to

 ORDER BY rand() LIMIT 2

this <i>seams</i> to work, but a question is will this eventually produce doubles of some of the generated numbers?

David

[twoeyes]

I dont think it will, but you can use this just in case:

$query = "SELECT DISTINCT * FROM clients WHERE banner = '1' AND displays > '0' ORDER BY rand() LIMIT 4";

[davieboy]

Originally posted by twoeyes
I dont think it will, but you can use this just in case:

$query = "SELECT DISTINCT * FROM clients WHERE banner = '1' AND displays > '0' ORDER BY rand() LIMIT 4";

[/B]

thank you very much for your help there