ok.. Here is what i am trying to do with an mySQL table.
I want to get a random row number from the table but also from only certian rows (example: Males only)
Here is what i have done...

I can get the random number from this after i have used COUNT(*) in a previous query..

my $number = int rand $total;
my $query = ("SELECT * FROM people LIMIT $number,1");


But here is what i want to do... ( this does not work, any ideas on how to write this to make it work ?)

my $query = ("SELECT * FROM people WHERE gender = 'Male' LIMIT $number,1");

I am just learning how to use a mySQL database in programs and need all the help i can get. Anyone know of any good online resources to learn? I have seen a lot of articles etc... out there but they are all basic on how to use SELECT DELETE UPDATE etc....
I know i need to know more than this...

Thanks

try something like this:

-g


s/(\d{2})/chr($1)/ge + print if $_ = '8284703280698276687967';

You can and should use order by rand() if you are using MySQL 3.23+, however if you are still using 3.22, you need to do it in two queries:

1. SELECT COUNT(*) FROM people WHERE gender = 'Male'

Then get the results in $total.

2. SELECT * FROM people WHERE gender = 'Male' LIMIT $total, 1

To get a random male.

Cheers,

Alex

--
Gossamer Threads Inc.

Great, Thanks to both of you for your quick replies. Both bits of code worked perfect and helped me understand some more about the queries.

I am still looking for some good documentation about this but can not find any. Even book names would be helpful.

Thanks for the help!!

Well i had to come back to this post after a month or 2 cause i came in to a problem while using "order by rand()" since the program is being installed on MySQL 3.22 and not 3.23.

So here is my thoughts.. I have been trying every way possible to get around not using order by rand() but just can not do it.
I have a question regarding your post Alex...

Your steps to do this are making me wonder because i have tried this already.. But you say to count the amount of Males first and then call a random number in the other query with Limit.

What if there were only 5 males in a database of 500 rows.
The query would only count to 5 therefore the random number would only be between 1-5 while the "Males" are scattered within the 500 rows and not in the first 5.

So what i tried to do was use the ORDER BY clause and group the Males in the first 5 rows then count etc... But.. i can not figure out the proper way to do this.

Anyone have any ideas?
Ami making any sence.. :)

Hi,

Sorry, my example was a little off. You should do:

1. SELECT COUNT(*) FROM people WHERE gender = 'Male'
2. Get the $total returned from above.
3. Pick a random number between 1 and $total using my $rand = int(rand() * $total) + 1;
4. Fetch the random row using SELECT * FROM people WHERE gender = 'Male' LIMIT $rand, 1

Hope that helps,

Alex

--
Gossamer Threads Inc.

i hope i am not a bother but i am still confused here..
the code above is only counting the amount of males there are and not the rows the are on in the table.. here is what is happening..

if the count gets 5 males it will want to only get a random number between 0-5 correct ? mean while the 5 males are really on rows 3, 7, 8, 9, 10. Therefore the query will look in the first 5 rows and only find the male on row 3 and when it hits rows 1,2,4,and 5 the program can not execute.

this is what i think is happening anyway...
any more ideas .. i am very thankful for all the help everyone has given me so far .. Thank you!

drew

Hi,

No, that will work. What happens is you get 5 back from the count (total number of males). You then pick a random number between 1 and 5. Your last query looks like:

SELECT * FROM persons WHERE gender = 'MALE' LIMIT $rand, 1

The LIMIT x,y means return y rows offset by x. If you didn't have the LIMIT statement, you would get 5 rows back, 1 for each male. With the limit you get a maximum of 1 row back, offseted anywhere from 1 to 5.

Does that make sense? Let me know if you are still confused. =)

Cheers,

Alex

--
Gossamer Threads Inc.

and so you had the answer from the very beginning. I did try it to see if it worked before and i guess i thought that it was not doing exactly how i wanted it to do. But in the end yes it is doing what i want it to do. Thank you very much Alex for explaining how this all worked. I like that a lot better than just some one give out code. Now i actually know what it is doing and not just wondering..

Thanks again -> this forum rocks! ( and canadian too !!)