#1
  1. No Profile Picture
    Nightwork
    Guest
    Devshed Newbie (0 - 499 posts)
    <?php

    $result = mysql_query ("SELECT count(*) FROM table");
    list($total) = mysql_fetch_row($result);
    srand((double)microtime()*1000000);
    $random=rand(0, $total-1);
    $result=mysql_query("select * from table where id='$random'");
    $row=mysql_fetch_row($result);

    echo "$row[1] $row[2] $row[3]";

    ?>

    I wrote this little piece of junk to randomly pick a row and that works ok BUT it doesn't take care of the fact that the database is manipulate, so there might be hole's in the id's (numbers missing). How can I validate if an randomized id actually leads to a excisting row?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jul 2000
    Posts
    669
    Rep Power
    15
    <?php
    $result = mysql_query ("SELECT distinct ID FROM table");
    while ($row = mysql_fetch_array($result)) {
    $randoms[] = $row["ID"];
    }
    srand((double)microtime()*1000000);
    $random=rand(0, count($randoms) - 1);

    $result=mysql_query("select * from table where id='$randoms[$random]'");
    $row=mysql_fetch_row($result);

    echo "$row[1] $row[2] $row[3]";

    ?>

  4. #3
  5. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15
    Nightwork,

    you can use a simple sql query for getting random row from mysqldatabase..

    use the following..

    -------------
    "SELECT * FROM tablename WHERE id > 0 ORDER BY RAND()"

    Note that a RAND() in a WHERE clause will be re-evaluated every time the WHERE is executed

    ----------------
    If you are using the current version of mysql.then probably the above sqlstatement may not work.so you can try another query..
    -------
    select *, id*0+rand() as a from tablename order by a limit 1;
    -------------


    Good Luck!!!




    ------------------
    SR -
    webshiju.com

    "The fear of the LORD is the beginning of knowledge..."
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Dresden, GERMANY
    Posts
    3
    Rep Power
    0
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by Shiju Rajan:
    Nightwork,
    "SELECT * FROM tablename WHERE id > 0 ORDER BY RAND()"
    [/quote]

    that works only with MySQL 3.23 and above.

    My solution is to add a float field called "mixer" and this:

    update table set mixer=rand();
    select from table order by mixer;

IMN logo majestic logo threadwatch logo seochat tools logo