randomizing results of a query (guru needed)

Anon

I want to give all of my products an equal oppurtunity to be purchased. I notice that as it is, items that appear near the top of the list get a disproportionate ammount of interest.

All of my products are very good, and I make just as much money selling one as the other. Therefore, I need to "ShAkE Up" my results a little.

Any ideas? Want to make it trickier?

I typically only like to show 3 items on a page (too many choices confuse my customers) but I don't want the same product to show up on more than one page.

Here is the extremely simple query I use now:
SELECT * 
FROM $table 
WHERE 
ut1 ='%$bedrooms' AND 
ut4 ='%$baths' AND 
ut14='%$locale'

I thought that I could get a list of results, disorder them (randomize) and then somehow implode them and pass the info to page 2 if a person is interested in going there.

Any help or comments would be greatly appreciated. 

P.S. 
If you know how to do this, you are programmer /DB guru extrordinare!

Thanks Alot,
Matthew Nuzum

Anon

Here's some simple pseudo code to do that:

$result=pg_exec($conn,"select id_number from table;");
$rows=pg_numrows($result);
for ($=0;$i<$rows;$i++){
$ids[]=pg_result($result,$i,"id_number");
}

shuffle($ids);
$count=5;
for ($i=0;$i<$count;$i++){
$res2=pg_result($conn,"select * from table where id_number=$ids[$i];");
print out your stuff here.
}

Anon

According to MySQL documentation you can do a random order by.

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

Run this query first then make an array of the product ids in the order they are returned. Implode the array like you where mentioning and pass it page to page. Then just use a query that uses a WHERE productID in ($array[$currentpage#*$itemsperpage]...)

I don't know if this will work in other databases.

Hope this helps.

Anon

That random function doesn't seem to work for some reason....the best way to do it is use your offset and limit functions and have offset as a random variable like:

function randomint($max = 100)
{
static $startseed = 0;
if (!$startseed)
{
$startseed = (double)microtime()*getrandmax();
srand($startseed);
}
return (rand()%$max);

}

$bar = $records;
$foo = randomint($bar);

//now do query
$result = mysql_query("select * from $dbtable LIMIT $foo,1");
while($row = mysql_fetch_row($result))

regards,
scott d~

Anon

This is a similar problem, but different for several reasons.

I'm customizing a copy of phpHoo2 (see,
http://www.cable-modems.org/phpHoo/ if you are unfamiliar with it. Great for hierarchical databases.

I've added a column to the Links table called "OnTop", if it is not null, then I want it displayed first, and the remaining results displayed randomly. I suppose the SubmitDate could be randomized, and used to sort on, but I'm not sure how

Here's the function that does the selection, and a remark, that might be helpful.

function get_Links($CatID = "")
{
if(empty($CatID))
{ $CatID = "= 0";

            } else {
                    $CatID = "= $CatID";
                    $sql = "UPDATE Categories SET Views=(Views+1) WHERE CatID $CatID";
                    $results = $this->sql_query($sql);

// need to put OnTop option, on top, and randomize display order of remainder
// example: select category_description, rand() as rnd from category order by rnd;

$sql = "SELECT * FROM $this->LNK_TBL WHERE (Approved != 0) AND CatID $CatID ORDER BY OnTop, SubmitDate ASC";
$results = $this->select($sql);};
return $results; }

Any ideas would be greatly apreciated.