CODEWITHSUNDEEP
phpmysqlsql
Nyotnoti
Nyotnoti

Reputation: 63

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
<?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();
        }
    });

Upvotes: 2

Views: 85

Answers (2)

Fahmi
Fahmi

Reputation: 37473

You can try like below -

    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"

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270301

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.

Upvotes: 0

Related Questions