MySQL select date to date

About MySQL select date to date I have datatable

TABLE A

ID | DATE
-----------------
1  | 2012-01-01
2  | 2012-01-12
3  | 2012-01-20
4  | 2012-02-03
5  | 2012-02-13
6  | 2012-02-14
7  | 2012-04-05

I want to show like this . And I want use Only MySQL .

 DATE
--------------------
 01,12,20 Jan 2012
 03,13,14 Fab 2012
 05 Apr 2012

OR

 DATE
--------------------
 01-01-2012,12-01-2012,20-01-2012
 03-02-2012,13-02-2012,14-02-2012 
 05-03-2012

Thank you

Answers


For the second form, you could combine GROUP_CONCAT() and GROUP BY:

SELECT   DATE_FORMAT(`DATE`, '%b %Y') AS Month
  ,      GROUP_CONCAT(`DATE`)         AS Dates
FROM     `table`
GROUP BY Month

For the first form, you can use DAY() within GROUP_CONCAT():

SELECT   DATE_FORMAT(`DATE`, '%b %Y') AS Month
  ,      GROUP_CONCAT(DAY(`DATE`))    AS Dates
FROM     `table`
GROUP BY Month

Seems pretty easy - Select your dates from the database in ascending date order then loop through each record (using localtime or one of the other time functions where you can get the month), storing the days in a string until you find a record with a different month / year, then print that month string and start a new one for that month.

Probably also worth using the date names from PHP to avoid date names like 'Fab' :)


SELECT GROUP_CONCAT(DATE SEPARATOR ',') FROM TABLEA

OR

select CONCAT_WS(' ',GROUP_CONCAT(a.day1 SEPARATOR ','),a.month1,a.year1) as `DATE` from (select DAY(`DATE`) as day1,DATE_FORMAT(`DATE`,'%b') as month1, YEAR(`DATE`) as year1 from TABLEA) a group by a.month1, a.year1

Need Your Help

HowTo get a Tag Cloud on gitweb?

git tags gitweb

How can I configure a Tag Cloud view to my gitweb project list?

Extjs Panel: how to turn 'frame' property to 'true' dynamically?

extjs panel

I use this code to make a panel->frame value = true at run time:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.