Collect only records between the current date and the next 10 days
When you use MySQL NOW () (English) , you are working with formatted dates 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.
What translated:
Returns the current date and time as a value in the format '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 to do is make use of the MySQL CURDATE () which allows you to work with dates in 2014-03-03
format:
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.
What translated:
Returns the current date as a value in the format 'AAAA-MM-DD' or in the format YYYYMMDD, depending on whether the function is used in a string or numeric context.
As you indicated that you have the dates in the "Year-Month-Day" format, I assume that your field in the database is of type date
.
So I suggested changing your query 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 query 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 are making match of the date in a huge number of fields, for reasons of performace you should reduce the number of control fields.
When you use (AlvaraValidade OR ... OR MaquinaTopoValidade)
you are saying that just one of these fields is with a date between today and today + 10 days, either field and not all.
Email sending
Notice: Undefined offset
This error indicates that you are trying to use a registry array entry that does not exist.
You should confirm that the fields you are trying to use are in the selection that comes from the database. For this purpose you can make a var_dump()
or print_r()
of a line 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
// ...
}
With the result of var_dump
or 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 is less than 10 days, you will have to compare it.
The simplest way is to convert to Unix timestamp the date contained in the field and the current date + 10 days using the function strtotime () while doing the verification:
// 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 make several types of comparisons to see if your date is what you want.
As long as you have the code to run within if(xx){ /* código aqui*/ }
you are already creating the limitation you want.
A test example:
<?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 date 2014-03-03 is less than 10 days.
The field: second with the date 2014-03-04 has less than 10 days.
The field: third with date 2014-03-10 is less than 10 days old.
They are all but the last whose date is more than 10 days.