CODEWITHSUNDEEP
phpmysql
P.Davide
P.Davide

Reputation: 379

mysql "between and" query including the first day and the last day

I have this query that i use to extract data from my database but I discovered that it miss the first day and the last day. In this example, it skip the records that have 'scadenze' set as '01-05-2021' and miss the records that have 'scadenze' set as '31-05-2021'. How can I inlcude also these days?

This is the query that I use:

$anno = date('Y'); // CURRENT YEAR
$mese = (date('m')+1); // THE MONTH THAT I NEED (THE NEXT ONE)

// FIRST DAY OF THE MONTH

$giornoinizio = '01';
$a = '';
$a .= $anno . '-' . $mese .'-' . $giornoinizio;

// LAST DAY OF THE MONTH

$giornofine = cal_days_in_month(CAL_GREGORIAN,$mese,$anno);
$b = '';
$b .= $anno . '-' . $mese .'-' . $giornofine;

$sql = "SELECT * FROM condominio WHERE scadenze BETWEEN '$a' AND '$b' ORDER BY scadenze ASC";

This query "works" but it cut out the first day and the last one.. How can I include them?

Thanks

Upvotes: 0

Views: 458

Answers (2)

RiggsFolly
RiggsFolly

Reputation: 94662

You are making life rather difficult for yourself.

You can simply get the 2 dates using this date code

$a = date('Y-m-d', strtotime('first day of +1 month'));
$b =  date('Y-m-d', strtotime('last day of +1 month'));

$sql = "SELECT * FROM condominio WHERE scadenze BETWEEN '$a' AND '$b' ORDER BY scadenze ASC";

But this data concatentation into a query is a bad habit to get into it can encourage you to SQL Injection Attack. Even if you are escaping inputs, its not safe! You should alway use prepared parameterized statements in either the MYSQLI_ or PDO API's instead of concatenating user provided values into the query.

Not sure which extension you are using, so I can only suggest a PDO solution

$a = date('Y-m-d', strtotime('first day of +1 month'));
$b =  date('Y-m-d', strtotime('last day of +1 month'));

$sql = "SELECT * FROM condominio
        WHERE scadenze BETWEEN :a AND :b 
        ORDER BY scadenze ASC";
$stmt = $con->prepare($sql);
$stmt->execute([':a'=>$a, ':b'=>$b]);

$result = $stmt->fetchAll();

foreach ( $results as $row ){
    // process each row
}

Upvotes: 3

Othmane Nemli
Othmane Nemli

Reputation: 1193

try to use date() function on ur colum

SELECT * FROM condominio WHERE date(scadenze) BETWEEN '$a' AND '$b' ORDER BY scadenze ASC

Upvotes: 1

Related Questions