CODEWITHSUNDEEP
phpmysqlcodeigniter
wasim
wasim

Reputation: 51

how to handle sql query in foreach loop in codeigniter

function emp_attendence() {
$dept = $this->session->userdata("department") ;
                 function filter($value)
                    {
                      $dept = "'$value'" ;  
                      return $dept;
                    }
              $query = $this->db->query('select emp_id from employee where department IN ('.implode( ",",array_map("filter",$dept) ) .') order by emp_id asc');
              $emp = $query->result_array()  ; // echo '<pre>' ;  print_r($emp[0]['emp_id']) ; exit();  



          foreach( $emp as $emp_id) {
                    foreach( $emp_id as $id) {
              $query = "SELECT count(attendence) as total_attendence 
                   FROM employee
                   INNER JOIN attendence ON employee.emp_id = attendence.employee_id
                   WHERE emp_id='$id' AND MONTH(date)=MONTH(CURDATE())";

                   $query = $this->db->query($query);  
                   $query = $query->result_array() ;
                }
        } 

         return $query ;  
        }

This above code is a model in codeigniter , my aim is to get total attendance for each employee in database , Code is running fine but i am getting value of my last record return by query not all records !!!

Upvotes: 1

Views: 2640

Answers (2)

mickmackusa
mickmackusa

Reputation: 47864

This honestly needs a complete refactor. You can bake all of your query logic into one query -- and should.

A model method should not know where its input comes from. In other words, you should not be hardcoding the method to draw $dept from the session array. Instead, pass all input parameters to the method as arguments so that your model methods are easily testable and re-usable.

CodeIgniter has special syntax for writing an IN comparison with a placeholder (it is not best practice to directly inject variables into your $sql string). After IN do not wrap your placeholder ? in parentheses -- CI does this for you. Also, CI will quote wrap strings with the appropriate quotes.

The new script below will make just one trip to the database and return an associative array with emp_id values as the keys and counts as values.

public function empAttendence(array $dept): array
{
    $sql = "SELECT e.emp_id,
                   COUNT(1) total_attendence 
            FROM employee   e
            JOIN attendence a ON e.emp_id = a.employee_id
            WHERE e.department IN ?
                  AND MONTH(a.date) = MONTH(CURDATE())
            GROUP BY e.emp_id
            ORDER BY e.emp_id";

    return array_column(
        $this->db->query($sql, [$dept])->result_array(),
        'total_attendance',
        'emp_id'
    );
}

Upvotes: 0

Death-is-the-real-truth
Death-is-the-real-truth

Reputation: 72289

Actually $query = $query->result_array() ; is a variable assignment and since its in loop so it is overwriting same variable again and again. So you got only last data.

Do like below:-

before foreach( $emp as $emp_id) { define $attendence_array = array();

Instead of $query = $query->result_array() ; write $attendence_array[] = $query->result_array() ;

Then instead of return $query ; write return $attendence_array ;

Then check $attendence_array by printing it out that it comes fine or not what you want

Upvotes: 2

Related Questions