Please help me, I have the table of postgresql like this:
tbl_user
----------------------------------
id name is_checked
----------------------------------
1 data1 0
2 data2 0
3 data3 0
4 data4 0
5 data5 0
6 data6 0
7 data7 0
8 data8 0
9 data9 0
10 data10 0
----------------------------------
In php I have many users login. When they get data for checking, they will see data in difference because I want them to update field is_checked="1" for difference data by user:
- user 1:
select * from tbl_user limit 2;
=> result:
1 data1 0
2 data2 0
- user 2:
select * from tbl_user limit 2;
=> result:
3 data3 0
4 data4 0
- user 3:
select * from tbl_user limit 2;
=> result:
5 data5 0
6 data6 0
------------------
I want user check data without duplicate row. if one row is checking by one user, then other users not be able to see that row. Please help to give me the solution. Thank You!
Use 3 values for the is_checked field, for example:
0 means “up for grabs”
1 means “is being checked”
2 means “is checked”
Then every worker selects the values like this:
UPDATE tbl_user t
SET is_checked = 1
FROM (SELECT t1.id
FROM tbl_usr t1
WHERE t1.is_checked = 0
LIMIT 2
FOR UPDATE) t2
WHERE t.id = t2.id
RETURNING t.*;
When checking is completed, is_checked is updated to 2.
Instead of integer you might want to use an enumeration data type for is_checked.