Missing ids

[Anon]

I am storing banners in a mysql database, each banner has an id that unique. I use a php script to generate a random number then display that banner that's associated with the number based on its id. But I have a problem because some of the banners have been removed so those ids no longer exist, is their a way to assign banners to id numbers that have been skipped or removed?

[dmcinnis]

Try this.

• generate your random number
• select from mysql
• if mysql_num_rows == 0 add one to the random number and cycle again. If you hit the ceiling (highest id number in your database) generate another random number and cycle again.

[[deleted]]

• if mysql_num_rows == 0 add one to the random number and cycle again. If you hit the ceiling (highest id number in your database) generate another random number and cycle again.

I wouldn't do that. Imagine you had 10 banners, and deleted banners 2-9.
Now 8 out of ten times you'll pick a number in the 2-9 range, and every time you'll end up at 10, so you're showing banner 10 9 out of 10 times.

Instead, run a query that simply does
SELECT * FROM banners ORDER BY rand() LIMIT 1;

[dmcinnis]

That is a much better idea.

David

[Anon]

I have never used rand() in this way before , do I still need to seed the varrable before getting the random entry, what I use now is something like this.

srand((double)microtime()*1000000);
$randomnumber = rand(1,6);

thanks
Ransom

[[deleted]]

Dont get confused, there is a rand() for PHP and a rand() for mysql.

The PHP rand() requires that you seed it at least once before you use rand().
The MySQL rand() is ready-to-use, but only lives inside MySQL queries.

if you do

SELECT * FROM table ORDER BY RAND() LIMIT 1

mysql will select all the records in random order, and return only the first one it selected (so it will be random).

Just run the query and fetch the first row it returns. That's all.

[Anon]

ok
thanks allot for all the help 🙂

Ransom