how to select database content foreach month?
i have table named visits in my database like this :
id ip action_date|time_stamp
i use this code to store site visits
/* Hits table has an auto-incrementing id and an ip field */
// Grab client IP
$ip = $this->input->ip_address();
// Check for previous visits
$query = $this->db->get_where('visits', array('ip'开发者_开发知识库 => $ip), 1, 0);
$query = $query->row_array();
if (count($query) < 1 )
{
// Never visited - add
$this->db->insert('visits', array('ip' => $ip) );
}
it's working nice . but my client need to know how many visits they have in month . how can i do that ? tanks .
How about something like:
$months = $this->db->select('
COUNT(`id`) AS "count",
DATE_FORMAT(FROM_UNIXTIME(`action_date`),"%b-%Y") AS "month"',false
)
->group_by('month')
->where(array('ip' => $ip))
->order_by('action_date','desc')
->get('visits')->result();
Should give you data like:
+-------+----------+
| count | month |
+-------+----------+
| 505 | Apr-2010 |
| 252 | Mar-2010 |
| 426 | Feb-2010 |
| 201 | Jan-2010 |
| 211 | Dec-2009 |
+-------+----------+
精彩评论