Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Nov 2011
    Posts
    252
    Thanks
    8
    Thanked 4 Times in 4 Posts

    Selecting random data from 2 tables

    Hi all,

    I have a database with 2 tables populated with first and last names. I am wanting to randomly select rows from each to create a full name. My code so far is -

    PHP Code:
    <?php
        
    // Connect to database server
        
    mysql_connect("host""username","password") or die (mysql_error ());

        
    // Select database
        
    mysql_select_db("DB name") or die(mysql_error());

        
    // Get data from the database depending on the value of the id in the URL
        
    $strSql "SELECT * FROM Male UNION SELECT Surname FROM Surname
            ORDER BY RAND() LIMIT 10"
    ;
    $rs mysql_query($strSql) or die(mysql_error());
        
        
    // Loop the recordset $rs
        
    while($row mysql_fetch_array($rs))
      {
      
                     
      
    // Write the data of the person
            
    echo "<table border=1 cellpadding=\"0\" cellspacing=\"0\">";
            echo 
    "<table border=1 width=100>";
      echo 
    "<td>" $row['Name'] . "</td>"
      echo 
    "<td>" $row['Surname'] . "</td>"
      
      }
      echo 
    "</table>";
    echo 
    "</table>";
    mysql_close();
    ?>
    This selects the data from both tables but it does not allow me to put it together how I want it to. I know I'm missing something simple I just don't know what.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    Let me get this straight. Let's just use 3 instead of 10 for an examples.

    Table MALE contains:
    Adam
    Bob
    Charles

    Table SURNAME contains
    Anderson
    Brown
    Carlton

    You want to randomly select pairs of names so that you would end up with something like
    Charles Brown
    Adam Carlton
    Bob Anderson

    Yes?

    Is it important not to have the same last surname repeated?

    That is, if you want 3 random selections from those names, would an answer such as this be acceptable?
    Bob Brown
    Adam Carlton
    Charles Carlton (repeated surname)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Nov 2011
    Posts
    252
    Thanks
    8
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by Old Pedant View Post
    Let me get this straight. Let's just use 3 instead of 10 for an examples.

    Table MALE contains:
    Adam
    Bob
    Charles

    Table SURNAME contains
    Anderson
    Brown
    Carlton

    You want to randomly select pairs of names so that you would end up with something like
    Charles Brown
    Adam Carlton
    Bob Anderson

    Yes?
    Yes that's what I'm wanting.

    Quote Originally Posted by Old Pedant View Post
    Is it important not to have the same last surname repeated?

    That is, if you want 3 random selections from those names, would an answer such as this be acceptable?
    Bob Brown
    Adam Carlton
    Charles Carlton (repeated surname)
    No it doesn't matter if the last name repeats.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    Okay, trivial then:
    Code:
    SELECT male.name, surname.surname
    FROM male INNER JOIN surname
    ORDER BY RAND()
    LIMIT 10;
    I should point out that it's also possible (though unlikely, and the more names you have in your tables the more unlikely) that you would get the same first name for each record (or same surname). The only guarantee from that code is that you'll never have a complete repeat.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Nov 2011
    Posts
    252
    Thanks
    8
    Thanked 4 Times in 4 Posts
    I'm getting the following error when using that code.

    Query execution was interrupted
    Could this be down to the amount of names in the tables?

    Edit; I just got an email from my webhost saying I was executing a slow query and they do not allow them on their server as they are a free host. Can anyone help with that or is it just one of those things?
    Last edited by Foster; 09-16-2013 at 03:54 PM.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    Oh, yes, the more names the slower that query will be!

    Under the circumstances, given you are using big tables and a free host, we need to avoid doing any of this in this way.

    Try this query. It may or may not work! But worth a try.
    Code:
    SELECT X.name, Y.surname
    FROM  
        ( SELECT name FROM males ORDER BY RAND() LIMIT 10 ) AS X
        INNER JOIN
        ( SELECT surname FROM surname ORDER BY RAND() LIMIT 10 ) AS Y
    ORDER BY RAND() 
    LIMIT 10;
    If that's still too slow, then we'll have to resort to using two queries and doing the matchups in PHP code. That's pretty trivial.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    The more I think about it the more I think you should just use PHP to do this.

    Thus:
    Code:
        $sqlM = "SELECT name FROM male ORDER BY RAND() LIMIT 10";
        $sqlS = "SELECT Surname FROM Surname ORDER BY RAND() LIMIT 10"; 
        $rsM = mysql_query($sqlM) or die(mysql_error()); 
        $rsS = mysql_query($sqlS) or die(mysql_error()); 
         
        while( $rowM = mysql_fetch_array($rsM) && $rowS = mysql_fetch_array($rsS) ) 
        { 
           $name = $rowM[0]; $surname = $rowS[0];
           echo "<td>$name</td><td>$surname</td>\n";  
        }
    Among other advantages, this guarantees you don't get a repeat on name or surname, at all.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Regular Coder
    Join Date
    Nov 2011
    Posts
    252
    Thanks
    8
    Thanked 4 Times in 4 Posts
    Thank you for getting back to me. I have only just been able to try this due to a few family issues.

    The below code works however it only shows surnames. I haven't tried the other code.
    Quote Originally Posted by Old Pedant View Post
    The more I think about it the more I think you should just use PHP to do this.

    Thus:
    Code:
        $sqlM = "SELECT name FROM male ORDER BY RAND() LIMIT 10";
        $sqlS = "SELECT Surname FROM Surname ORDER BY RAND() LIMIT 10"; 
        $rsM = mysql_query($sqlM) or die(mysql_error()); 
        $rsS = mysql_query($sqlS) or die(mysql_error()); 
         
        while( $rowM = mysql_fetch_array($rsM) && $rowS = mysql_fetch_array($rsS) ) 
        { 
           $name = $rowM[0]; $surname = $rowS[0];
           echo "<td>$name</td><td>$surname</td>\n";  
        }
    Among other advantages, this guarantees you don't get a repeat on name or surname, at all.

  • #9
    Regular Coder
    Join Date
    Nov 2011
    Posts
    252
    Thanks
    8
    Thanked 4 Times in 4 Posts
    This one works! Thank you.
    Quote Originally Posted by Old Pedant View Post
    Oh, yes, the more names the slower that query will be!

    Under the circumstances, given you are using big tables and a free host, we need to avoid doing any of this in this way.

    Try this query. It may or may not work! But worth a try.
    Code:
    SELECT X.name, Y.surname
    FROM  
        ( SELECT name FROM males ORDER BY RAND() LIMIT 10 ) AS X
        INNER JOIN
        ( SELECT surname FROM surname ORDER BY RAND() LIMIT 10 ) AS Y
    ORDER BY RAND() 
    LIMIT 10;
    If that's still too slow, then we'll have to resort to using two queries and doing the matchups in PHP code. That's pretty trivial.


  •  

    Posting Permissions

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