CODEWITHSUNDEEP
phpmysqltransactions
Ty Rozak
Ty Rozak

Reputation: 471

Database Insert into Random Unused Row - With Transaction

I am writing an app that wishes to randomly assign a number to users, then puts in into a MySql database. There are many people who use it at the same time and as such I dont want parallel uses to overwite each other.

My current code is the following:

$sql_get = "SELECT * FROM database";
$results = mysql_query($sql_get, $bd);

$list = array();
while($row = mysql_fetch_array($results))
    {
        if ($row['userId']  == "")
        {
            array_push($list, $row['number']);
        }
    }

$rand_nums = array_rand($list , 1); 

$sql_update = "UPDATE database SET userId='". $userId ."' WHERE number=". $rand_nums;
$results = mysql_query($sql_update, $bd);

So basically, it gets the empty rows, puts them into a list, chooses a random empty row number and puts the data into the row. The current issue is that the get and the empty rows can happen at the same time for multiple users and may overwrite data written at the same time.

How can I structure this code (transaction or otherwise) to ensure concurrent use has no bad effects?

Thank you

Upvotes: 0

Views: 29

Answers (1)

Barmar
Barmar

Reputation: 782693

You could do it all in one query:

UPDATE database AS d
SET d.userId = $userId
WHERE d.userId = ''
ORDER BY RAND()
LIMIT 1

Upvotes: 1

Related Questions