PHP MYSQL Query Bring Results from Today and This Week

Asked

Viewed 173 times

1

I’m assembling type a CRM, where makes queries in MYSQL with PHP to bring the appointments I have today, this week and all others.

I wish you’d bring something like that:

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

It has to bring everything in the same consultation, or I have to separate in 3 consultations (Today’s consultation, this week’s consultation and all other)

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 select date

  • Dear Leonardo, do not put the code of an answer in the question, we are QUESTIONS and ANSWERS, if you still have any problem with the code of any question comment to who answered, if you edit adding the code in the question will seem that his answer has no sense at all.

1 answer

2


You can add a column to your SQL indicating this from some date checks with IF, follows 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 deal with the programming, the lines that have hoje means that you belong to today’s group. Those who have semana belong to the current week. And finally the ones that have todos do not belong to any of the previous groups.

You can do 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 tasks separated according to the group, now just do one 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>

  • cool, the consultation was right, but how do I separate today from the week and everyone else at the time to bring the result in php? I am using foreach($comrpomissos as $comp){echo '<tr><td>'. $comp->compromise. '</td></tr>}

  • You can separate into arrays before and after making a foreach for each group

  • Or if you just give one GROUP BY datacompromisso it will already bring in the right order.

  • I didn’t understand the part of the array, how would I do it?

  • Enter your code PHP so that I can set an example upon it.

  • change the answer there

  • @Leandromarzullo changed my answer.

  • the foreach in each group would be this? foreach($today as $h){ echo $h->datacompromise;}

  • Exactly, then you make a table for each group.

  • I edited the answer as I left, I must be doing something wrong, the query is appearing blank

  • You don’t want to print the group hoje in a table, the semana in another and the todos in another?

  • yes, vdd, I put line, but still, I shouldn’t show the values?

  • See if the way I put it solves. If not, put the code where you run the select.

  • changed the question

  • In my reply I added the code to print the tables, you put it after the separation of the groups?

  • The foreach($results as $r){ is only to separate, does not print anything on the screen.

  • Let’s go continue this discussion in chat.

Show 12 more comments

Browser other questions tagged php mysql select date

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