CODEWITHSUNDEEP
phpmysqlcodeigniterdate
Duwi irwanto
Duwi irwanto

Reputation: 139

count date per month , per year on database with date format with CodeIgniter

how to calculate the amount of data in 1bulan / 1 year on the basis of the date in the database based on the date format?i am using codeigniter

Example

date 2016-02-10

2016-05-19

2016-07-20

2016-02-30

2016-05-20

2016-05-21

Result count

data on Mounth Feb = 2

data on Mounth May = 3

data on Mounth Jul = 1

This My script

<?php 
        $sql = "SELECT * FROM tbcounter ";
        $get_monthly = " WHERE date LIKE '".date("Y-m")."%'";
        $monthly = mysql_num_rows(mysql_query($sql . $get_monthly));    
        echo $monthly;                      
?>

Upvotes: 0

Views: 1419

Answers (2)

Praveen Kumar
Praveen Kumar

Reputation: 2408

You can use DATE_FORMAT(date_column,'desired_output') as in this example:- You can read about date function on http://php.net/manual/en/function.date.php 

$date_variable = date("Y-m",strtotime('last month')); //prints 2016-01 
echo   $this->db->get_where("tbcounter",array("DATE_FORMAT(`date`,'%Y-%m')"=> $date_variable ))->num_rows();

This is demo code :)

Upvotes: 1

Bugfixer
Bugfixer

Reputation: 2617

For Counting data per year and per month you can use 

GROUP BY YEAR(date), MONTH(date)

In your query.

Example :

SELECT count( * )
FROM `TableNAme`
GROUP BY YEAR( `Date` )
LIMIT 0 , 30


SELECT count( * )
FROM `TableNAme`
GROUP BY MONTH( `Date` )
LIMIT 0 , 30

Here Date is your column name in table.

Upvotes: 0

Related Questions