get date range from date of birth

I am creating a peers page for my website and I am trying to get a query that can fetch users from the users table which is structured as id, name, gender, dob. The query should fetch the users relative to the logged user’s date of birth, i.e users who’s dob is either 2 years less or 3 years more than the logged user’s dob. The dob in the table is structured like 1993-03-23. I need help with the query so that it meets my requirements. This is a basic sample query.

$users = $db->query(
    'SELECT users.name FROM users WHERE users.id=:id',
    array(':id'=>$userid)
);

Answer

If You know logged user dob can use next approach:

<?php
$logged_user_dob = '1993-01-01';
$query = "SELECT * 
    FROM tbl 
    WHERE dob BETWEEN DATE_SUB(:logged_user_dob, INTERVAL 2 YEAR) AND DATE_ADD(:logged_user_dob, INTERVAL 3 YEAR)";
$stmt = $pdo->prepare($query);
$stmt->execute([':logged_user_dob'=>'1993-01-01']);

$res = $stmt->fetchAll(PDO::FETCH_ASSOC);

var_export($res);

Share PHP code online

In case you have only logged user id – use next query:

<?php
$query = "SELECT tbl.* 
    FROM tbl 
    JOIN tbl AS tmp ON 
        tbl.dob BETWEEN DATE_SUB(tmp.dob, INTERVAL 2 YEAR) AND DATE_ADD(tmp.dob, INTERVAL 3 YEAR)
        AND tmp.id = :id
    WHERE tbl.id <> :id";

$stmt = $pdo->prepare($query);
$stmt->execute([':id'=>2]);

$res = $stmt->fetchAll(PDO::FETCH_ASSOC);

var_export($res);

Run & test PHP code online

Advertisement

Answer