Query database, explode date to get year, and populate dropdown with unique year

I'm trying to populate a form SELECT with OPTIONS from a table in my database. It should display each year once.

eg. If the date fields in the database are 02-09-2010, 10-14-2010, 08-09-2011 :

The dropdown should show:

2010

2011

Instead, it shows:

2010

2010

2011

My code:

$result = mysql_query("SELECT date FROM user_history");

        $yearoptions = "";

        while($row = mysql_fetch_array($result)) {
            $date = $row['date'];
            $yeararray = explode("-", $date);
            $year = array_unique($yeararray);
            $开发者_JS百科yearoptions .= '<option value="' . $year[2] . '">'
           . $year[2] . '</option>';

Can anyone see what I'm doing wrong and offer a suggestion?

$result = mysql_query("SELECT distinct year(date) as years FROM user_history ORDER BY 1 ASC");

<?php while($row = mysql_fetch_array($result)) { ?>
  <option value="<?php echo $row['years'];"><?php echo $row['years']; ?></option>
<?php endwhile; ?>

SELECT DISTINCT YEAR(date) FROM user_history, maybe with ORDER BY date, would make the PHP part much simpler...

Try: SELECT DISTINCT YEAR(date) FROM user_history

First thing, may I suggest that you use date_parse on the $row['date'], something like this.

$date = date_parse($row['date']);
$year = $date['year'];

The reason years are showing up twice is that you have no check for redundancy. I would recommend adding each year to an array and using in_array($year, $array) to check if you already have it added.

Try:

SELECT DISTINCT YEAR(date) as date FROM user_history ORDER BY date asc

You dont need the code that you had to extract the year component this way...

Your full code would be:

$result = mysql_query("SELECT DISTINCT YEAR(date) as date FROM user_history ORDER BY date asc");

        $yearoptions = "";

        while($row = mysql_fetch_array($result)) {
            $date = $row['date'];
            $yearoptions .= '<option value="' . $date . '">'
           . $date . '</option>';

Edite You may also find the following useful when working with dates / times in the future http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

You aren't checking for duplicates in the year before outputting it. That's your base problem.

You should store the possible years as an array first, then populate the options based on that array. That way, you won't have to worry about duplicating. If you have 11.26.2009, 09.01.2010, 12.15.2010, 06.05.2011, iterate through all the results for an array similar to $yearOptions = array('2009', '2010', '2011');

The other benefit here is if there's an error with the way the dates are displaying, you can simply var_dump($yearOptions); to see what's showing up in there.

Finally, I'd suggest using strtotime() instead of explode(), simply because it can accept a potentially non-standard date format and still return a proper UNIX timestamp. Then you can manipulate it using date(). Extra mini-bonus: if you ever want to switch to '09 rather than 2009, it's a simple switch.

$result = mysql_query("SELECT date FROM user_history");
$yearOptions = array();
while($row = mysql_fetch_array($result)) {
     $date = $row['date'];
     // convert the date to a UNIX timestamp
     $date = strtotime($date);
     // retrieve just the Y (xxxx) from the timestamp
     $year = date('Y', $date)
     // if the year doesn't already exist in the array, add it.
     if(!in_array($year, $yearOptions)) { $yearOptions[] = $year; }
}

foreach($yearsOptions as $year) {
     echo('<option value="' . $year . '">' . $year . '</option>');
}

Hope it works.

As mentioned above, the easiest way would be

SELECT DISTINCT YEAR(date) FROM user_history ORDER BY date

but if the date is not saved in database a regular DATE db type, you cannot apply the year function and then you need to process manually.

$result = mysql_query("SELECT date FROM user_history");

$yearoptions = "";
$yearArray = array();
while($row = mysql_fetch_array($result)) {
    $date = $row['date'];
    $yeararray = explode("-", $date);
    array_push($yearArray, $year[2]);
}
$yearArray = array_unique($yearArray); // Keep only distinct elements
$yearArray = array_sort($yearArray); // To have years in asc order


for(int i=0; i<sizeof($yearArray); i++) {
    $yearoptions .= '<option value="' . $yearArray[i] . '">'. $yearArray[i] . '</option>';
}