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.
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?
– Zuul
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
– ChrisAdler