shane shane - 4 years ago 98
MySQL Question

how to convert a normal mysql query in to codeigniter

I want to get the highest mark from mark table, but i have set the mark obtained as varchar becuase i want to store some text values in some cases.

when i write this in mysql it returns the highest mark as an integer value

SELECT MAX(`mark_obtained`) AS `mark_obtained` FROM `mark` WHERE `exam_id` = '1' AND `class_id` = '10' AND `section_id` = '32' AND `subject_id` = '21' AND `mark_obtained` BETWEEN 0 AND 100


this is the codeigniter method i wrote to obtained the highest mark,but when i give it prints the value it prints it always print the varchar value

function get_highest_marks($exam_id, $class_id, $section_id, $subject_id) {

$this->db->where('class_id', $class_id);
$this->db->where('section_id', $section_id);
$this->db->where('subject_id', $subject_id);
$this->db->where( "mark_obtained BETWEEN 0 AND 100", NULL, FALSE );

$highest_marks = $this->db->get('mark')->result_array();



foreach ($highest_marks as $row) {


echo $row['mark_obtained'];
}
}


so how to always print the highest integer value

Answer Source
function get_highest_marks($exam_id, $class_id, $section_id, $subject_id) {
        $this->db->select('MAX(mark_obtained) AS mark_obtained');
        $this->db->where('exam_id', $exam_id);
        $this->db->where('class_id', $class_id);
        $this->db->where('section_id', $section_id);
        $this->db->where('subject_id', $subject_id);
        $this->db->where('mark_obtained >=', 0);
        $this->db->where('mark_obtained <=', 100);
        $highest_marks = $this->db->get('mark')->result_array();
        foreach($highest_marks as $key=>$val) {
            echo $val = (int)$highest_marks[$key]['mark_obtained'];
        }
}

or

function get_highest_marks($exam_id, $class_id, $section_id, $subject_id) {
    $query = $this->db->query("SELECT MAX(mark_obtained) AS mark_obtained FROM mark 
    WHERE exam_id = '".$exam_id."' AND class_id = '".$class_id."' AND section_id 
    = '".$section_id."' AND subject_id = '".$subject_id."' AND mark_obtained 
    BETWEEN 0 AND 100");
    if($query->num_rows() > 0){
          $result = $query->row_array();
          echo $result['mark_obtained'];
    }
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download