CODEWITHSUNDEEP
phpmysql
jono
jono

Reputation: 1842

Select random user from database table and store in another table

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();
?>

Upvotes: 0

Views: 277

Answers (4)

Fracsi
Fracsi

Reputation: 2314

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.

Upvotes: 3

SkarXa
SkarXa

Reputation: 1194

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

Upvotes: 1

amulous
amulous

Reputation: 732

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

Upvotes: 1

rebroken
rebroken

Reputation: 633

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";

Upvotes: 2

Related Questions