-1

in my table i have columns : year,month,day in users table I want to calculate and select users whose age is between 18 AND 27 I tried using the DATEDIFF() AND CONCAT() sql function to concatenate the birthday columns(year,month,day) but it does me no good. I checked DATEDIFF() from W3schools.org which states that DATEDIFF() takes 3 arguments while mariaDB says 2 arguments.

but when I tried w3schools their kind of result is what I want to achieve.

please how do I achieve this? or another way to do this in SQL

$onlineAt       = strtolower($_POST['online_at']);
$ageMin         = $_POST['age_min'];
$ageMax         = $_POST['age_max'];
$sex            = $_POST['sex'];
$countryCode    = $_POST['country'];
$time           = time(); $CUid = getCuID();
$onlineAt       = ( $onlineAt == 'now' ? 5: ($onlineAt  == 'today' ? 1440:1441)); //minutes
$date           = date('Y-m-d');
//sql to fetch people
$sql = "SELECT *
                FROM (
                    SELECT m.last_activity_time lat ,
                    m.user_id,
                    m.firstname,
                    m.lastname,
                    m.username,
                    m.course,
                    m.about,
                    m.user_group FROM 
                    $main_table m 
                    WHERE ((($time - m.last_activity_time) /60) < {$onlineAt})
                    AND m.gender ='{$sex}' AND ( 
                        FLOOR(DATEDIFF(CONCAT(m.year,'-',m.month,'-',m.day),'{$date}'))  BETWEEN {$ageMin} AND {$ageMax}
                    )

                ) AS sub WHERE user_id !='{$CUid}'  ORDER BY RAND() LIMIT 20";

$query = queryDB($sql);
//die(mysqli_error($conn));
if (mysqli_num_rows($query)) {
    var_dump(mysqli_num_rows($query));
    while ($row = mysqli_fetch_array($query)) {
        //code
    }
}else{
    echo "no result";
}
Jeffrey D.
  • 414
  • 1
  • 7
james Oduro
  • 673
  • 1
  • 6
  • 22

2 Answers2

1

Just use TIMESTAMPDIFF() function instead:

AND m.gender ='{$sex}'
AND ( 
    TIMESTAMPDIFF(YEAR, CONCAT(m.year,'-',m.month,'-',m.day),'{$date}')  BETWEEN {$ageMin} AND {$ageMax}
)
Alex
  • 16,739
  • 1
  • 28
  • 51
-1

you can calculate the age by subtract two date columns using timestampdiff() function in my SQL to

SELECT timestampdiff(Year,'2011-03-07' , '2021-06-24') as age;

Usama Ramzan
  • 1