MYSQL PHP query bring results of Today and This Week

Question

MYSQL PHP query bring results of Today and This Week

Navigation

#1 by (2 votes)

0

I'm setting up a CRM type, where it does the queries in MYSQL with PHP to bring the appointments I have today, this week and all others.

I'd like you to bring something like this:

Hoje:
compromisso A
compromisso B

Essa semana:
compromisso C

Todos
compromisso D
compromisso E

The table in MYSQL looks like this:

compromissos
id|datacompromisso    |compromisso
1 |2018-07-30 10:00:00|compromisso A
2 |2018-07-30 10:00:00|compromisso B
3 |2018-07-31 10:00:00|compromisso C
4 |2018-08-10 10:00:00|compromisso D
5 |2018-09-20 10:00:00|compromisso E

You can bring everything in the same query, or I have to separate it into 3 queries (Today's query, this week's query, and everything else)

The results in PHP look like this:

foreach($results as $r){
  echo '<tr>';
  echo '<td>'.$r->datacompromisso.'</td>';
  echo '<td>'.$r->compromisso.'</td>';
  echo '<td>'.$r->grupo.'</td>';
  echo '</tr>';
}

php mysql data select

asked by anonymous 30.07.2018 / 19:42

1 answer

Can not import the Observable package from the timer feature? Prevent duplicate registration with C #

score 2 · Accepted Answer

You can add a column in your SQL that indicates this from some date checks with IF , here's an example:

SELECT IF(
    DATE(datacompromisso) = DATE(NOW()),
    'hoje',
    IF(
        YEAR(datacompromisso) = YEAR(NOW()) AND WEEK(datacompromisso) = WEEK(NOW()),
        'semana',
        'todos'
    )
) AS grupo

After that you're done with programming, lines with hoje means that you belong to today's group. Those with semana belong to the current week. And finally those with todos do not belong to any of the previous groups.

You can make this separation in array , for example:

$hoje = array();
$semana = array();
$todos = array();

foreach($results as $r){
  if ($r->grupo == "hoje")
    array_push($hoje, $r);
  else if ($r->grupo == "semana")
    array_push($semana, $r);
  else if ($r->grupo == "todos")
    array_push($todos, $r);
}

At this point you will already have all the tasks separated according to the group, now just make a foreach in each group and print on the screen for the user:

<h3>Compromissos de Hoje</h3>
<table>
  <thead>
    <tr>
      <td>Data</td>
      <td>Compromisso</td>
    </tr>
  </thead>
  <tbody>
    foreach($hoje as $h){
      echo '<tr>';
      echo '  <td>'.$h->datacompromisso.'</td>';
      echo '  <td>'.$h->compromisso.'</td>';
      echo '</tr>';
    }
  </tbody>
</table>

<h3>Compromissos desta Semana</h3>
<table>
  <thead>
    <tr>
      <td>Data</td>
      <td>Compromisso</td>
    </tr>
  </thead>
  <tbody>
    foreach($semana as $s){
      echo '<tr>';
      echo '  <td>'.$s->datacompromisso.'</td>';
      echo '  <td>'.$s->compromisso.'</td>';
      echo '</tr>';
    }
  </tbody>
</table>

<h3>Outros Compromissos</h3>
<table>
  <thead>
    <tr>
      <td>Data</td>
      <td>Compromisso</td>
    </tr>
  </thead>
  <tbody>
    foreach($todos as $t){
      echo '<tr>';
      echo '  <td>'.$t->datacompromisso.'</td>';
      echo '  <td>'.$t->compromisso.'</td>';
      echo '</tr>';
    }
  </tbody>
</table>