I am doing an exercise of a Task allocator for 2 workers. Each worker must be assigned to an area. It works with mysql 5
My Task table has: id (autoincremental), area, task and worker. For example, it should look like this, each worker in an area:
Area | Task | Employee
HR | personal assistance | worker1
HR | carry out settlements | worker1
Accountant | make declarations |Worker2
Management| plan agenda | worker1
Logistics| plan orders | Worker2
Logistics| contract suppliers| Worker2
I have been doing this, I have declared a variable to generate a ranking grouped by area but the result assigns everything to Worker1, please, any suggestions, I can't find a way to assign a Worker by area??
$sql = "SELECT id,area, count(*),@rn :=d( @rn+1 ) AS rank,trabajador FROM dbempresa.tareas, (SELECT @rn:=0) a group BY area";
$result = mysqli_query($con,$sql);
$cantidad = $result -> num_rows;
while ($row = $result -> fetch_array()){
for ($i=1;$i<$cantidad;$i=$i+2){
$consulta="UPDATE dbempresa.tareas SET trabajador=concat('Trabajador','1')where @rn=$i";
$resulta = mysqli_query($con,$consulta);
$consulta="UPDATE dbempresa.tareas SET trabajador=concat('Trabajador','2') where @rn=('$i'+1)";
$resulta = mysqli_query($con,$consulta);}}