Select database data within another while?

Question

Select database data within another while?

Navigation

#1 by (1 votes)

0

I have a calendar made by me, not very good, but done by me, in which you have a while that creates squares, that is, the days, of the current month. So far so good, but now I would like it if one day had an event, it would change the color of that square, 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 . Do you have a solution?

php sql mysqli

asked by anonymous 02.11.2017 / 18:41

1 answer

Increase font size JavaScript / HTML / Jquery Error CS0012 The type 'Client' is defined in an assembly that is not referenced

score 1 · Accepted Answer

By assuming that in the eventos table the data field is of a date type, we can soon get the number of events per day by changing the query to use a GROUP BY for 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 have built a dictionary so that it can then 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 loop / loop that creates the various 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 that I changed the ifs by ternary operators just to get more compact in writing.

Example on Ideone (with forced example 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 that day have on the calendar by changing only <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

That 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>