The number of rows per month for the year

Question

I want to select Registration per month.

<?php
include 'member_entry/sdg_connect.php';

$sql="select member_id from sdg_members where member_joindate BETWEEN  DATE('2015-02-30') AND DATE( '2016-12-31')";

if ($result=mysqli_query($conn,$sql))
  {
      $rowcount=mysqli_num_rows($result);
      echo $rowcount;

      mysqli_free_result($result);
  }

  mysqli_close($conn);
 ?>

Answer 1

As mentioned in scaisEdge, you should use group by:

select
  YEAR(member_joindate) AS year, MONTH(member_joindate) AS month, COUNT(DISTINCT member_id) AS joins
from
  sdg_members
where
  member_joindate BETWEEN '2015-02-30' AND '2016-12-31'
group by
  YEAR(member_joindate), MONTH(member_joindate)

This will give you results in the form

year | month | joins
2015 |     3 |    10
2015 |     4 |     3
2015 |     5 |    11
...

After that, you no longer need to apply num_rows, you just need to go through the result set

Answer 2

You can use the cumulative count () function and group it by month

$sql="select count(member_id), month(member_joindate) 
from sdg_members
where member_joindate BETWEEN  str_to_date('30/02/2015', '%d/%m/%Y')
    AND    str_to_date('31/12/2016', '%d/%m/%Y')  
group by month(member_joindate) ";

Answer 3

member_joindate DATE, :

SELECT COUNT(member_id), MONTH(member_joindate) 
from sdg_members
where member_joindate BETWEEN '2015-02-30' AND '2016-12-31'   
group by MONTH(member_joindate)

member_joindate DATETIME, :

SELECT COUNT(member_id), MONTH(member_joindate) 
from sdg_members
where member_joindate BETWEEN '2015-02-30 00:00:00' AND '2016-12-31 23:59:59'   
group by MONTH(member_joindate)

, MONTH() , MONTHNAME().

SELECT COUNT(sb_id), MONTHNAME(sb_ondate) 
from sbbleads_members
where sb_ondate BETWEEN '2015-02-30' AND '2016-12-31'   
group by MONTHNAME(sb_ondate);