SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Wizard
    Join Date
    Jul 2001
    Location
    The Netherlands
    Posts
    2,617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting a random id from a database

    I am trying to build a script, which selects a random ID from my MySQL database. The ID's are all integers, ranging from 1 to whatever ID is the last is the database.

    I have been trying to get a script running that selects a random number out of this heap of integers. This is what I have so far:

    PHP Code:
    <?php
    $dbx 
    mysql_connect() or die("Could not connect to database");
    mysql_select_db (Jokes$dbx);
                              
    $sql "SELECT MAX(id) FROM Jokes";
    $result mysql_query($sql);
                              

    ?>
    As you can see, I have a query requesting the maximum ID in the table. I now want to select a random number which has to be between 1 and the maximum id I requested. How would I integrate this in this script?

    Thanks in advance .

  2. #2
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you just want to get a random record and have MySQL 3.23 or higher you can use ORDER BY rand()

    Take a look at this example and freddy's code if you don't have 3.23:

    http://sitepointforums.com/showthrea...highlight=RAND

    Sean
    Harry Potter

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

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2001
    Location
    The Netherlands
    Posts
    2,617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I have MySQL 3.23.22, which looks sufficient to me . But I want to select one of the random numbers, and I presume ORDER By RAND() (thanks for introducing me to that function btw ), will generate a random list of all the ID's...

  4. #4
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, use it like this to get one random id:

    PHP Code:
    $sql mysql_query("SELECT ID FROM table ORDER BY rand() LIMIT 1") or die(mysql_error()); 
    Sean
    Harry Potter

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

  5. #5
    SitePoint Wizard
    Join Date
    Jul 2001
    Location
    The Netherlands
    Posts
    2,617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I had the code set up like that, I only missed the LIMIT 1. I owe you one !

  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)
    Your welcome And if you ever see me in a pub

    Sean
    Harry Potter

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

  7. #7
    SitePoint Wizard
    Join Date
    Jul 2001
    Location
    The Netherlands
    Posts
    2,617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, if you help me out one more time, I will certainly do that .

    I now have this code:

    PHP Code:
    <?php
    $dbx 
    mysql_connect() or die("Could not connect to database");
    mysql_select_db (Jokes$dbx);

    $sql "SELECT ID FROM Jokes ORDER By RAND(id) LIMIT 1";
    $result mysql_query($sql);

    [
    b]$sql "SELECT Joke FROM Jokes WHERE ID = '$result'";[/b]

    $result mysql_query($sql);
    while (
    $row mysql_fetch_array($result)) {
    echo (
    $row["Joke"]);
    ?>
    Can you spot the flaw? I think it is in the bold line of code, but I am not sure what is wrong.

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $result holds nothing more than a ref to the query result and can't be used in this fashion. Also there is not need to run two queries. Also since you are only getting one record you don't need the while loop around mysql_fetch_array()

    PHP Code:
    <?php
    $dbx 
    mysql_connect() or die("Could not connect to database");
    mysql_select_db (Jokes$dbx);

    $sql "SELECT * FROM Jokes ORDER By RAND() LIMIT 1";
    $result mysql_query($sql);
    $row mysql_fetch_array($result);
    echo (
    $row["Joke"]);
    ?>
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  9. #9
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would change your code to this Mark:

    PHP Code:
    $dbx mysql_connect() or die("Could not connect to database");
    mysql_select_db (Jokes$dbx);

    $sql mysql_query("SELECT Joke FROM Jokes ORDER BY rand() LIMIT 1") or die(mysql_error());
    $result mysql_fetch_array($sql);

    extract($result);

    echo 
    $Joke
    Sean
    Harry Potter

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

  10. #10
    SitePoint Wizard
    Join Date
    Jul 2001
    Location
    The Netherlands
    Posts
    2,617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just keep learning new tricks every day. Does extract() "unpack" the result to a string? The rest of the code seems quite familiar, only a few little changes.

  11. #11
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It saves you having to do this:

    PHP Code:
    $sean $result["sean"]; 
    Sean
    Harry Potter

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

  12. #12
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://www.php.net/extract


    It takes an array and creates variables out of each element using the key as the variable name and the value as the value of the element.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  13. #13
    SitePoint Wizard
    Join Date
    Jul 2001
    Location
    The Netherlands
    Posts
    2,617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Two PHP-Wizards coming to my aid for such a simple script. I feel honored ! Thanks freddy and sean, everything is working just the way I want it right now .


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
  •