php - 如何对CodeIgniter和MySql表中调查的房屋数量进行求和

Question

这是我的 SQL 问题...

我有三个表:

类(class)子类别

-------------------------------------------------------
| course_subcat_id | course_cat_id| subcoursename| code| 
-------------------------------------------------------

类(class)类别

-------------------------------------------------------
| course_cat_id |  coursename| code| project  |deleted|
-------------------------------------------------------

事件

------------------------------------------------------------------------------
|event_id|title| project |course_cat_id |course_subcat_id|no_of_house_surveyed
-----------------------------------------------------------------------------

-

我想显示调查的房屋总数和发生的事件数量具有相同的 course_cat_id 和 course_subcat_id。

这是我的代码。

models

function getCourseCat(){
        $this->db->select('COUNT(e.title) as no_of_events, SUM(e.no_of_house_surveyed) as total_house_surveyed, c.course_cat_id, c.coursename,s.course_subcat_id,
                  s.subcoursename' )
                  ->from('events e')
                  ->join('course_category c ON e.course_cat_id = c.course_cat_id')
                  ->join('course_subcategory s ON e.course_subcat_id = s.course_subcat_id')
                  ->group_by('e.event_id');
          $result=$this->db->get()->result();
          }

controller

$data['course_cat'] = $this->test_event_summary_model->getCourseCat();在此处输入代码

views

foreach($course_cat->result() as $c){

                if ($c->no_of_events == 0) {
                    continue;
                }
echo "<td>".$c->no_of_events."</td>";

                $total_events += $c->no_of_events;
}

Answer 1

我希望这个答案能够解决您的问题-

Model

function getCourseCat(){
    $where = "e.course_cat_id = c.course_cat_id AND e.course_subcat_id = s.course_subcat_id";
    $this->db->select('c.course_cat_id,c.coursename,s.course_subcat_id,s.subcoursename,COUNT(e.title) as no_of_events,SUM(e.no_of_house_surveyed) as total_house_surveyed');
    $this->db->from('events e');
    $this->db->from('course_category c');
    $this->db->from('course_subcategory s');
    $this->db->where($where);
    $this->db->group_by(array("c.course_cat_id", "c.coursename", "s.course_subcat_id", "s.subcoursename"));
    $query = $this->db->get();
    $res = $query->result_array();
    return $res;
}

Controller

public function index(){
    $this->load->model('test_event_summary_model');
    $res['results'] = $this->test_event_summary_model->getCourseCat();
    $this->load->view('cat_view',$res);
} 

View

foreach ($results as $res) {
    echo "<b>course_cat_id</b> - ".$res['course_cat_id']."<br>";
    echo "<b>coursename</b> - ".$res['coursename']."<br>";
    echo "<b>course_subcat_id</b> - ".$res['course_subcat_id']."<br>";
    echo "<b>subcoursename</b> - ".$res['subcoursename']."<br>";
    echo "<b>no_of_events</b> - ".$res['no_of_events']."<br>";
    echo "<b>total_house_surveyed</b> - ".$res['total_house_surveyed'];
}

Output