mysql year java_How to deselect current year in MYSQL

可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:

问题:

So I have this problem of selecting the years that are inside my database. I have existing years from 2015 to 2019. I have to remove the current year.. I'm using this method YEAR(CURDATE()) but I can't seem to use of properly.

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

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 .= "" .$row['year']. "";

$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();

}

});

回答1:

You can try like below -

SELECT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_flood_info`

where year(`date_happened`)

UNION

SELECT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_fire_info` where year(`date_happened`)

UNION

SELECT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_accident_info` where year(`date_happened`)

UNION

SELECT DATE_FORMAT(`date_happened`,'%Y') AS year FROM `tbl_crime_info` where year(`date_happened`)

ORDER BY year DESC"

回答2:

I would use the year() function instead of converting to a string:

SELECT year

FROM ((SELECT year(date_happened) as year

FROM `tbl_flood_info`

) UNION

(SELECT year(date_happened) as year

FROM `tbl_fire_info`

) UNION

(SELECT year(date_happened) as year

FROM `tbl_accident_info`

) UNION

(SELECT year(date_happened) as year

FROM `tbl_crime_info`

)

) t

WHERE year < YEAR(CURDATE());

UNION removes duplicates so all the SELECT DISTINCTs are unnecessary.