[PHP/MySQL] Selecting Random Info

[ducktape]

I made a simple banner rotator. I used the RAND() to call random banners from what is stored in a database.

http://neonsonline.com/index.php

$result = mysql_query("SELECT * FROM banner_ads ORDER BY RAND() LIMIT 1",$db);
$banners = mysql_fetch_array($result);

thats what im using to call the info. I currently have 3 banners and it calls the same each time. does anyone know of a way to make it call a different one each page load.

 

[Canuckkev]

You would need to store that info somewhere, most likely in an extra column. For example, add a column called "last_selected" and make it a BOOL.

Then, each time the script loads, do this (I'm not going to type out the code...)

Select id from table where last_selected = TRUE;
Store the selected id in a variable.
$random = $selectedid;
while($random == $selectedid){
$random = rand(1,$num_columns);
}
select * from table where id = $random;
//clean up the table now...

Update table where id = $random set last_selected = TRUE;
Update table where id = $selectedid set last_selected = FALSE;
etc...

I forget the syntax for UPDATE, but you get the idea...

EDIT
Oh ya, this is assuming you have only one visitor at a time. I guess sessions would be your next best bet if you didn't want this.

 

[Cagez]

It might be easier to just use shuffle() to shuffle the array returned from the DB. Its not perfect, sometimes you'll get dupes but I think its easier then adding data to the database and whatnot.

$result = mysql_query("SELECT * FROM banner_ads ORDER BY RAND() LIMIT 1",$db);
$banners = mysql_fetch_array($result);

srand((float)microtime()*1000000);
shuffle($banners);

 

[Canuckkev]

I don't see how that would be different than using rand. Yes, I suppose it's easier, but it would give the same result as using rand? Or maybe it might be a little more random...I don't think you have to seed rand, so seeding shuffle maybe makes it more random?

Anyways, I personally don't think it's a big deal if the same banner comes up twice in a row...not worth 4 sql queries at least. If however, you still wanted it to be a different one each time, a better method than I previously stated (takes up slighly more space, but fewer queries) is to have a "last_accessed" column, that has a timestamp. And then select the next one ordered by that timestamp. Once selected, update the timestamp. 2 queries instead of 4.

 

[biggulp]

try

SELECT * FROM banner_ads ORDER BY RAND(UNIX_TIMESTAMP()) LIMIT 1

$result = mysql_query("SELECT * FROM banner_ads ORDER BY RAND() LIMIT 1",$db);
$banners = mysql_fetch_array($result);

srand((float)microtime()*1000000);
shuffle($banners);

won't work since you already limit to 1 row so there's nothing to shuffle

 

[Cagez]

Oh yeah.... Well, if there was no limit :biggrin2:

 

[ducktape]

Originally posted by biggulp

SELECT * FROM banner_ads ORDER BY RAND(UNIX_TIMESTAMP()) LIMIT 1

tried it and it only shows the same over and over although it is a different one from b4

 

[jetalomar]

how about this one

result = mysql_query("SELECT * FROM banner_ads ORDER BY RAND() LIMIT 1",$db);
while ($banners = mysql_fetch_array($result);){

}

just wanted to add my $.02

 

[biggulp]

looks like something wrong with your random functions.:confused2