[RESOLVED] Problem selecting a random userid

[wwwfilmfilercom]

I'm trying to do a simple thing but run into a problem. All I want to do is pick a random 'userid' and display that on my page.

To come up with random number I do the following:

$res =& $mdb2->query('SELECT * FROM users');
$num = $res->numRows();
$random = (rand()%$num+1);

i.e. Get the number of rows in my table of users and then pick a random number from it.

However, I have noticed this isn't a good method since my 'userids' are not all sequential. For instance during testing etc. I have deleted id's 3, 4, 5, etc. So if I get random number 3, 4, ,5 etc. I end up with an error.

Is there a better way to select a random 'userid' from the ones which definitely exist?

* Also please note the code above uses PEAR MDB2 Class...

[kows]

well, it might be a little sloppy but, you could build an array that has keys of the user IDs. you can do this by looping through your database and adding onto the array for each entry in your table. this will make sure that your "range" of user IDs are only of those that actually exist. when you're finished building the array, you can use array_rand() to pick a random key out of it. the only reason this is sloppy is because you're going to have to loop through all of the valid users in the database to build the array (even though you might only be grabbing the key); and depending on the size of the database this could definitely hinder performance.

other than that, I have no idea of how you could obtain all of the valid keys so that you could only pick from them. you could make a recursive function that kept picking a random number over and over until a query result returned empty.. however, this could produce numerous un-needed queries on your database, and if you have 100 users online at a time.. this would probably be a bigger performance hit if a bunch of them kept hitting unassigned keys.

hope that helps. if not, someone else might have a better idea.

[penagate]

Try:

select userid from users order by rand() limit 1

[wwwfilmfilercom]

That works great Penagate - thanks. Thanks to Kows too