Traverse rows of a table within loops

Question

What I intend to do is schedule times and dates on front-end according to the records of a table, but with the code I have I can only mark the first row of the table. How to go through the entire table by marking all the corresponding dates?

I had some success with another while in place of mysqli_fetch_assoc() using mysqli_fetch_array() but it repeats the number of rows in the table, multiplying the hours of the front-end. Follows my code:

<div class="row">
   <?
   for ($i=0; $i < 12; $i++) { 
      $justDay=date('Y-m-d',strtotime("+$i days"));
      $days=formatDt(date('Y-m-d',strtotime("+$i days")));
      $dweek= strftime('%A', strtotime("+$i days, today"));
   ?>
   <div class="col-md-1 bord-head-table text-center">
      <span class="text-primary"><?= $days;?><br><!--linha exbe semana--></span><br>
      <span class="text-white fontweek"><?=utf8_encode($dweek); ?></span>
      <hr class="border-lime">
      <!-- chama tabela horas-->
      <? $sql = "SELECT * from horas";
      $qr=mysqli_query($conexao,$sql);
      $it=0;
      while ($ft=mysqli_fetch_array($qr)) { 
         $it++;
         $sqlAula = "SELECT * from monta_aula";
         $qrAula=mysqli_query($conexao,$sqlAula);
         $ftAula=mysqli_fetch_assoc($qrAula);
         $hrAula=$ftAula['hora_inicio_aula'];

         /* compara hora e data da tabela aula com a hora e data do front end e contorna o horário correspondente ao cadastrado no banco, na tabela aula*/
         if ($hrAula == $ft['hora'] and $justDay == $ftAula['dia_aula']) {
            $hc="border-class";
         } else{
            $hc="text-green-d";
         }
      ?>
      <div class="bord-hours">
         <a href="#" class="<?=$hc;?>">
         <?=$ft['hora'];?>
         </a><br>
      </div>
      <?    }//while ?>
   </div>
   <!--col md 1 - representa cada coluna, com data, dia da semana e horários (de 08:00:00 às 21:00:00)-->
   <? }//for ?>
</div>
<!--row-->

Table to go:

Table Hours:

Front End:

Answer 1

I believe you have to create some relationship between your for() and the consultations that are being made subsequently.

At each loop of for() you search all times on all registered days. You would not have to bring only the records of that specific day that is calculated in $justDay = date('Y-m-d', strtotime('+$i days'))?

Another detail is that when you do $hrAula = $ftAula['hora_inicio_aula'], you are only taking the first record of the query that can return n records.

You can try doing it this way then:

$sqlAula = "SELECT id_monta_aula FROM monta_aula WHERE dia_aula = '" . $justDay . "' AND hora_inicio_aula = '" . $ft['hora'] . "' LIMIT 1";

That way, every time while() is executed, the query will fetch a lesson (note the LIMIT 1) on the day calculated there in $justDay and at each time of the query "SELECT * from horas" for that day.

Therefore, you only need to check if there are any lessons in this query:

if (mysqli_num_rows($sqlAula) == 1)
{
    $hc = 'border-class'; // Tem aula nesse dia e horário!
}
else
{
    $hc = 'text-green-d'; // Não tem aula...
}

I don’t recommend using * in the queries, so I removed, but then you can put the fields you need.

Remembering that in terms of performance, it is also not very recommended that you bring the hours of the database as it is being done. However, I believe it will work normally.

The reason is very simple, the for() repeats itself 12 times, that is, every time it repeats itself will run 1 consultation bringing the schedules and more 14 below queries (one for each table time). This will all be equivalent to 12 * 15 = 180 consultations.

I hope I’ve helped! :)