Query Available Dates By Month and Year

sactown

Hi,

I've got a list of dates in a mysql database in Year, Month, Date format (2003-03-02), and I want to list them in a form using <select>. But I only want to see the month with its respected year only. I don't want to see all the dates for every month, I only want one month's name and its year. So I can run a query for let say for 2003-02 (Feb. 2003), then I would be able to see all entries for Feb 2003.

I know I can do this without PHP, and list the months manually, but I figure their would be an easier way in PHP to do this.

Here is what I am using know - which is quering all dates from the database into the Select Field in to the form.

<?
...	while($row=mysql_fetch_array($sql)) 
{
echo "<OPTION VALUE=\"$row[date]\">$row[date]</option>";
}
...
?>

Hope this make sense. If not, let me know.

Thanks.

nemonoman

if you are using a DATE type column, you can say something like

SELECT CONCAT(YEAR(datecolumn),'-',MONTH(datecolumn))
FROM sometable
WHERE YEAR(datecolumn)='2003'
ORDER BY datecolumn

If you want something more user friendly, try

SELECT CONCAT(MONTHNAME(datecolumn),' ,',YEAR(datecolumn))
FROM sometable
WHERE YEAR(datecolumn)='2003'
ORDER BY datecolumn

If you are trying to collect all the data for a certain month, your statement would be something like

SELECT *
FROM sometable
WHERE YEAR(datecolumn)='2003'
AND MONTH(datecolumn)='2'

sactown

That looks ok. But I want a query to go through all the dates in the database, and then to display only a single entry for a month and a year, for what ever is in the database. Rather than specifying a year.

Right now I am getting a drop down list of something like this:

...
2002-12-06
2002-12-13
2002-12-20
2003-01-03

But rather than seeing all the dates for Dec 2002, I only want to see a single entry - the month and its year:
2002-12 (or as 12-2002).

nemonoman

This code should do that:

SELECT CONCAT(MONTHNAME(datecolumn),' ,',YEAR(datecolumn))
FROM sometable
WHERE YEAR(datecolumn)='2003'
GROUP BY MONTH(datecolumn)
ORDER BY datecolumn

Then push the returned items into a select statement.