Select random user from database table and store in another table

Question

I need the following code to loop back and select another random user if the random user it selects is already in the winners table.

The below code works but if it randomly selects a user that is already in the winners table, it doesn't try again. I have tried a few different ways but haven't found a solution that works.

Thanks in advance. (this is my first question on stackoverflow, please go easy on me if I have failed to follow any protocols)

<?php  
$host="localhost";
$user_name="db_user";
$pwd="db_pass";
$database_name="db_name";

$db=mysql_connect($host, $user_name, $pwd);

if (mysql_error() > "") print mysql_error() . "<br>";
mysql_select_db($database_name, $db);
if (mysql_error() > "") print mysql_error() . "<br>";

$sqlCommand = "SELECT userid, firstname, surname, email FROM users ORDER BY RAND() LIMIT 1"; 
$query = mysql_query($sqlCommand) or die (mysql_error());

while ($row = mysql_fetch_array($query)) { 

    $userid = $row["userid"]; 
    $firstname = $row["firstname"];  
    $surname = $row["surname"];  
    $email = $row["email"];

    $checkuserid = mysql_query("SELECT userid from winners WHERE userid=$userid");

    if (mysql_num_rows($checkuserid)==0) {
        $sqlCommand = "INSERT into winners (userid, firstname, surname, email) values ('" . $userid ."','" . $firstname . "', '" . $surname . "', '" . $email . "')";
        mysql_query($sqlCommand) or die (mysql_error()); 
    } else {

    }
} 

mysql_close(); 
header("Location: http://mysite.com"); /* Redirect browser */
exit();
?>

Is it necessary to have two tables? If not, you could just add a field (e.g. hasWon) and only select those rows, where hasWon is false. SELECT [...] FROM users WHERE hasWon = false AND id = [...] — germi, Jun 26 '13 at 08:31

score 3 · Answer 1 · edited May 25 '19 at 14:21

You can do this in one query:

INSERT INTO winners 
    SELECT userid, firstname, surname, email 
    FROM users 
    WHERE userid NOT IN (SELECT userid FROM winners) 
    ORDER BY RAND() 
    LIMIT 1

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

score 2 · Answer 2 · answered Jun 26 '13 at 08:31

Instead of looping until achieving success, you could just exclude existing winners with a subquery by replacing your $sqlCommand with:

$sqlCommand = "SELECT userid, firstname, surname, email ".
              "FROM users ".
              "WHERE userid NOT IN (SELECT userid FROM winners) ".
              "ORDER BY RAND() LIMIT 1";

SkarXa · Accepted Answer · 2013-06-26T08:35:50.757

You can change your first query to exclude users present in the other table with NOT EXISTS.

http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

It should look like this

SELECT userid, firstname, surname, email FROM users WHERE NOT EXISTS (SELECT * FROM winners
                WHERE users.userid = winners.userid) ORDER BY RAND() LIMIT 1;

Also your code didn't work because it only had one loop because of the limit 1

score 1 · Answer 4 · answered Jun 26 '13 at 08:32

1

If you want a loop, put the code fragment from the `$sqlCommand' statement to the end of the while loop within another loop.

answered Jun 26 '13 at 08:32

amulous

704
2
6
15

Select random user from database table and store in another table

4 Answers4

Linked