Calling Stored Procedure inside foreach PHP Codeigniter

In CodeIgniter, you can call stored procedures within a foreach loop to process hierarchical data efficiently. This approach is useful when you need to fetch parent records and their related child records using separate stored procedures.

Controller Implementation

The controller handles the main logic by calling the model methods and processing the data in nested loops −

<?php
$header = $this->model_name->call_head();
foreach($header as $item) {
    $name = $item['name'];
    $array['name'] = $name;
    $array['data'] = $item['data'];
    $child_val = $this->model_name->call_child($name);
    foreach($child_val as $value) {
        $array['child'] = array(
            'child_name' => $value['child_name'],
            'child_data' => $value['child_data']
        );
    }
}
?>

Model Implementation

The model contains methods to execute the stored procedures and handle database connections properly −

<?php
public function call_head() {
    $query = "CALL PROCEDURE_HEAD()";
    $result = $this->db->query($query)->result_array();
    $this->db->next_result();
    $this->db->free_result();
    return $result;
}

public function call_child($name) {
    $query = "CALL PROCEDURE_CHILD(?)";
    $result = $this->db->query($query, array($name))->result_array();
    $this->db->next_result();
    $this->db->free_result();
    return $result;
}
?>

Key Points

When working with stored procedures in CodeIgniter, remember these important considerations −

• Always call next_result() and free_result() after executing stored procedures to prevent connection issues
• Use parameter binding with ? placeholders for security when passing variables to stored procedures
• The outer foreach processes parent records while the inner foreach handles child records

Conclusion

This pattern allows you to efficiently call stored procedures within nested loops in CodeIgniter. The key is proper database connection management using next_result() and free_result() methods to ensure clean execution of multiple stored procedure calls.