0

I Have the following code:

<?php
error_reporting(E_ALL);

// connect to your MySQL database here 
$dbhandle = mysql_connect('xxx', 'xxxx', 'xxxx');
$selected = mysql_select_db("xxxx",$dbhandle);

// Get the winner data
function setWinner(){
    $sql = 'UPDATE user SET winner = 1 WHERE id=(SELECT id FROM user ORDER BY RAND())';
    $query = mysql_query($sql) or die (mysql_error());
    echo $query;
}
// Get the winner data
function getWinner()
{
    $sql = 'SELECT id, fullname, email, number FROM user WHERE winner = 1';
    $query = mysql_query($sql) or die (mysql_error());

    if(mysql_num_rows($query) == 1) {
        $user = mysql_fetch_assoc($query);

    } else {
        // If there is no winner yet, automatically create one
        setWinner();

        // Recall the function which should now find a winner
        $user = getWinner();        
    }


    return $user;
}
$winner = getWinner();
print_r($winner);
?>

Trying to work with g4vroche's answer but getting this error: You can't specify target table 'user' for update in FROM clause

I used this for a competition to select a random user from the database, but i now need it to look through all the users to see if there's a user that's winner column equals 1 and if there isn't one it should select the random user and update that users winner column with 1.

Any Help greatly appreciated.

Improve this question
5
  • Why not just use two queries? That would work just fine, and you wouldn't need to use some complex nested query. Commented Sep 6, 2013 at 13:35
  • because i am stupid :), i did try the two queries route i probably did something wrong.. could you show some example code? Commented Sep 6, 2013 at 13:36
  • You really should stop using mysql_* and start using mysqli_* or PDO as mysql_* is now deprecated. Read more: php.net/manual/en/function.mysql-query.php Commented Sep 6, 2013 at 13:38
  • @DawidvanderHoven show what you have and maybe we can fix it. Commented Sep 6, 2013 at 13:39
  • you know what.. i am using mysqli.. i guess i am just use to saying mysql sorry :( Commented Sep 6, 2013 at 13:39

2 Answers 2

Reset to default
1

I'm using the mysql_query() function just because you used it in your example. You should read up on PDO and implement that instead.

Per my comment above, 

require_once "connect_to_mysql.php"; 
// look for records with `winner` set to 1
$result = mysql_query('SELECT id, fullname, email, number FROM user WHERE winner = 1 LIMIT 1') or die (mysql_error());
if (mysql_num_rows($result) < 1) { // if no records were found, pick a random row
    $result = mysql_query('SELECT id, fullname, email, number FROM user ORDER BY RAND() LIMIT 1');
}
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Would I then set winner = 1 if not found in the second query or?
You'd set winner = 1 for whichever record the second query found (since it'd already be set if returned by the first query). The problem here is that you'd have to UPDATE user SET winner = 0 and reset everyone's "winner" status at some point so the same user doesn't get returned over and over again.
0

I don't know what your winner column actually stores, but assuming it's a boolean, which may contains only 0 or 1, you could do this in a single query :

SELECT id, fullname, email, number FROM user ORDER BY winner DESC, RAND() LIMIT 1;

Which will :

  • Sort all records on winner column, making rows hanving winner = 1 firsts
  • Sort equals records with RAND()

So you will get and random result, priority being given to records having winner column equals to "1".

EDIT

I missunderstood your need.

You have two different process here

  • Get the winner
  • Set the winner

You should probably split that into two function / methods

// Get the winner data
function getWinner()
{
    $sql = 'SELECT id, fullname, email, number FROM user winner=1';
    $query = mysql_query($sql);

    mysql_num_rows($result) == 1) {
        $user = mysql_fetc_assoc($query);           
        return $user;
    }

    return false;
}

function setWinner()
{
    $sql = 'UPDATE user SET winner=1 ORDER BY RAND() LIMIT 1';
    mysql_query($sql);
}

// Call this one time
setWiner();

// Call this any time you want to get the winner's data
$winner = getWinner();

A more lazy approach, will be to make the getWinner() function to call setwinner if there is no winner yet :

// Get the winner data
function getWinner()
{
    $sql = 'SELECT id, fullname, email, number FROM user winner=1';
    $query = mysql_query($sql);

    mysql_num_rows($result) == 1) {
        $user = mysql_fetc_assoc($query);

    } else {
        // If there is no winner yet, automatically create one
        setWinner();

        // Recall the function which should now find a winner
        $user = getWinner();        
    }


    return $user;
}

That's up to your context, but on general point of view it's bad practice, because a function which looks like reading data (getSomething) will, under some circumstances, also alter data (call to setWinner().

Improve this answer

4 Comments

but all the winners are 0 from the start then when i need to pick a random user i also need to update his winner column to 1
Ok I misunderstood your need. That is totally two differents operations. I'll edit my answer
You can't specify target table 'user' for update in FROM clause
You are right, Mysql doesn't handle this. I edited the example code with a working (tested) query to do that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.