SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: Random

  1. #1
    SitePoint Guru
    Join Date
    Oct 2001
    Location
    USA
    Posts
    764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Random

    I need something that calls truely random id's out of my tables. For some reason rand() doesn't seem to work. I mean it calls random id's but I seem to see most of the same ones called maybe 70-80% of the time. Any idea how I could get it more random than that?

  2. #2
    webality...dunno what it means bcp_2005's Avatar
    Join Date
    Jun 2002
    Location
    Huntsville Alabama
    Posts
    394
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can try this tutorial:
    http://robouk.mchost.com/tuts/tutori...=randomnumbers
    I'm not sure how random the numbers will be but you can give it a shot .

    -Bryan

  3. #3
    SitePoint Enthusiast wogboy's Avatar
    Join Date
    Jun 2002
    Location
    Melbourne
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Edit:


    Just saw this tidbit in the usernotes section of the PHP manual. Try changing your query instead of your PHP code:

    Code:
    SELECT * FROM tablename ORDER BY RAND() LIMIT 1
    This will select a random row from your table apparently, but is not recommended for tables with a lot (100k or more maybe) records.


    Make sure you aren't seeding the random number generator
    with the same value each time. Rather, seed it with a reference to the current time as that will mostly guarantee a unique seed each time.

    Something like this in your code somewhere:

    PHP Code:
    srand((double)microtime()*1000000); 
    Just make sure that is only performed once in any one script run though.

    Also check that you have a decent sized range in your call to the rand() function, if any have been specified.

    BTW, PHP 4.2 and later automatically seeds the random number generator for you if you haven't done so manually yourself before calling rand() or a similar function.

    Failing all that, post your code where the behaviour is occuring.
    Last edited by wogboy; Feb 16, 2003 at 22:17.

  4. #4
    SitePoint Guru
    Join Date
    Oct 2001
    Location
    USA
    Posts
    764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am using the rand() in the sql statement. It still is not giving me a truely random nummber. Like I said it is giving me the 1st id and some other random id 70% of the time. I think this is weird since there is 64 numbers it could come up with.

  5. #5
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i am having a problem with this too. seems like a problem with MySQL to me. if i try it from PHP, where it disconnects from MySQL at the end, i always get the first row. and the first time i run the query from the mysql command line i have the same result. if i run the query again on the same connection, it starts returning random rows, like it should.

    it almost seems like a problem with how the MySQL server seeds the random number generator internally for each connection. something is definitely wrong, though...
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  6. #6
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you tried seeding it?

    Code:
    SELECT what FROM what ORDER BY RAND(NOW()) LIMIT 1
    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  7. #7
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by seanf
    Have you tried seeding it?
    yes, but that just makes it worse (i get the same value every time) since RAND() with an argument makes it return the same value on every iteration (row i guess).

  8. #8
    SitePoint Addict mr tinkles's Avatar
    Join Date
    Jan 2003
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    truly random generators just don't exist...

    but that doesn't really help you.

    if the mySQL can't handle, you should be able thru PHP, as in:

    if you know you have, or find out you have 64 rows, and want just 1...

    $int = rand (0, 64);

    Then select from DB tbl.

    if you have 64 rows, but no idea of range of ids, then:

    SELECT min(id), max(id) FROM tbl;

    $int = rand ($min, $max);

    Then, try select, now rows, try again until you get one.

    If you need selected multiple ids to be random, sure would be nice if mySQL worked, but still can use PHP.

    SELECT id into array, then use "mixed array_rand ( array input [, int num_req])" and reselect.

    Less than ideal, but if mySQL no workey, ???

  9. #9
    Hi there! Owen's Avatar
    Join Date
    Jan 2000
    Location
    CA
    Posts
    1,165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do 10,000 queries--I think you'll find that they are decently randomized. Two queries taken really quickly (w/in one second) will return the same answer, but over a long time they'll be random. I use the mySQL rand function for an ad server, and the numbers do work out.

    Owen

  10. #10
    SitePoint Guru
    Join Date
    Oct 2001
    Location
    USA
    Posts
    764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To what Owen said

    My site runs a cron job once every 24 hours and it still isnt pulling random numbers. So I do not agree with your 10k querries rather than querries taken quickly comment. That random function honestly looks for the first thing that satifies the sql statment and uses it, at least 70% of the time. Probably more. Has anyone since thought of a way to fix this problem. Is it anything the makers of php know about and plan on fixing?

  11. #11
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this should be fixed according to the change logs for 4.0.10 and 3.23.56. other than that, it seems to be a problem on new connections. it seems to fix it if you just call RAND() a couple times before using it in your ORDER BY:

    PHP Code:
    <?php

    mysql_query
    ('SELECT RAND(), RAND(), RAND()');

    $r mysql_query('SELECT * FROM table ORDER BY RAND() LIMIT 1');

    Quote Originally Posted by 321web
    That random function honestly looks for the first thing that satifies the sql statment and uses it
    there's nothing that "satisfies the sql statement." RAND() is used in the ORDER BY, not the WHERE, therefore there's nothing to satisfy. the problem was with the rand initialization (the first time it's used) on new connections from what it sounds like in the change log.


    Quote Originally Posted by 321web
    Is it anything the makers of php know about and plan on fixing?
    it was a MySQL problem, not a PHP one. and again, it sounds like it should be fixed since 4.0.10 and 3.23.56. otherwise, you can use my workaround of calling RAND() a few times first.

  12. #12
    SitePoint Guru
    Join Date
    Oct 2001
    Location
    USA
    Posts
    764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah I have 3.23.54 just missed the fix. Can anyone point me in the right direction to update my version? Maybe a good tutorial? I have never done it before.

  13. #13
    SitePoint Guru
    Join Date
    Oct 2001
    Location
    USA
    Posts
    764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    [QUOTE='DR_LaRRY_PEpPeR']there's nothing that "satisfies the sql statement." RAND() is used in the ORDER BY, not the WHERE, therefore there's nothing to satisfy. [img]images/smilies/smile.gif[/img] the problem was with the rand initialization (the first time it's used) on new connections from what it sounds like in the change log.[QUOTE]

    There were certain things that satisified my statement. My statement looks like

    PHP Code:
    $sql "SELECT id,title,price FROM table WHERE price > '19.99' ORDER BY rand() LIMIT 2" 
    What I am saying is it would look for the first id with a price of 19.99 and just display it 70% of the time. All that doesnt matter now though cause I just need to update.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •