I have a table leave_muster inside which I have to update all the records of the previous month on every start of the new month.

My Code

public function sync()
        $pre_month = (int)date('m') - 1;

        $data = $this->Leave_muster_model->get_by_month($pre_month);

        $updated_data = [];

        foreach ($data as $key => $value) {

            // Fetch each employee details to perform some check
            // Then update the array and 

            $updated_data[$key] = [
                'emp_id'                   => $value->emp_id,
                'accumulated_leaves'       => 0.0,
                'total_accumulated_leaves' => 0.0,
                'leave_taken'              => 0.0,
                'salary_deducted_days'     => 0.0,
                'leave_balance'            => 0.0,
                'month'                    => date('m'),
                'year'                     => date('Y'),
                'created_date'             => date('Y-m-d')


In the above code, I have to fetch the employee details before updating the leaves to perform some checks, So I have two ways

  1. Fetch the employee details inside the loop before any update to perform checks.
  2. Fetch all the employee details outside the loop into an array then get the employee by employee id the perform checks

I want to know which would be a better approach as I know that in any given point of time in the future there won't be any millions of employees in the table.

Lang: PHP
Framework: Codeigniter

  • 1
    \$\begingroup\$ You are "yatta-yatta"ing with perform some checks. This very relevant information for us to know. If the checks can be articulated within SQL, then it would be possible (best practice) to perform the whole process with a single, non-iterated query. Additionally, this is a cronjob, right. Are you executing INSERT or UPDATE queries with the qualifying data? Is $key relevant in your code, I am guessing not. \$\endgroup\$ – mickmackusa Apr 23 '19 at 8:48
  • \$\begingroup\$ @mickmackusa I cannot directly perform that checks with SQL queries, there are some calculations involved. \$\endgroup\$ – RopAli Munshi Apr 23 '19 at 8:52
  • \$\begingroup\$ Are you able to explain that bit to us (as an edit to your question)? SQL can do calculations. \$\endgroup\$ – mickmackusa Apr 23 '19 at 8:52
  • \$\begingroup\$ I am sure you've solved this already, but in case you have not. Not sure how many records you need to modify, but typically, I would've done a single query to retrieve all the data once off, and then loop through the data and do the checks that need to be done. When that is done, I will do the same loop you've done to set the new values, but not do the query inside the loop. I would rather use update_batch() once outside of the loop. See here: codeigniter.com/userguide3/database/query_builder.html \$\endgroup\$ – Kobus Myburgh Feb 13 '20 at 21:44

Browse other questions tagged or ask your own question.