How to get a list of dates in a sql query using php?

dimensionav
dimensionav used Ask the Experts™
on
I have a mysql table named Sales from where I need to get all the combinations of MONTH and YEAR in order to put them in a list, look at this example:

This can be the table Sales:

ID_SALE, DATE_SALE
1, JANUARY-10-2012
2, MARCH-15-2012
3, MARCH-20-2012
4, APRIL-10-2012

I want to populate a combobox that could bring the list in pair Month-Year based on DATE_SALE field, something like this:

JAN-2012
MAR-2012
APR-2012

As you can see there are only the month-year involved in all registers of Sales table.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2016
Commented:
Please read this article.  I'll think about your question and try to suggest some solutions.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
Most Valuable Expert 2011
Top Expert 2016
Commented:
Here is how I would do it.
http://www.laprbass.com/RAY_temp_dimensionav.php
<?php // RAY_temp_dimensionav.php
error_reporting(E_ALL);
echo "<pre>";

// REQUIRED FOR PHP 5.1+
date_default_timezone_set('America/Chicago');

// TEST DATA, REFOMATTED INTO AN ARRAY OF DATES
$arr = array
( 'JANUARY-10-2012'
, 'MARCH-15-2012'
, 'MARCH-20-2012'
, 'APRIL-10-2012'
)
;

// MAKE AN ARRAY OF DATES IN THE FORMAT WE WANT TO USE FOR THE COMBO BOX
foreach ($arr as $date)
{
    $ts  = strtotime($date);
    $key = date('M-Y',    $ts);
    $val = date('Y-m-01', $ts);
    $out[$key] = $val;
}

// GENERATE THE SELECT OPTIONS
foreach ($out as $key => $val)
{
    $opt = "<option value='$val'>$key</option>" . PHP_EOL;

    // SHOW EACH OPTION AS WE GENERATE IT
    echo htmlentities($opt);
}

Open in new window

The array used for the test data would actually be your query results set from your data base.

Author

Commented:
Sorry for late response

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial