how to handle sql query in foreach loop in codeigniter

Question

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 !!!

before foreach( $emp as $emp_id) { define $attendence_array = array(); and then instead of $query = $query->result_array() ; write $attendence_array[] = $query->result_array() ; and then instead of return $query ; write return $attendence_array ; and then check $attendence_array by printing it out that it comes fine or not what you want — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Mar 13, 2016 at 8:31
Thanks Anant for quick response , your solution is working like a charm !!! — wasim
– wasim, Commented Mar 13, 2016 at 8:44

Death-is-the-real-truth · Accepted Answer · 2016-03-13 08:50:04Z

2

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

answered Mar 13, 2016 at 8:50

Death-is-the-real-truth

72.3k10 gold badges59 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mickmackusa · Accepted Answer · 2023-11-02 08:21:19Z

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'
    );
}

Collectives™ on Stack Overflow

how to handle sql query in foreach loop in codeigniter

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related