Formulation of logic for problem solving

Question

I have two tables, table A with id and tasks (description), and another table B with a task fk, quantity, fk run. In addition to other columns that are not relevant.

In my table in html (table), I show all the tasks already in thead, and in the results I get the respective amounts of each task.

The problem is that a store can register 5 tasks, and register activities for its employees, but after a while, it can register one more task, only qd this occurs, my view of the table in html breaks. Because in my table B there is no data of this new task.

Would anyone have any suggestions how to solve this problem?

Part of the search query of the amount of tasks:

//tabela A, checo todas as tarefas cadastradas
  $consulta2 = $pdo->prepare("SELECT id_tarefa FROM tarefa WHERE id_loja=:id");
   $consulta2->bindParam(':id', $id_loja, PDO::PARAM_INT);
                    
           if($consulta2->execute()){

               if($consulta2->rowCount() > 0){

                 while($dados_cons2 = $consulta2->fetch(PDO::FETCH_OBJ)){
                            

                            $id_tarefa2=$dados_cons2->id_tarefa;
                       

  //essa seria minha tabela B, checo todas as atividades, que possuem as quantidades da tarefas
  $dados3 = $pdo->prepare("SELECT * FROM atividades_diarias as ativ where ativ.id_ativ=:id_ativ and ativ.id_loja=:id_loja and ativ.tipo_atividade=:tipo and ativ.id_tarefa=:id_taf");

                    $dados3->bindParam(':id_ativ',$id_ativ, PDO::PARAM_INT);
                    $dados3->bindParam(':id_loja',$id_loja, PDO::PARAM_INT);
                $dados3->bindParam(':tipo',$tipo_atividade, PDO::PARAM_STR);
                 $dados3->bindParam(':id_taf',$id_tarefa2, PDO::PARAM_INT);

                    $dados3->execute();

                         while($table3 = $dados3->fetch(PDO::FETCH_OBJ)){

                                       
                 $qtd_tarefa = $table3->qtd_tarefa;

                    //só que as tarefas novas n tem dados antigos na minha tabela B, ele é inexistente, aí quebra a tabela
                     if($qtd_tarefa!=null){ //usei null mas n funcionou
                                    echo'<td>'.$qtd_tarefa.'</td>';
                              }else{
                                     $vazio="";
                                             echo'<td>'.$vazio.'</td>';
                                                }

                                                   }    

My table in Html (example)

Date.... Job name...task1.... task2... NEWJOB..... time of entry
1/6/18............ Isadora.............................. 3....................... 08:00

Result I need:
Date.... Job name...task1.... task2... NEWJOB..... time of entry 1/6/18............ Isadora........................... 2................................................ 08:00

Note that the new registered task takes the value of the next column, qd should empty, since there is no historical data about it.

Does anyone know how to check if my task exists in table B, and if n exists put null in the table view in html? I have tried several alternatives including, Inner Join.

Thank you!

Answer 1

You said a store "can" register 5 tasks and then one more. Then we have a maximum of 6 tasks. This table of yours in html should contain 6 columns of tasks, task1 to task5 and the new task. Problem is not in sql, because it only recovers from the database that actually exists registered, if there is less we should create the empty cells as you want, then In your loop:

while($table3 = $dados3->fetch(PDO::FETCH_OBJ)){


                 $qtd_tarefa = $table3->qtd_tarefa;

                    //só que as tarefas novas n tem dados antigos na minha tabela B, ele é inexistente, aí quebra a tabela
                     if($qtd_tarefa!=null){ //usei null mas n funcionou
                                    echo'<td>'.$qtd_tarefa.'</td>';
                              }else{
                                     $vazio="";
                                             echo'<td>'.$vazio.'</td>';
                                                }

                                                   }    

Add a joint task force found. and at the end of the loop recreate the cells of the empty table, And even if you have registered less than 5 tasks the empty columns will be created and your table in html will be correct. As an example:

$contadorTarefas = 0;
while($table3 = $dados3->fetch(PDO::FETCH_OBJ)){
  $contadorTarefas++;
  $qtd_tarefa = $table3->qtd_tarefa;
  echo'<td>'.$qtd_tarefa.'</td>';
}
//Se não existe tarefa para preencher todas as colunas de tarefas, cria as que faltam vazias
while ($contadorTarefas < 6) {
  $contadorTarefas++;
  echo'<td></td>';
}