Page 1 of 1

two questions on generating a random user

Posted: Wed Sep 10, 2003 10:32 am
by m3rajk

Code: Select all

$frm=mysql_query("SELECT users.gender,stats.dob,stats.sex_pref,stats.mar_stat,stats.country FROM users,stats WHERE users.uid=stats.uid AND users.uid=
obviously that's imcomplete. what i'm not sure is how to randomly generate a user id number. rand() takes a seed, and i'm not going to know how many users there are prior to running this select. so i'm not sure how to randomly get a valid number (there will probably be holes as people delete from the site, etc.

also, once i have the array will it be $array['table.field'] or $array['field'] to access the array?
i haven't tried to get things form multiple tables before.
thnx in advance
-Josh

Posted: Wed Sep 10, 2003 11:18 am
by trollll
If I understand the goal correctly, you could probably use subqueries to make sure that you generate a random number in between the highest and lowest value and then have the query return a row "WHERE userID > random_number LIMIT 1" (assuming you have it generate a number less than the highest userID, but could return a number lower than the lowest userID) to get the first row that meets the criteria.

Hope that made sense...

I usually either use mysql_fetch_row to use $array[0], $array[1]... or use an associative array but have the query set up like:

Code: Select all

SELECT users.gender AS 'gender',stats.dob AS 'dob',stats.sex_pref AS 'sex_pref',stats.mar_stat AS 'mar_stat',stats.country AS 'country' FROM...

Posted: Wed Sep 10, 2003 12:48 pm
by JAM
Worked for me...

Code: Select all

"select field from test ORDER BY RAND(MD5(NOW())*NOW()) LIMIT 1"

Posted: Wed Sep 10, 2003 7:48 pm
by m3rajk
trollll: ok. neither the mysql doc page nor what you said are clear to me. it almost sounds as if you can't do it under 4.1 but then talks about only being able to do that, so the page lost me, i wen tot it hoping to get a clearer understanding of what you gave me

maybe this will help clear up my goal?

i have a database with user id numbers assigned when you sign up.

some people may delete, some may be deleted. this leaves holes.

for the front page i want to seelct a user at random and pull some information for a section.

so i want to pull a randome user between 1 and an unknown maximum.

i'm hoping to find a way to do this so that it'll change the user displayed with each load of the index.

Posted: Wed Sep 10, 2003 8:45 pm
by Unipus
I would retrieve all of the results that might be possibilities (because you're retrieving all that data anyway, right?). Then, I would use the array generated by the ID column of the user table and use PHP's shuffle() function to handle the rest.

Posted: Wed Sep 10, 2003 9:11 pm
by trollll
Started typing and then some stupid script crashed the browser...

Okay, I started thinking along Unipus' line of thinking, which would work. If you may end up having scores or users and don't want a large query, you could also do something like

Code: Select all

$count_result = mysql_query("SELECT COUNT(users.uid) FROM users, stats WHERE users.uid=stats.uid");
$random_number = rand(0, mysql_result($count_result, 0) - 1);
$random_user_result = mysql_query("SELECT users.gender AS 'gender',stats.dob AS 'dob',stats.sex_pref AS 'sex_pref',stats.mar_stat AS 'mar_stat',stats.country AS 'country' FROM users, stats WHERE users.uid=stats.ui LIMIT $random_number, 1");
Or something like that. That way it should work just fine even if you end up with half a million users. Don't remember if MySQL < 4 allows you to use "LIMIT a, b" but if not, just use "START AT a LIMIT b" instead and it should keep MySQL happy. Unipus' method would work fine, I just can't help but think of scalability. :roll:

Posted: Wed Sep 10, 2003 10:06 pm
by m3rajk
yeah. i want something scable. betwween my post and getting back to the responses now, someone else suggested something that seems to work as one query....

Code: Select all

SELECT users.gender,stats.dob,stats.sex_pref,stats.mar_stat,stats.country
FROM users
INNER JOIN stats ON users.uid=stats.uid
ORDER BY rand()
LIMIT 1

Posted: Wed Sep 10, 2003 10:39 pm
by JAM
Heh, yah well...

Just as info, I'm using mysql < v.4

Seeding the rand() in that was is just an example. Using only NOW() is, i think, not good enough. NOW()*PI might also help. (Actually, using MD5 is a bad example, as that is a string... But the point is there.)
I cant voutch for that using rand() without seeding it is any good.

Posted: Thu Sep 11, 2003 1:46 pm
by m3rajk
it seems to work. (i'm on mysql 3.23.56)