How to count record month group with unix timestamp in php & mysql.

Question

Example my_table

ID | Name | Date
--------------------------
12 | John | 123456789
13 | Mike | 987654321
...
29 | Rick | 123498765
30 | Adam | 987651234

show output result like this

Month | Count
--------------------------
3 | 5 | 
6 | 8 |

How can I do this with PHP?

You need to calculate the start and end of the month in epoch time, then use a BETWEEN type MySQL query. — Brian Gottier
– Brian Gottier, Commented Oct 11, 2017 at 6:28

zarruq · Accepted Answer · 2017-10-11 07:35:42Z

5

You can do this using MySQL Query as below.

SELECT MONTH(FROM_UNIXTIME(`Date`)) `Month`
    ,COUNT(ID)
FROM my_table
GROUP BY `Month`;

edited Oct 11, 2017 at 7:35

zarruq

2,4552 gold badges12 silver badges20 bronze badges

answered Oct 11, 2017 at 6:31

Urvish

1514 bronze badges

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

Comments

Akshay Hegde · Accepted Answer · 2017-10-11 06:53:29Z

2

Since post tagged codeigniter

This is codeigniter way:

$query = $this->db->select("month(from_unixtime(`Date`)) as `month`, count(1) as `count`",FALSE)
                  ->group_by("month");
                  ->get("your_table");

answered Oct 11, 2017 at 6:53

Akshay Hegde

17.1k2 gold badges25 silver badges39 bronze badges

1 Comment

Prakash Tank Over a year ago

In this answer can we add 1 column called "names" with adding comma separate names, for example, month value is 3 then cell show 3 names in comma separares. Can you able to share the lines of code ? so its help to getting name list in single sql query. Thanks.

Collectives™ on Stack Overflow

How to count record month group with unix timestamp in php & mysql.

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related