Php Calender Week Random User Calculation

Question

I got an database (phpmyadmin) in which I put users, grid looks like this

+-----+---------+-----------------+----------------+
| id  |  user   |  categorie_bit  |  calender_week |
+-----+---------+-----------------+----------------+
|  1  |  Kevin  |              01 |             39 |
+-----+---------+-----------------+----------------+

the id and user is self explained. Categorie_bit stands for the current job he / she has to do like having a walk with the dog.

0 - no job

1 - job 1

10 - job 2

11 - both jobs

and the calender week shows the current calender week.

like this in php:

$date = new DateTime();     
echo $date->format('W');

I got various users, each user has to do a job for this calenderweek for this instance 39. The user is picked randomly from the database. (I used a PDO Wrapper)

$table = "users";
$columns = array("id", "firstname");
$orderBy = array('RAND()');

$result_set = $db->select($table,$columns, Null, Null, $orderBy, 1);

In the next step I give the user a "job".

Categorie_bit = 1.

The problem is now that I want to pick a user who did not have a "job" last calender week, so that all users are treated equally.

Edit: How do I accomplish this for further usage, lets say user 1 had to work week 39 and user 2 for week 44 and so on...

My first idea is to change the random picker somehow.

Anyone knows how to accomplish this?

You're aware for the bit you can just store it as a decimal integer, so you'd just have 0, 1 or 2 (and 3 for both jobs). You can then use PHPs bitwise operators to test them. — Styphon
– Styphon, Commented Sep 24, 2015 at 10:40
I did this to be more dynamic later on, like if I want to add something — as21
– as21, Commented Sep 24, 2015 at 10:43
Sure, so if you wanted to add on another job, job 3 then you'd have a binary number of 100, which is 4. You can just store decimal 4 for job 3, then decimal 8 for job 4. If they have multiple jobs you just add the numbers together, so job 1 and job 3 would be decimal 5, binary 101. — Styphon
– Styphon, Commented Sep 24, 2015 at 10:44
Ok Thanks,well I want to keep the categorie_bits like it is now, do not misunderstand. I did this also for learning matters:) But again thank you! — as21
– as21, Commented Sep 24, 2015 at 10:47

Styphon · Accepted Answer · 2015-09-24 11:01:09Z

1

Just add a WHERE clause to your query checking if there's an entry last week for your user. I'm not familiar with your PDO wrapper but the resultant query should be:

SELECT u.id, u.firstname 
FROM users u 
WHERE (
    SELECT COUNT(*) 
    FROM users u2 
    WHERE u.id = u2.id 
    AND u2.calendar_week = <?= $date->format('W') - 1 ?>
    AND u2.categorie_bit > 0
) = 0
ORDER BY RAND()
LIMIT 1

UPDATE

To make sure each user wasn't assigned another job until every user had done a job at least once I would record the information differently. I would have two tables, one for users and one for the job they completed and the week they completed it on.

Users
+----+-------+
| id | user  |
+----+-------+
|  1 | Kevin |
+----+-------+

Jobs
+----+--------+-----+------+
| id | userId | job | week |
+----+--------+-----+------+
|  1 |      1 |  01 |   39 |
|  2 |      2 |  10 |   39 |
|  3 |      6 |  01 |   40 |
|  4 |      8 |  10 |   40 |
+----+--------+-----+------+

Then you can use the following query:

SELECT u.id, u.user 
FROM users u
LEFT JOIN jobs j ON u.id = j.userId
WHERE j.id IS NULL
ORDER BY RAND()
LIMIT 1

So now you only get users who haven't completed a job. Once everyone has completed a job and the query returns 0 results you just clear the jobs table and start afresh.

edited Sep 24, 2015 at 11:01

answered Sep 24, 2015 at 10:47

Styphon

10.5k9 gold badges54 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

as21 Over a year ago

Thanks I will try this with PDO, do you have any idea how to extend this for future case? Lets say user 1 did calender week 39, user 2 did 44 and so on. I cannot -1 if I want to extend this for future use I think. I will also edit the question.

Styphon Over a year ago

If you wanted to extend it like that so everyone eventually has a job I'd organise it differently. I'll update my answer in a minute.

as21 Over a year ago

Could you explain your sql query please

Styphon Over a year ago

If a record for a user doesn't exist in the jobs table then all the fields in jobs will be NULL, so all we do is left join jobs and check to see if id is null (i.e. there is no record). If there is a record ID won't be null so it's excluded from the search results.

Collectives™ on Stack Overflow

Php Calender Week Random User Calculation

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related