Php – how to fetch the data according to particular month & year in thesql php

countdatetimefetchMySQLPHP

Ok, i have a database in which i have all my users registered with their datetime which stores in my MYSQL datetime field as 2012-03-31 12:13:42 now i want to fetch COUNT of users according to year and month.

As i want to present a chart data for that purpose, i want total customers added in January, in feb, march and so on…. in particular year.

i.e it has to be year specific that in 2010 Jan lets say 52 Customers in 2010 Feb lets say 72 Customers.

Note: The query i want to execute is using PHP and Mysql.

Best Answer

You can use the MySQL date functions

SELECT YEAR(date_field) as stat_year,
       MONTH(date_field) as stat_month,
       COUNT(*) as stat_count
FROM `table`
GROUP BY stat_year,stat_month

If you only want the results for a specific year/month, you can add conditions to the WHERE clause (and remove the GROUP BY) like this

SELECT COUNT(*) as stat_count
FROM `table`
WHERE YEAR(date_field) = 2012
  AND MONTH(date_field) = 3

Related Solutions

Php – How to use PHP to get the current year

You can use either date or strftime. In this case I'd say it doesn't matter as a year is a year, no matter what (unless there's a locale that formats the year differently?)

For example:

<?php echo date("Y"); ?>

On a side note, when formatting dates in PHP it matters when you want to format your date in a different locale than your default. If so, you have to use setlocale and strftime. According to the php manual on date:

To format dates in other languages, you should use the setlocale() and strftime() functions instead of date().

From this point of view, I think it would be best to use strftime as much as possible, if you even have a remote possibility of having to localize your application. If that's not an issue, pick the one you like best.

Mysql – Should I use the datetime or timestamp data type in MySQL

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Best Answer

Related Solutions

Php – How to use PHP to get the current year

Mysql – Should I use the datetime or timestamp data type in MySQL

Related Topic