Select Distinct month and year from mysql timestamp field and echo them in php

Question

My mysql table has a createdOn column with filedtype 'timestamp' in the format of 2011-10-13 14:11:12.

What I need is to show, is distinct month,year from createdOn column.

I have searched on stackover flow and was able to echo back months using following code,

*$sqlCommand = "SELECT DISTINCT MONTH(createdOn) AS 'Month' FROM videoBase ORDER BY createdOn DESC";
$query=mysqli_query($myConnection,$sqlCommand) or die(mysqli_error());
while($row = mysqli_fetch_array($query)) {
    $date = date("F", mktime(0, 0, 0, $row['Month']));
    echo ''.$date.' <br />';
}*

This outputs months as :

October
January

What I need is the output in the format of:

October 2011
January 2012

Can anybody please let me know, what changes I should make in the code in order to get the required output.

Thanks

Hi Guys, Thanks for the replies. just been able to solve the issue. I have used the mysql query as suggested by Marcus and then used EscoMaji $date = date("F, Y", strtotime($row['Month'])); It gives me output in the form of October 2011, February 2012 and so on. — Code Road, Feb 22 '12 at 14:05

score 10 · Answer 1 · answered Feb 22 '12 at 13:48

For a MySQL solution:

SELECT DISTINCT CONCAT(MONTHNAME(createdOn), ' ', YEAR(createdOn)) AS `Month`
FROM videoBase
ORDER BY createdOn DESC

This takes the output of the MONTHNAME() function and YEAR() function and concatenates them with a space between, like this:

October 2011
January 2012

EscoMaji · Accepted Answer · 2012-02-22T15:03:03.947

6

Use this:

$date = date("F Y", strtotime($row['Month']));

and in your query don't select the month, just:

SELECT DISTINCT createdOn AS 'Month' FROM videoBase ...

So it will be:

$comma = '';
while($row = mysqli_fetch_array($query)) {
    $date = $comma . date("F", mktime(0, 0, 0, $row['Month']));
    echo $comma . $date;
    $comma = ', ';
}

edited Feb 22 '12 at 15:03

answered Feb 22 '12 at 13:33

EscoMaji

763
5
14

Hi,I tried this and it has given the following output: October 2011 February 2012 February 2012 February 2012 which is due to the fact that every timestamp is Distnct. While I need the outpu as October 2011, February 2012, March 2012. Just distinct month with year. – Code Road Feb 22 '12 at 13:53

Saiyam Patel · Answer 3 · 2012-02-22T13:45:25.403

-1

try below for current year

 echo $date.date("Y");

OR if you want specific year then you must have to take from database table

Thanks

edited Feb 22 '12 at 13:45

answered Feb 22 '12 at 13:37

Saiyam Patel

1,161
9
18

I have just append the current year at the end of the month name above code is same from the question – Saiyam Patel Feb 22 '12 at 13:46
1

this still won't work, the DATE will get the current year, not the one from the query. – rcs20 Feb 22 '12 at 13:49
see my comment on answer "OR if you want specific year then you must have to take from database table" – Saiyam Patel Feb 22 '12 at 13:51

Select Distinct month and year from mysql timestamp field and echo them in php

3 Answers3

Linked