Check records between today and 10 days in the future to send by email

Asked

Viewed 247 times

3

I’m using Phpmailer to send an email with data when it’s 10 days away. When I send the email, all the records are showing up when the idea is to show up the logs between today and 10 days in the future.

Consultation

$sql = "
SELECT tb_detalhe_trabalhador.id, Nome, AlvaraValidade, AcidenteValidade, 
SeguroValidade, SocialValidade, RemuneracaoValidade, InstaladorValidade, 
MontadorValidade, MedicaValidade, ProjectistaValidade, GasValidade, RedesValidade, 
SoldadorValidade, MecanicoValidade, MaquinaValidade1, MaquinaValidade2,
MaquinaValidade3, MaquinaTopoValidade
FROM tb_detalhe_trabalhador 
inner join tb_trabalhador on tb_detalhe_trabalhador.id = tb_trabalhador.id
inner join tb_equipamentos on tb_detalhe_trabalhador.id = tb_equipamentos.id
WHERE AlvaraValidade or AcidenteValidade or SeguroValidade
or FinancasValidade or SocialValidade or GasAnexo or ClasSoldadorValidade
or MaquinaValidade1 or MaquinaValidade2 or MaquinaValidade3 or
MaquinaTopoValidade < (now()+ interval 10 day)";

PHPMAILER message body:

while ($row = mysql_fetch_array($validade)) {

  $id = $row[0];
  (...)

  <body><p><strong>Alvara:</strong></strong>$AlvaraValidade</body>
  <body><p><strong>Seguro de Acidente de trabalho:</strong>$AcidenteValidade</body>
  <body><p><strong>Seguro de responsabilidade Civil:</strong>$SeguroValidade</body>
  <body><p><strong>Declaracao de nao divida as financas:</strong>$FinancasValidade</body>
  <body><p><strong>Declaracao de  nao divida S. Social:</strong>$SocialValidade</body>
  <body><p><strong>Declaracao de remuneracao:</strong>$RemuneracaoValidade</body>
  <body><p><strong>Credencial de instalador:</strong>$InstaladorValidade</body>;

Note: Currently I have an error when sending email:

Notice: Undefined offset

This comes to me for the last two dates.

  • 1

    Does your database query return the data as expected? If you cannot specify the format of the date field and the query you are performing?

  • Show me the data and send Email. But not with less than or 10 days to finish the date. Date fields appear Year-Month-day I already change the question and put the select

1 answer

4


Collect only records between the current date and the next 10 days

When using the Mysql NOW() (English), you are working with dates formatted as follows: 2014-03-03 11:47:30:

Returns the Current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, Depending on whether the Function is used in a string or Numeric context. The value is Expressed in the Current time zone.

That translated:

Returns the current date and time as a value in the format 'YYYY-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS.uuuuuu, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

What you need is to make use of Mysql CURDATE() (English) allowing you to work with dates in the format 2014-03-03:

Returns the Current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, Depending on whether the Function is used in a string or Numeric context.

That translated:

Returns the current date as a value in 'YYYY-MM-DD' format or in YYYYMMDD format, depending on whether the function is used in a string or numeric context.

Since you indicated that you have dates in the "Year-Month-Day" format, I assume your field in the database is of the type date.

Therefore, I would suggest amending your consultation to collect the dates as follows:

"...WHERE (...) BETWEEN CURDATE() AND CURDATE() + INTERVAL 10 DAY";
          └─┬─┘         └───┬───┘     └────────────┬────────────┘
            ↓               ↓                      ↓
         campos          entre a            e a data actual
                       data actual            mais 10 dias

Your appointment would look like this:

$sql = "
SELECT
    tb_detalhe_trabalhador.id,
    Nome,
    AlvaraValidade,
    AcidenteValidade,
    SeguroValidade,
    SocialValidade,
    RemuneracaoValidade,
    InstaladorValidade,
    MontadorValidade,
    MedicaValidade,
    ProjectistaValidade,
    GasValidade,
    RedesValidade,
    SoldadorValidade,
    MecanicoValidade,
    MaquinaValidade1,
    MaquinaValidade2,
    MaquinaValidade3,
    MaquinaTopoValidade
FROM tb_detalhe_trabalhador 
INNER JOIN tb_trabalhador on tb_detalhe_trabalhador.id = tb_trabalhador.id
INNER JOIN tb_equipamentos on tb_detalhe_trabalhador.id = tb_equipamentos.id
WHERE (AlvaraValidade or AcidenteValidade or SeguroValidade or FinancasValidade or SocialValidade or RemuneracaoValidade or InstaladorValidade or MontadorValidade or MedicaValidade or ProjectistaValidade or GasValidade or RedesValidade or SoldadorValidade or MecanicoValidade or ClasSoldadorValidade or MaquinaValidade1 or MaquinaValidade2 or MaquinaValidade3 or MaquinaTopoValidade) BETWEEN CURDATE() AND CURDATE() + INTERVAL 10 DAY";

Note:
You’re making match date in a huge number of fields, for reasons of performace should as far as possible reduce the number of control fields.

When using (AlvaraValidade OR ... OR MaquinaTopoValidade) are you saying that just one of those camps is with a date between today and today+10days, any of the fields and not all.


Sending the email

Notice: Undefined offset

This error indicates that you are trying to use an array log entry that does not exist.

You must confirm that the fields you are trying to use are present in the selection that comes from the database. For this purpose you can make a var_dump() or print_r() of a row of the database and check what is in it:

while ($row = mysql_fetch_array($validade)) {

  var_dump($row); // vai-te dar no ecrã um registo da base de dados
  print_r($row);  // alternativa ao var_dump()
  die();          // mata o script para não existir mais execução
                 
  // ...
}

Upon the outcome of var_dump or of print_r you should adjust your query to collect everything you need.


Fields less than 10 days old

To find out if a date that was collected has less than 10 days, you will have to compare the same.

The simplest way is to convert to Unix timestamp the date contained in the field and the current date+10days using the function strtotime() while doing the checking:

//                         teu campo menor 
//                       que daqui a 10 dias
//                                ↑
//                                │
//        converter a data        │   converter a data de
//       do teu campo da BD       │    hoje mais 10 dias
//   ┌────────────┴────────────┐ ┌┴┐ ┌─────────┴─────────┐
if ( strtotime($row["teuCampo"])  <  strtotime("+10 days") ) {
  // fazer algo porque tem menos de 10 dias
}

From the example above, you can perform various types of comparisons to determine if your date is as you intended.

As long as you have the code running inside the if(xx){ /* código aqui*/ } you’re already creating the limitation you want.

An example of testing:

<?php
$matriz = array(
    0 => array(
        "nome" => "primeiro",
        "data" => "2014-03-03"
    ),
    1 => array(
        "nome" => "segundo",
        "data" => "2014-03-04"
    ),
    2 => array(
        "nome" => "terceiro",
        "data" => "2014-03-10"
    ),
    3 => array(
        "nome" => "quarto",
        "data" => "2014-03-15"
    )
);

foreach ($matriz as $row) {
 if (strtotime($row["data"]) < strtotime("+10 days")) {
   echo '<p>O campo: '.$row["nome"].' com a data '.$row["data"].' tem menos de 10 dias.</p>';
 }
}
?>

Test result:

The field: first with the date 2014-03-03 has less than 10 days.
The field: according to the date 2014-03-04 has less than 10 days.
The field: third with the date 2014-03-10 has less than 10 days.

They are all but the last whose date is more than 10 days from now.

  • Yes, just one of them to send the email. One last question. When sending the Email he shows me all the fields and I have to see which one is missing less than 10 days. There is the possibility to only show what is missing 10 days?

  • @user3253195 Of the various fields with dates that were selected, you want to find out which fields contain a date less than 10 days, that’s it?

  • @user3253195 See the last part of the answer I added now, shows a way to compare if the date is less than 10 days.

  • Excellent explanation. Thank you very much. I only had a doubt. I have to put this code after While and before the body of the message. Correct?

  • @user3253195 If I understand the problem correctly, yes, after the while(...){ You do the checking and you’ll act on the outcome. But for your scenario, it needs some changes, everything depends on the ultimate goal of control and the way you want the email to be built and the contents present in it. In order not to lengthen this topic too much, if you have more difficulties in that part, ask a new question with that particular problem.

Browser other questions tagged

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