0

I have the following relation in my schema:

Entries:
entryId(PK) auto_inc
date date

In order to count the total entries in the relation I use a query in my php like this:

$sql = mysql_query("SELECT COUNT(*) as Frequency FROM Entries WHERE date = '$date'");

My question is how can I count the number of entries for the CURRENT month..

user906568
  • 475
  • 2
  • 9
  • 21
  • warning your code is susceptible to sql injection. – Daniel A. White Aug 25 '11 at 23:37
  • Like this: http://xkcd.com/327/ – user194076 Aug 25 '11 at 23:39
  • Right, but that refers to user input...something I'm not allowing...In all input instances I use msql_real_escape_string – user906568 Aug 25 '11 at 23:41
  • 3
    @Daniel It's a little presumptuous to assume that without knowing how the `$date` variable is initialised. – Phil Aug 25 '11 at 23:42
  • @Phil - i always put it in there if it looks like it. – Daniel A. White Aug 25 '11 at 23:45
  • @Daniel It's a fair assumption given the majority of PHP code examples. – Phil Aug 25 '11 at 23:47
  • 1
    I just want to comment on Phil and Noximos answers. While they will get you the result, they will not be able to use an index on the date column, so if performance issues are a concern on this, then I'd suggest the more complicated approach I suggested. Otherwise Phil's would work, and Noximo's would work, but would also find rows for any year, which could be a problem depending on your data. Noximo's could be made to work by adding AND YEAR(date) = YEAR(CURDATE()) – gview Aug 25 '11 at 23:52

3 Answers3

1

You want a between query based on your date column.

WHERE date BETWEEN startdate AND enddate.

Between is equivalent to date >= startdate AND date <= enddate. It would of course be also possible to just use >= AND < explicitly which would simplify it a bit because you don't need to find the last day of the month, but just the first day of the following month using only DATE_ADD(..., INTERVAL 1 MONTH).

However startdate and enddate in this case would be derived from CURDATE().

You can use CURDATE(), MONTH(), DATE_ADD and STR_TO_DATE to derive the dates you need (1st day of current month, last day of current month). This article solves a similar problem and all the techniques needed are shown in examples that you should be able to adapt:

http://www.gizmola.com/blog/archives/107-Calculate-a-persons-age-in-a-MySQL-query.html

The first day of the current month is obvious YEAR-MONTH(CURDATE())-01. The last day you can calculate by using DATE_ADD to add 1 Month to the first day of the current month, then DATE_ADD -1 Days.

update- Ok, I went and formulated the full query. Don't think str_to_date is really needed to get the index efficiency but didn't actually check.

SELECT count(*) 
FROM entries
WHERE `date` BETWEEN 
CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-', '01')
AND
DATE_ADD(DATE_ADD(CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-', '01'), INTERVAL 1 MONTH), INTERVAL -1 DAY);
gview
  • 14,876
  • 3
  • 46
  • 51
  • @Phil, yes definately. And as I said, yours is simple, elegant, and would do the job for many people, who don't want to crank out a monstrosity like mine ;) – gview Aug 26 '11 at 00:40
  • Too bad MySQL doesn't have a date truncate function like Oracle. Would have made your solution simpler – Phil Aug 26 '11 at 00:47
0

Try this

SELECT COUNT(1) AS `Frequency`
FROM `Entries`
WHERE EXTRACT(YEAR_MONTH FROM `date`) = EXTRACT(YEAR_MONTH FROM CURDATE())

See EXTRACT() and CURDATE()

Edit: Changed NOW() to CURDATE() as it is more appropriate here

Phil
  • 157,677
  • 23
  • 242
  • 245
0

Try

 $sql = mysql_query("SELECT COUNT(*) as Frequency FROM Entries WHERE MONTH(date) = MONTH(NOW()) );
Noximo
  • 77
  • 1
  • 8
  • 1
    That will match same months across different years. You're also missing the closing quote on your query string – Phil Aug 25 '11 at 23:49