How to display every month User Revenue details using PHP and mysql-CodePudding

I need to display every month User Revenue details in chart. Here I'm using PHP CodeIgniter frame work. In below I have mentioned my user controller:

public function Revenue_byuser() {
    $rev_data = [];
    $rev_array = [];
    
    $getuser_info = $this->User_Model->getuser_alldetail();
    for($i=0;$i<=count($getuser_info);$i  ) {
        $get_rev = $this->User_Model->getrev_byuserid($getuser_info[$i]->id);
        foreach($get_rev as $key => $val) {
            $rev_data[] = $val['total_revenue']??'0';
        }
        $rev_array[] = array('name' => $getuser_info[$i]->id, 'data' => $rev_data);    
    }
    
    $Return['revenue_data'] = $rev_array;
    $this->resp_output($Return);
    exit;        
 }

My User Model:

public function getuser_alldetail() {
    return $this->db->get_where('login', ['dept' => 1, 'status' => 1])->result() ;
}

public function getrev_byuserid($userid) {
    $query = "select sum(revenue) as total_revenue,DATE_FORMAT(upd_date,'%Y-%m') AS revmonth from revenue_details WHERE added_by = ? AND DATE_FORMAT(upd_date, '%y') = DATE_FORMAT(CURRENT_DATE, '%y') GROUP BY revmonth ORDER BY revmonth ASC";
    return $this->db->query($query,[$bdid])->result_array();
}

And This chart.js file:

$(window).on("load", function(){
    var siteurl = window.location.origin;
    $.ajax({
        url: siteurl '/User/Revenue_byuser',
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: function(resp) {
            var revuser_option = {
                chart: {
                    height: 250,        
                    type: 'line',
                    toolbar:{
                      show: false
                    }
                },
                dataLabels: {
                    enabled: false
                },
                stroke: {
                    curve: 'smooth'
                },
                series: resp.revenue_data,
                xaxis: {
                    type: 'date',
                    categories: ["Jan", "Feb", "Mar", "April", "May", "June", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"],
                },
                tooltip: {
                    x: {
                        format: 'dd/MM/yy'
                    },
                },
                colors: ['#111211','#15ad15','#0cebdc','#f21335','#f2b313','#131ff2','#cd13f2','#111411','#16ad15','#2cebdc','#f28335','#f9b313']
            };

            var user_revenue = new ApexCharts(document.querySelector("#rev_byuser"),revuser_option);
            user_revenue.render();
        }
    });
});

Then when print my response I'm getting same revenue details for all users.

But I want to print like this:

series: [{
    name: 'Username-1',
    data: [31, 40, 28, 51, 42, 109, 100, 17, 87, 12, 7, 89]
}, {
    name: 'Username-2',
    data: [33, 32, 76, 12, 44, 62, 31, 54,96,8,5,54]
},
{
    name: 'Username-3',
    data: [21, 50, 38, 41, 52, 10, 20]
}, {
    name: 'Username-4',
    data: [11, 32, 45, 32, 34, 52, 41]
}],

Where I made mistake here please help me out.

CodePudding user response：

If I understand you correctly, you are experiencing an ever increasing array for rev_data. On the second line of the public function Revenue_byuser(), you specify $rev_data as such:

$rev_data = [];

A bit further on you fill that array with a new index:

    foreach($get_rev as $key => $val) {
        $rev_data[] = $val['total_revenue']??'0';
    }

Problem is, you don't empty the array after you've add the data for that specific user. As such, you keep pushing data into an array which you re-use for each user.

To solve this, empty the array after you've added the data for a user:

$rev_array[] = array('name' => $getuser_info[$i]->id, 'data' => $rev_data);    
$rev_data    = [];