How to print month name from MySQL timestamp count in PHP?

Question

I count article view by timestamp month using json output. I have this code :

$value = array();
$stats  = Access::FETCH("SELECT COUNT(*) AS id FROM news_stats GROUP BY YEAR(date), MONTH(date)");

foreach($stats as $key => $value){

   $rows2[] = $value['id'];

   }
echo json_encode($rows2);

output is : NOTE: this is count for month each month

["1","6"]

I need to print month name for this ouput like this:

["january","June"]

How do can in print month name ? Menaig is: 1 , 6 count from which month?

Answer 1

You can do it in MySQL itself by adjusting your query like so to use MONTHNAME:

SELECT COUNT(*) AS id FROM news_stats GROUP BY YEAR(date), MONTHNAME(date);

The only real value of doing it in PHP via an mktime/datecombo is somehow your script needs the numerical value as well as the full month name itself. But that too could be addressed in MySQL by doing something like a SELECT YEAR(date) as year, MONTH(date) as month, MONTHNAME(date) as month_name… but it all depends on coding needs more than anything.

Also, just realized it’s all coded for GROUP BY in your example. So perhaps this would work better:

SELECT COUNT(*) AS id,
YEAR(date) as `year`,
MONTH(date) as `month`,
MONTHNAME(date) as `month_name`,
FROM `news_stats`
GROUP BY `year`, `month`;

Answer 2

Try This :

$value = array();
$stats = Access::FETCH("SELECT id, YEAR(FROM_UNIXTIME(date)) AS `YEAR`, 
                MONTHNAME(FROM_UNIXTIME(date)) AS `MONTH`, 
                COUNT(*) AS id 
         FROM news_stats GROUP BY `YEAR`, `MONTH` ORDER BY `YEAR` DESC, `MONTH`");


foreach($stats as $key => $value){

   $rows2[] = $value['MONTH'];

   }
   echo json_encode($rows2);

Output is :

["June","August"]

Enjoy!!