php - MYSQL如何取消选择当前年份

Question

所以我遇到了选择数据库中年份的问题。我有从 2015 年到 2019 年的现有年份。我必须删除当前年份。我正在使用这种方法 YEAR(CURDATE()) 但我似乎无法正确使用。

//This is the PHP code that I use to get the years existing and throw it in the option input
<?php 

session_start();
require_once 'connection.php';

$output = "";

$query = "(SELECT DISTINCT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_flood_info`)
          UNION
          (SELECT DISTINCT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_fire_info`)
          UNION
          (SELECT DISTINCT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_accident_info`)
          UNION
          (SELECT DISTINCT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_crime_info`)
          ORDER BY year DESC";

$result = mysqli_query($connection, $query);

$numrows = mysqli_num_rows($result);
$x = 0;
if ($numrows > 0) { 
    while($row = mysqli_fetch_assoc($result)){  
        $output .= "<option>" .$row['year']. "</option>";

        $x++;
    }
}

echo $output;

mysqli_close($connection);
?>

//The ajax code to get the values from the PHP file
    $.ajax({
        type: "post",
        url: "../get_year.php",
        success: function(data) {
            $("#selectYear").append(data.trim());
            getFilter();
        }
    });

Answer 1

你可以像下面这样尝试-

    SELECT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_flood_info`
    where year(`date_happened`)<year(now())
    UNION
    SELECT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_fire_info` where year(`date_happened`)<year(now())
    UNION
    SELECT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_accident_info` where year(`date_happened`)<year(now())
    UNION
    SELECT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_crime_info` where year(`date_happened`)<year(now())
    ORDER BY year DESC"