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

Question

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

I need to show this is a great month, year from the created column.

I was looking for a stream of stacks and was able to repeat months using the 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 prints months as:

 October January 

I need output in the format:

 October 2011 January 2012 

Can someone please let me know what changes I should make to the code to get the desired result.

thanks

Answer 1

Use this:

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

and in your request do not select the month, just:

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

So this will be:

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

Answer 2

For MySQL solution:

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

This displays the result of the MONTHNAME() and YEAR() functions and concatenates them between spaces, for example:

 October 2011 January 2012 

Answer 3

try below for the current year

  echo $date.date("Y"); 

OR, if you need a specific year, you need to take from the database table

thanks