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?

php.net//manual/en/function.date.php look at option "F"

VikingBlooded
– VikingBlooded

2014-06-20 15:54:24 +00:00
Commented Jun 20, 2014 at 15:54 — VikingBlooded
– VikingBlooded, Commented Jun 20, 2014 at 15:54

Giacomo1968 · Accepted Answer · 2014-06-20 16:32:32Z

4

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`;

edited Jun 20, 2014 at 16:32

answered Jun 20, 2014 at 15:59

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Brant Over a year ago

This still won't give him the monthname in his result set.

user27133 Over a year ago

@JakeGould: jake i have a question: can list all month of the year and count by month, for each month print count if any month not have count show 0 like this : ["12","0","0","120","70","30","20","11","0","0","10","89"] how do create this?

ಠ_ಠ · Accepted Answer · 2014-06-20 16:19:29Z

3

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!!

edited Jun 20, 2014 at 16:19

answered Jun 20, 2014 at 16:08

ಠ_ಠ

1,2352 gold badges17 silver badges29 bronze badges

Comments

TN888 · Accepted Answer · 2014-06-20 15:52:33Z

1

You can use code similar to this in the foreach loop:

<?php
$monthNum = $value['id'];
$monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
?>

Took from: http://gilbert.pellegrom.me/php-month-number-to-month-name-the-easy-way/

answered Jun 20, 2014 at 15:52

TN888

7,7499 gold badges52 silver badges85 bronze badges

1 Comment

Mike Brant Over a year ago

The fundamental problem is with the query not returning any data about the month at all. No amount of PHP date manipulation will solve that.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related