如何从表CodeIgniter和MySql中调查所调查的房屋数量

Question

Here's my SQL question...

I have three tables:

course_subcategory

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

course_category

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

events

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

-

I want to show the total number of house surveyed and number of events occured having same course_cat_id and course_subcat_id.

Here is my code.

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();enter code here

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

I hope this answer will solve your problem-

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