CODEWITHSUNDEEP
phpcodeignitermysqliphpmyadmindata-processing
Raheel Khan
Raheel Khan

Reputation: 494

How to Process SQL Query data in Php Codeigniter

NOTE:it's a specific issue, need answer for that.

This is my database table from where i want to fetch data. Database Table

my DB Query

select std.reg_id, std.name as student_name, r.marks as obtain_marks, q.ques_marks as total_marks,  cat.name as category_name, q.cat_id, course.name as course_name 
FROM tbl_result as r JOIN tbl_question as q on q.id = r.ques_id 
JOIN tbl_category as cat on cat.id = q.cat_id
JOIN tbl_batch_assigned_courses as assign on assign.id = r.course_offered_id 
JOIN tbl_courses as course on course.id = assign.course_id 
JOIN tbl_student as std on std.id = r.student_id 
WHERE r.student_id = 168 AND r.course_offered_id = 46

You see? There are many categories and each category has Obtain and total marks.

now what I want is to fetch the data from this table (already did) and display it on the view (already did).

But along with that I want to show that total obtain marks and and total marks of each category, and also sum of both.

For example:

Communication Skills has total obtain marks of 15 marks and it's total marks are 25.

(total obtain / total question marks) * 100 ~= [some value] for Category 1.

but i want the above expression for all categories (could be any number).

Php should do it automatically, just by passing, student, and course id.

My Controller Code: 

public function show_single_student_report(){
            $go = $this->input->post("submit_go");
            if(isset($go)) {
                $program_id = $this->input->post('program_id');
                $batch_id = $this->input->post('batch_id');
                $course_id = $this->input->post('course_id');
                $student_id = $this->input->post('student_id');

                $data['results'] = $this->csv_model->get_single_student_result($student_id, $course_id);

                $this->load->view('show_single_student_report', $data);
            }
            else{
                redirect("main/check_result");
            }


        }

the view code is :

$this->load->view('show_single_student_report', $data);

this

<?php foreach ($results as $std):  ?>
<div class="row">

         <?php $obtain += $std->obtain_marks; ?>
         Name : <?= $std->student_name;?> <br>
         obtain marks: <?= $std->obtain_marks; ?> <br>
         total_marks: <?= $std->total_marks;?> <br>
         category: <?= $std->category_name; ?><br>
         course: <?= $std->course_name; ?><br>
         category_id: <?= $std->cat_id;?> <br>
         </div>
         <div style="height: 50px;"></div>

         <?php endforeach;?>

Basically I don't know how to iterate the MYSQL Query via PHP.

Upvotes: 0

Views: 199

Answers (1)

Don Bhrayan Singh
Don Bhrayan Singh

Reputation: 475

You want to modify your query to aggregate the marks by category.

Try running:

SELECT 
    q.cat_id,
    SUM(r.marks) AS obtain_marks,
    SUM(q.ques_marks) AS total_marks,
FROM tbl_result as r 
JOIN tbl_question as q on q.id = r.ques_id 
GROUP BY q.cat_id

This should give you an idea how SUM() and GROUP BY works together. Then, just join it up with your other data:

SELECT 
std.reg_id, 
std.name as student_name
FROM tbl_student as std
JOIN
(
    SELECT 
    q.cat_id,
    r.student_id,
    SUM(r.marks) AS obtain_marks,
    SUM(q.ques_marks) AS total_marks,
    FROM tbl_result as r 
    JOIN tbl_question as q on q.id = r.ques_id 
    WHERE r.student_id = std.id
    GROUP BY q.cat_id
) tbl1
ON tbl1.student_id = std.id

Upvotes: 1

Related Questions