Select database data within another while?

Question

Select database data within another while?

Asked 7 years ago

Viewed 44 times

0

I have a calendar made by me, not very good, but made by me, in which has a while creating squares, ie the days, of the current month. So far so good, but now I would like, if a certain day had an event, it would change the color of this comic, ie add a class for example.

Here I have the code

    <table class="calen">
      <?php
      $at=0;
      $days = date("t");
      $atual = date("d");

      while($at < $days){
        $query = mysqli_query($db, "SELECT * FROM eventos WHERE MONTH(data) = '".$mes_at."' AND YEAR(data) = '".$ano_at."'");
        $mos = mysqli_fetch_assoc($query);
        $dia=substr($mos['data'], 8, 2);
        $at++;
        if($dia == $at){
          $clic="with_event";
        }else{
          $clic="";
        }
          if($atual == $at){
            $atual1 = "atual";
          }else{
            $atual1 = "";
          }
        if($at%7 == 1){
          echo "</tr>";
          echo "<tr>";
        }
      ?>
        <td class="calen_td <?=$atual1; ?> <?=$clic; ?>" data-dia="<?=$at; ?>" data-month="<?=$mes_atual; ?>"><?=$at; ?></td>
    <?php } ?>
  </tr>
    </table>

I have 4 events entered, 2 for day 17, 1 for day 15, and one for day 1, but only on day 15 it adds the class with_event. Is there any solution?

A while that makes queries to the database is probably not what you want, because it creates too much weight. Better to do 1 query that brings all the data and processes them all with one while.

– Isac

2017/11/02 at 22:41
@Isac You can give an example?

– I_like_trains

2017/11/03 at 16:50

1 answer

Browser other questions tagged php sql mysqli

You are not signed in. Login or sign up in order to post.

by Isac • **24,736** points · Answer 1 · 2017-11-03T18:20:51+00:00

Imagining that on the table eventos the field data is of a date type, we can then get the amount of events per day by changing the query to use a GROUP BY in the days:

SELECT DAY(data), count(*) as contagem from eventos 
       where MONTH(data) = $mes_at and YEAR($ano_at) = $ano_at group by DAY(data)

With this query we built a dictionary so that it can be used in the construction of the calendar in html:

$query = mysqli_query($db, "SELECT DAY(data) as dia, count(*) as contagem from eventos 
               where MONTH(data) = $mes_at and YEAR($ano_at) = 2017 group by DAY(data)");

$dias = Array();

while ($row = mysqli_fetch_assoc($query)){
    $dias[$row["dia"]] = $row["contagem"]; //acrescentar cada contagem da consulta
}

Next we use this dictionary in the cycle/loop that creates the several days of the table:

<table class="calen">
    <?php

    $days = date("t");
    $atual = date("d");
    $mes_atual = date("m");

    for ($at = 1; $at <= $days; $at++){ //for para ser mais simples
        if($at%7 == 1){
            echo "</tr><tr>";
        }
        ?>
        <td class="calen_td <?=$at==$atual?'atual':''?> <?=isset($dias[$at])?'with_event':''?>" data-dia="<?=$at?>" data-month="<?=$mes_atual?>"><?=$at?></td>
        <?php
    }
    ?>
</tr>
</table>

Notice I switched out the ifs by ternary operators only to become more compact in writing.

Example in Ideone (with forced sample days)

Ideone html result interpreted in browser:

<html>

<head>
  <style>
    .with_event {
      background-color: red;
    }
    
    .atual {
      background-color: green;
    }
    
    .calen td {
      padding: 20px;
    }
  </style>

  <body>
    <table class="calen">
      </tr>
      <tr>
        <td class="calen_td  " data-dia="1" data-month="11">1</td>
        <td class="calen_td  " data-dia="2" data-month="11">2</td>
        <td class="calen_td  " data-dia="3" data-month="11">3</td>
        <td class="calen_td atual " data-dia="4" data-month="11">4</td>
        <td class="calen_td  " data-dia="5" data-month="11">5</td>
        <td class="calen_td  " data-dia="6" data-month="11">6</td>
        <td class="calen_td  " data-dia="7" data-month="11">7</td>
      </tr>
      <tr>
        <td class="calen_td  " data-dia="8" data-month="11">8</td>
        <td class="calen_td  " data-dia="9" data-month="11">9</td>
        <td class="calen_td  " data-dia="10" data-month="11">10</td>
        <td class="calen_td  " data-dia="11" data-month="11">11</td>
        <td class="calen_td  " data-dia="12" data-month="11">12</td>
        <td class="calen_td  with_event" data-dia="13" data-month="11">13</td>
        <td class="calen_td  " data-dia="14" data-month="11">14</td>
      </tr>
      <tr>
        <td class="calen_td  " data-dia="15" data-month="11">15</td>
        <td class="calen_td  " data-dia="16" data-month="11">16</td>
        <td class="calen_td  " data-dia="17" data-month="11">17</td>
        <td class="calen_td  " data-dia="18" data-month="11">18</td>
        <td class="calen_td  with_event" data-dia="19" data-month="11">19</td>
        <td class="calen_td  with_event" data-dia="20" data-month="11">20</td>
        <td class="calen_td  " data-dia="21" data-month="11">21</td>
      </tr>
      <tr>
        <td class="calen_td  " data-dia="22" data-month="11">22</td>
        <td class="calen_td  " data-dia="23" data-month="11">23</td>
        <td class="calen_td  " data-dia="24" data-month="11">24</td>
        <td class="calen_td  with_event" data-dia="25" data-month="11">25</td>
        <td class="calen_td  " data-dia="26" data-month="11">26</td>
        <td class="calen_td  " data-dia="27" data-month="11">27</td>
        <td class="calen_td  " data-dia="28" data-month="11">28</td>
      </tr>
      <tr>
        <td class="calen_td  " data-dia="29" data-month="11">29</td>
        <td class="calen_td  " data-dia="30" data-month="11">30</td>
      </tr>
    </table>
  </body>

</html>

Since you now have the event count per day you can even show how many events have that day on the calendar by changing only the <td>:

<td class="calen_td <?=$at==$atual?'atual':''?> <?=isset($dias[$at])?'with_event':''?>" data-dia="<?=$at?>" data-month="<?=$mes_atual?>"><?=$at?>
    <?php if(isset($dias[$at]) && $dias[$at]>0) { ?> //se tem dias coloca no span
        <span class="dias"><?=$dias[$at]?></span>
    <?php } ?>
</td>

See this example also in Ideone

Which gives you the following html:

<html>

<head>
  <style>
    .with_event {
      background-color: red;
    }
    
    .atual {
      background-color: green;
    }
    
    .dias {
      background-color: gray;
      padding: 5px;
      font-size: 13px;
      font-weight: bold;
      position: absolute;
      top: -5px;
      left: 35px;
    }
    
    .calen td {
      padding: 20px;
      position: relative;
    }
  </style>

  <body>
    <table class="calen">
      </tr>
      <tr>
        <td class="calen_td  " data-dia="1" data-month="11">1 </td>
        <td class="calen_td  " data-dia="2" data-month="11">2 </td>
        <td class="calen_td  " data-dia="3" data-month="11">3 </td>
        <td class="calen_td atual " data-dia="4" data-month="11">4 </td>
        <td class="calen_td  " data-dia="5" data-month="11">5 </td>
        <td class="calen_td  " data-dia="6" data-month="11">6 </td>
        <td class="calen_td  " data-dia="7" data-month="11">7 </td>
      </tr>
      <tr>
        <td class="calen_td  " data-dia="8" data-month="11">8 </td>
        <td class="calen_td  " data-dia="9" data-month="11">9 </td>
        <td class="calen_td  " data-dia="10" data-month="11">10 </td>
        <td class="calen_td  " data-dia="11" data-month="11">11 </td>
        <td class="calen_td  " data-dia="12" data-month="11">12 </td>
        <td class="calen_td  with_event" data-dia="13" data-month="11">13 <span class="dias">2</span>
        </td>
        <td class="calen_td  " data-dia="14" data-month="11">14 </td>
      </tr>
      <tr>
        <td class="calen_td  " data-dia="15" data-month="11">15 </td>
        <td class="calen_td  " data-dia="16" data-month="11">16 </td>
        <td class="calen_td  " data-dia="17" data-month="11">17 </td>
        <td class="calen_td  " data-dia="18" data-month="11">18 </td>
        <td class="calen_td  with_event" data-dia="19" data-month="11">19 <span class="dias">1</span>
        </td>
        <td class="calen_td  with_event" data-dia="20" data-month="11">20 <span class="dias">5</span>
        </td>
        <td class="calen_td  " data-dia="21" data-month="11">21 </td>
      </tr>
      <tr>
        <td class="calen_td  " data-dia="22" data-month="11">22 </td>
        <td class="calen_td  " data-dia="23" data-month="11">23 </td>
        <td class="calen_td  " data-dia="24" data-month="11">24 </td>
        <td class="calen_td  with_event" data-dia="25" data-month="11">25 <span class="dias">1</span>
        </td>
        <td class="calen_td  " data-dia="26" data-month="11">26 </td>
        <td class="calen_td  " data-dia="27" data-month="11">27 </td>
        <td class="calen_td  " data-dia="28" data-month="11">28 </td>
      </tr>
      <tr>
        <td class="calen_td  " data-dia="29" data-month="11">29 </td>
      </tr>
    </table>
  </body>

</html>