Hello, I have a "jobs" table and many workers are going to retrieve a job from it at the same time. I want to make sure that every worker retrieves a unique job, so I want to update the jobs table when a job is retrieved. Can I do SELECT and UPDATE in the same query? Or how could I accomplish this? Thank you very much.
Why you want to do SELECT and UPDATE in the same query? You can do this by turns. First do "SELECT", then "UPDATE".
I would add a "userID field" and then run update first and set it to be taken by the user and then select it. that way no other user can take the job
Interesting solution. But in order for me to find out which job to be updated, I need to do a SELECT statement first. Basically, I need to sort the jobs by date inserted, then pick the oldest one.
Still works the same way.. update blabla set userid='' and taken='true' where <where clause> order by oldest select <what_you_want> from blabla where userid='' ORDER by newest this would give you the latest job you assigned to that user
Thanks n3r0x for your follow up. I have tried what you said before. But I got the following error: Sql Error: You can't specify target table 'jobs' for update in FROM clause Here is my sql: UPDATE jobs SET started_at = '$now' WHERE id in (SELECT MIN(id) AS id FROM jobs WHERE started_at IS NULL) I use start_at to indicate a job is taken. MIN(ID) is gonna be the oldest job.
Hi, please post you create table script. Why you get oldest by ID not by datetime colunm? Regards Edit: If you want to try yourself, just take a look to this thread : solution is similar!
Thank you. I will take a look at it. I think I am getting the sql error because I can't update and select from the same table at the same time.
UPDATE jobs JOIN (SELECT COALESCE(MIN(id),0) MINID FROM jobs WHERE started_at IS NULL) A ON A.MINID=jobs.id SET jobs.started_at = NOW(); Code (markup):
Thanks guys for trying to help me. After many hours of searching on the net, I found a super simple and straight forward solution that doesn't require sub-query. Hope it can help other people. UPDATE jobs SET started_at = '$now' WHERE started_at IS NULL ORDER BY id LIMIT 1
There is no need to define a variable on the jobs table. This will become harder to manage if you will allow users to drop their jobs to pick up another one (or perhaps you can delete users). $job_id = $_POST['job_id']; $sql = "SELECT jobs.id FROM jobs JOIN users ON users.job_id=jobs.id WHERE jobs.id='{$job_id}'"; Code (markup): This will return 0 rows if no-one has taken that job. You then perform your UPDATE to change the job_id on the user. Im sorry i cannot answer your question because i do not know how to UPDATE and SELECT with the mentioned methods. For your adopted method, i believe what i posted will be easier to maintain.
I not sure if I understand you, but user is not assigned with a job_id in advance. Whoever user finishes his job first will come to pick up new job.
I would see the jobs as tasks. With that in mind, you would assign a job to a particular user, using their id. Any job without an assigned user can be collected by a user. You would run two queries. You can use SELECT, and then UPDATE. $query = mysql_query("SELECT id, name FROM jobs WHERE assigned_user_id=0") or print(mysql_error()); while ($data = mysql_fetch_array($query)) { //outputs like this, 1 : job name : link to take job echo $data['id'] . " : " . $data['name'] . " : <a href='?action=take_job&id='" . $data['id'] . "'>click here to take this job</a> <br />"; } if ($_GET['action'] == 'take_job') { $user_id = $_SESSION['user_id']; //or wherever you store your users id $job_id = $_GET['id']; mysql_query("UPDATE jobs SET assigned_user_id='" . $user_id . "' WHERE id='" . $job_id . "'") or print(mysql_error()); echo "Job taken"; } Code (markup): And then build in some prevention to stop users taking more than one job and make it look pretty. Thats your job.
I think you are missing the main point here. Remember my question was "Can you do SELECT and UPDATE query in the same scoop in MYSQL?" I want to do two things in one scoop to avoid concurrency issue. Imagine I have couple thousands of users try to get available jobs at the same time and if you do this in two seperate queries, that is big possibility that two users will get the same job. Here is a scenario: Let's say job A is oldest job and User A is here and select job A. Right before User A update the database, User B is here to grab a job as well. Then, User B will get assigned to job A too since User A hasn't update the database. We talking about things in less than a second here. It might not overlap like this if you only have 2 or 3 users, but with thousands, this will be an issue.
Would still be solved by updating first and selecting after.. since the user have already taken the job the second user can´t take it.. making it impossible that 2 users gets the same job.. what i don´t understand is why you have to use "1" query and complicate it more than it have to be?
Because you can't do this: "update blabla set userid='' and taken='true' where <where clause> order by oldest select <what_you_want> from blabla where userid='' ORDER by newest " You can't update and select from the same table. That's why I found out about this: UPDATE jobs SET started_at = '$now' WHERE started_at IS NULL ORDER BY id LIMIT 1
ofc i never said you had to run them in the same query^^ First mysql_query("UPDATE") THen mysql_query("SELECT") would solve the problem with 2 ppl not being able to take the same job.........
I said add in your prevention, which you already had. Here is the revision. You cannot SELECT and UPDATE. You can however choose which rows to update with WHERE, simples. if ($_GET['action'] == 'take_job') { $user_id = $_SESSION['user_id']; //or wherever you store your users id $job_id = $_GET['id']; mysql_query("UPDATE jobs SET assigned_user_id='" . $user_id . "' WHERE id='" . $job_id . "' AND assigned_user_id=0") or print(mysql_error()); echo "Job taken"; } Code (markup):
That's assume that you know the job id already. But in my case which job will be chosen requires a query.