[eluser]Bigil Michael[/eluser]
In a school management system, I want to calculate the workload of each teacher in that school. I have created 3 tables, given below
1. staff table
stf_id stf_firstname
1 A
2 B
3 C
2. taff_subjects table
id stf_id subjects
1 1 1
2 1 2
3 1 3
4 2 1
5 2 3
3. timetable table
in this table i am saving only subjects
id period monday tuesday wednesday thursday friday saturday
1 2 1 .....
2 1 2 ......
3 3 ....
4 2
5 1
I want the result like this
staff name Monday Tuesday wednesday thursday friday saturday total
A 3 periods 2prds 5prds ..................... 25 prds
B 1 prds 6 prds ........................... 22 prds
written the query for monday only
Code:
$this->db->select('S.stf_firstname as teacher, count(T.monay) as monday_count');
$this->db->from('staffs as S');
$this->db->join('staff_subjects as SS','SS.stf_id = S.stf_id','LEFT');
$this->db->join('timetable as T','T.monday = SS.subjects OR T.tuesday = SS.subjects OR T.wednesday = SS.subjects OR T.thursday = SS.subjects OR T.friday = SS.subjects OR T.saturday = SS.subjects ','LEFT');
$this->db->group_by('S.stf_id');
$result_courses = $this->db->get();
return $result_courses->result_array();
but it did not print the correct count.
can any one solve this query or suggest an alternative method to solve this issue..