Fetch data from monthly table MySQL based on date range (ex-data between 01-Jan-2016 to 01-April-2016)

Question

I have monthly database tables which store daily user signup count of my website.in my UI design I have daterange piker between two date I have to get sum of signup user between date range provided .(But there will be 4 OR 5 many month between date range (based on date range)) How I will get data ? I need optimised solution so I will get that sum of data from multiple mysql tables.

CREATE TABLE daily_analytics_01_2017 ( 
id int(11) NOT NULL AUTO_INCREMENT, 
country varchar(255) DEFAULT NULL, 
device varchar(255) DEFAULT NULL, 
browser varchar(255) DEFAULT NULL, 
gender varchar(255) DEFAULT NULL, u
ser_loginCount int(11) NOT NULL, 
user_signup_count int(11) NOT NULL, 
tracking_date date NOT NULL, 
PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

Above table for each month name like

daily_analytics_MONTH_YEAR

along with this all data for login and other action on my site I have to track on monthly basis .

Answer 1

Use Following Function to built query:

function getMonthWiseQuery($fromdate,$todate)
{

    $fromexplode=explode("-",$fromdate);
    $startyear=$fromexplode[0];
    $startmonth=$fromexplode[1];
    $startdate=$fromexplode[2];


    $toexplode=explode("-",$todate);
    $endyear=$toexplode[0];
    $endmonth=$toexplode[1];
    $enddate=$toexplode[2];

    $queryBuild=array();
    $startmonthnew = $startmonth;
    $count=0;
    for ($i = $startyear; $i <= $endyear; $i++) {
        for ($j = $startmonthnew; $j < 13; $j++) {

                $count++;
                if ($fromdate) {

                    if (strlen($j) == 1) {
                        $j = "0" . $j;
                    }
                    if($count!=1)
                    {
                        $query=" UNION ALL";
                        $query.= " select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_{$j}_{$i} where tracking_date BETWEEN \"{$fromdate}\" and \"{$todate}\" ";
                    }else{
                        $query = "select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_{$j}_{$i} where tracking_date BETWEEN \"{$fromdate}\" and \"{$todate}\" ";
                    }

                    array_push($queryBuild,$query);

                }

            if ($j == 12) {
                $startmonthnew = 1;
                break;
            }

            if ($endyear == $i) {
                if ($j == $endmonth) {
                    break;
                }
            }

        }
    }

return implode(" ",$queryBuild);
}

Function will return union query with all table in date range-

    select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_01_2017 where tracking_date BETWEEN "2017-01-01" and "2017-03-21"  
UNION ALL 
select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_02_2017 where tracking_date BETWEEN "2017-01-01" and "2017-03-21"  
UNION ALL 
select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_03_2017 where tracking_date BETWEEN "2017-01-01" and "2017-03-21" 

Then from this query we can fetch sum of total result from UNion of tables-

   select sum(Total_UserBy_referral) as Total_UserBy_referral , sum(TotalUnverifiedUsers) as TotalUnverifiedUsers,sum(TotalRegisteredUsers) as TotalRegisteredUsers, sum(TotalActivatedUsers) as TotalActivatedUsers from (
select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_01_2017 where tracking_date BETWEEN "2017-01-01" and "2017-03-21"  
UNION ALL 
select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_02_2017 where tracking_date BETWEEN "2017-01-01" and "2017-03-21"  
UNION ALL 
select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_03_2017 where tracking_date BETWEEN "2017-01-01" and "2017-03-21" 
) as t

Answer 2

check this code , it will show all previous 12 month login user signup information . you need to change this code as your table field and table name.

select date_year y, date_month m, count(*) cnt
            from
            (select id, year(access_time) date_year, month(access_time) date_month, access_time, uid
            from users_login_detail_history
            where date(access_time) >= date_format(concat (year(date_add(date(now()), interval -1 year)), '-' , month(date_add(date(now()), interval -1 year)) , '-1'), '%Y-%m-%d')) login_data
            group by date_year, date_month
            order by date_year, date_month;

From this sql this is output result

Answer 3

$sql = "SELECT  id, country,device,     
            LOWER(MONTHNAME(tracking_date)) AS month,
            YEAR(tracking_date) AS `year`,
            SUM(user_signup_count) AS `monthly_sum`
        FROM daily_analytics_01_2017
        WHERE tracking_date BETWEEN '$first' AND '$last'
        GROUP BY month";

Explanation: It is not a good idea to create table month wise, create a single table and use that. above query will be enough for that.

$first - start date
$last - end date

Answer 4

Wouldn't it be a better if you store all the sign ups in a single table? It's easier to query and maintain.

// the selected range will have to be formatted
$from = '2017-01-01 00:00:00';
$to = '2017-03-15 23:59:59';

// you will have to run this query just once on one particular table 
$query = "SELECT count(*) as number_of_signups FROM tablename WHERE created_on BETWEEN $from AND $to";

That being said if you do prefer to store sign ups in different tables on a monthly basis you can query each table with a data range on the timestamp column.

// initialization
$count = 0;

// the selected range will have to be formatted
$from = '2017-01-01 00:00:00';
$to = '2017-03-15 23:59:59';

$con=mysqli_connect("yourhost","dbuser","password","your_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

// run this query on all the signup tables 
$query = "SELECT count(*) as number_of_signups FROM tablename WHERE created_on BETWEEN $from AND $to";

$result=mysqli_query($con,$query);

// Associative array
$row=mysqli_fetch_assoc($result);
$count += $row['number_of_signups'];

// Free result set
mysqli_free_result($result);

mysqli_close($con);

As you can see this part gets really lengthy and complicated:

// run this query on all the signup tables 
$query = "SELECT count(*) as number_of_signups FROM tablename WHERE created_on BETWEEN $from AND $to";

$result=mysqli_query($con,$query);

// Associative array
$row=mysqli_fetch_assoc($result);
$count += $row['number_of_signups'];