SitePoint Sponsor |
|
User Tag List
Results 1 to 11 of 11
Thread: random record
-
Oct 14, 2000, 17:20 #1
- Join Date
- Jul 2000
- Posts
- 69
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
hi,
I was wondering if it's possible to select a random record from a table in a mysql database.
i've read the php.net manual, and below is the code that they suggested:
Code:SELECT * FROM table ORDER BY RAND() LIMIT 0,1
can somebody help?
Thank you
-
Oct 14, 2000, 19:12 #2
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
ORDER BY RAND() works only as of MySQL 3.23.3. Paul DuBois' book, MySQL, provides an alternate method that works on older versions of MySQL.
What you need to do is SELECT a column of random numbers and then sort on that column. However, the following query will not quite work:
SELECT ..., RAND() AS rand_col FROM ... ORDER BY rand_col LIMIT 1
The MySQL optimizing engine sees this query and says "oh, rand_col is just the result of the same function in every row, so I'll just run RAND() once and treat rand_col as if every row contained the constant result of that function." This effectively optimizes the ORDER BY clause right out of the query!
To fool the optimizer into running the RAND() function to get a different random number for each row, refer to another column in the table in the expression. For example, if you have an ID column in your table, you can do this:
SELECT ..., ID*0+RAND() AS rand_col FROM ... ORDER BY rand_col LIMIT 1
Ta da!
Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Oct 14, 2000, 19:23 #3
- Join Date
- Aug 2000
- Location
- Silicon Valley
- Posts
- 2,241
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
it seems the same thing with length() ???
- Son Nguyen
AdSpeed.com - Ad Serving and Ad Management Made Easy
-
Oct 14, 2000, 19:25 #4
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Depends how you're using it. Can you provide an example?
Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Oct 14, 2000, 19:43 #5
- Join Date
- Jul 2000
- Posts
- 69
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
hi,
so what would my sql statement be if i have a table with 2 columns, ID , and Email, and there's 11 records?
forgive me for asking too much since i've just started with this!
-
Oct 14, 2000, 19:50 #6
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
SELECT *, ID*0+RAND() AS rand_col FROM your_table_name ORDER BY rand_col LIMIT 1
Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Oct 14, 2000, 19:52 #7
- Join Date
- Jul 2000
- Posts
- 69
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
is rand_col a variable?
-
Oct 14, 2000, 19:54 #8
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Nope, rand_col is a temporary column name (known as an alias). You could actually name this anything you wanted, as long as the two occurrences in the query match.
For more information about aliases, see Part Nine of my article series:
http://www.webmasterbase.com/article...d=228&pid=1066Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Oct 14, 2000, 19:57 #9
- Join Date
- Jul 2000
- Posts
- 69
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks,
I got it to work!
by the way, is there a limit to tables in a database?
-
Oct 14, 2000, 19:59 #10
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Each table is represented by a file on your hard disk (three files, actually). As long as your file system can accomodate more files, MySQL can accomodate more tables.
You should never need an unbounded number of tables, though.Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Oct 14, 2000, 20:00 #11
- Join Date
- Jul 2000
- Posts
- 69
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
thanks a lot!
Bookmarks