Running Queries In Nested Loop

Question

Before Explain the code i have to explain structure of data

So I have data in this format

=>Department
==>Category
===>Sub-Category

There are multiple department having multiple categories and those categories have sub-categories.

There can be a many category having same name but different department and same in sub-category and category.

There is table contain all the data and product. I have to extract data in a proper and put in table of

Departments 
Categories => fk is Departments id
Subcategories => fk is Categories id

First i am inserting the data in departments

Then In categories first i have get id of department then insert into categories table:

public function insertDataInCategory($data)
{   
    if(!empty($data)){
        //pass data from hierarchy
        foreach ($data as $dkey => $drow) {
            //get id
            $this->db->select('id');
            $this->db->from('departments');
            $this->db->WHERE('name',$dkey);
            $query = $this->db->get();
            //getting department it to identify category  
            $did = $query->result_array(); 

            //adding category from department
            foreach ($drow as $ckey => $crow) {
                $cat_content = array(
                   'department_id' => $did[0]['id'] ,
                   'name' => $ckey ,
                   'description' => ''
                );
                $this->db->insert('categories', $cat_content);
            }
        }
    }
}

in subcategories the first i have to get id of department for category and after that i identify that this sub-category belong to that category. So there are 3 queries running here that are increasing the time of execution.

public function insertDataInSubcategory($data)
{   
    //pass data from hierarchy
    if(!empty($data)){
        //department foreach
        foreach ($data as $dkey => $drow) {
            //category each
            $this->db->select('id');
            $this->db->from('departments');
            $this->db->WHERE('name',$dkey);
            $query = $this->db->get();
            //getting department it to identify category
            $did = $query->result(); 
            foreach ($drow as $ckey => $crow) {
                //get id
                $this->db->select('id');
                $this->db->from('categories');
                $this->db->WHERE('name',$ckey);
                $this->db->WHERE('department_id',$did[0]->id);
                $query = $this->db->get();
                //getting id of category of sub-category 
                $cid = $query->result_array(); 
                //sub-category foreach
                foreach ($crow as $skey => $srow) {
                    $scat_content = array(
                       'category_id' => $cid[0]['id'] ,
                       'name' => $skey ,
                       'description' => ''
                    );
                    $this->db->insert('sub_categories', $scat_content);
                }       
            }
        }
    }
}

The functions of insertDataInCategory and insertDataInSubcategory time is increasing drastically. Please tell me how to decrease it time. and proper method to insert into tables

What are the contents of $data that is passed to insertDataInCategory($data) and to insertDataInSubcategory($data) ? Please show the structure and a short example of values that might be passed. — DFriend
– DFriend, Commented Oct 29, 2017 at 14:19

xitas · Accepted Answer · 2017-12-15 12:16:42Z

The problem is you are running queries multiple times which increases execution time. running a single query will reduce your time.

try this Code:

public function insertDataInCategory($data)
{   
    if(!empty($data)){
        //pass data from hierarchy
        foreach ($data as $dkey => $drow) {
            //get id
            //adding category from department
            foreach ($drow as $ckey => $crow) {
                   $query = "INSERT INTO categories ";
                   $query.= "(department_id, name , description)";
                   $query.= "VALUES ((SELECT id FROM departments WHERE name = '$dkey') , '$ckey' , '')";
                   $this->db->query($query);
            }
        }
    }
}

public function insertDataInSubcategory($data)
{   
    //pass data from hierarchy
    if(!empty($data)){
        //department foreach
        foreach ($data as $dkey => $drow) {
            foreach ($drow as $ckey => $crow) {
                //sub-category foreach
                foreach ($crow as $skey => $srow) {
                   $query = "INSERT INTO sub_categories ";
                   $query.= "(category_id , name , description)";
                   $query.= "VALUES ((SELECT id FROM categories WHERE name = '$ckey' && department_id = (SELECT id FROM departments WHERE name = '$dkey')) , '$skey' , '')";
                   $this->db->query($query);
                }       
            }
        }
    }
}

Now only one connection is set and it will insert record much quicker.

Bhushan · Accepted Answer · 2017-10-28 13:30:01Z

0

Please modify function as I mentioned below. It will solve your nested loop issue. Here I am assuming $this->db->insert is returning inserted row id. If it's not returning id then you need to get it using select query and store it in mentioned variable.

 public function insertDataInDepartment($data)
 {
     if(!empty($data)){
         //pass data from hierarchy
         foreach ($data as $dkey => $drow) {
                 $dept_content = array(
                'name' => $dkey ,
                'description' => '' ,
                'is_active' => 'y'
             );
              $intInsertId = $this->db->insert('departments', $dept_content);
           insertDataInCategory($drow , $intInsertId);
         }
     }
 }

 public function insertDataInCategory($drow, $deptId)
 {   
      foreach ($drow as $ckey => $crow) {
          $cat_content = array(
               'department_id' => $did[0]['id'] ,
               'name' => $ckey ,
               'description' => ''
          );
          $intCatId = $this->db->insert('categories', $cat_content);
          insertDataInSubcategory($crow, $intCatId);
     }
 }

 public function insertDataInSubcategory($data)
 {   
      foreach ($crow as $skey => $srow) {
           $scat_content = array(
                'category_id' => $cid[0]['id'] ,
                'name' => $skey ,
                'description' => ''
           );
           $this->db->insert('sub_categories', $scat_content);
     }
 }

answered Oct 28, 2017 at 13:30

Bhushan

6303 silver badges9 bronze badges

7 Comments

Hamza Over a year ago

in this code i am not getting id of department for category table

Hamza Over a year ago

i have to improve query

Bhushan Over a year ago

Then you have to use select query to get interested I'd.

Hamza Over a year ago

$this->db->select('id');             $this->db->from('departments');             $this->db->WHERE('name',$dkey);             $query = $this->db->get();

Hamza Over a year ago

this is the select query

|

Collectives™ on Stack Overflow

Running Queries In Nested Loop

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related