获取特定日期范围内的文章数量

I would like to have my articles counted per day in certain date period, so I could get array of 40 counts of 40 days.

CREATE TABLE IF NOT EXISTS `articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(255) NOT NULL,
  `author_id` int(11) NOT NULL,
  `date_added` datetime NOT NULL,
  `body` text NOT NULL,
  `description` varchar(500) NOT NULL,
  `location` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

I am not an expert in MySQL, so farthest where I gone is this:

$sql  = 'SELECT date_added FROM articles ';
    $sql .= 'WHERE date_added > ' . '"' . $mysql_datetime_start . '" AND ';
    $sql .= 'date_added < ' . '"' . $mysql_datetime_end . '" ';
    $sql .= 'GROUP BY date_added ';

And than I wanted to go into loops, but there must be some better solution with single query.

This is for a calendar on my site, so one query could save a lot of processor time.

Hope someone will help. Thanks.

You can use MySQL's DATE function to separate out just the date portion of the DATETIME column, then group by it.

SELECT DATE(date_added), COUNT(*) FROM articles
WHERE date_added BETWEEN $mysql_datetime_start AND  $mysql_datetime_end 
GROUP BY DATE(date_added);

I think this is what you are looking for:

$sql = 
"SELECT DATE( date_added ), count(*) FROM articles 
  WHERE date_added > '" . $mysql_datetime_start . "'
  AND date_added < '" . $mysql_datetime_end . "' 
  GROUP BY DATE( date_added )"

be sure to sanitize your date time start and end before using them.