PHP Get count

[durgaprasadcs]

Hi all,

I have records in my sql database which contains fields like ID, Name, Date
I need Name Count grouped by Month using date field.

Example :
ID | Name | Date (mm/dd/yyyy)
-----------------------
1 John 01/01/2013
2 David 01/20/2013
3 Harry 02/10/2013


So when i get the count based on the above data, if should be like follow

January - 2 
February - 1


That is John and David falling in January Month
Harry is falling in February Month

How can i acheive this. Its like kind of excel pivot table.

[Jacques1]

If the date field has a proper type (DATE, DATETIME or TIMESTAMP), you simply use the date and time functions: http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html

 

If it's some hard-coded VARCHAR (looks like it), you have a problem. Since MySQL doesn't know what to do with your strings, you first have to repair your database by turning all pseudo-dates into actual dates.

SELECT
	DATE_FORMAT(date, '%M') AS month,
	COUNT(*) AS name_count
FROM
	test
GROUP BY
	EXTRACT(MONTH FROM date)
ORDER BY
	EXTRACT(MONTH FROM date) ASC
;

[cyberRobot]

If the date values aren't stored as a date type in the database, you could use PHP to figure out the month. For example:

<?php
echo date('n', strtotime('01/01/2013'));
?>

More information about these functions, including examples, can be found in the PHP manual:

http://www.php.net/

 

Once you know the month for each date, it just comes down to counting the occurrences.