two questions on generating a random user

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

two questions on generating a random user

Post 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
User avatar
trollll
Forum Contributor
Posts: 181
Joined: Tue Jun 10, 2003 11:56 pm
Location: Round Rock, TX
Contact:

Post 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...
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Worked for me...

Code: Select all

"select field from test ORDER BY RAND(MD5(NOW())*NOW()) LIMIT 1"
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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.
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post 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.
User avatar
trollll
Forum Contributor
Posts: 181
Joined: Tue Jun 10, 2003 11:56 pm
Location: Round Rock, TX
Contact:

Post 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:
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post by m3rajk »

it seems to work. (i'm on mysql 3.23.56)
Post Reply