Calling Stored Procedure inside foreach PHP Codeigniter

Question

I'm having an error when calling stored procedure inside foreach
It says "Commands out of sync you can't run this command now" .
I already looking for solution, but the result is not like i'm expecting. This is my code

$query = "CALL PROCEDURE_HEAD()";
$sql   = $this->db->query($query)->result_array();
foreach($sql as $key) {
   $name = $key['name'];
   $array['name'] = $name;
   $array['data'] = $key['data'];

   $query2 = "CALL PROCEDURE_CHILD('$name')";
   $sql2   = $this->db->query($query2)->result_array();

   foreach($sql2 as $value) {
     $array['child'] = array(
                        'child_name' => $value['child_name'],
                        'child_data' => $value['child_data']
                       );
   }
}

i have tried run codeigniter : Commands out of sync; you can't run this command now, and because i'm using Procedure after Procedure,it doesn't run.
Any kind of help is really appreciated

score 1 · Accepted Answer · answered Nov 19 '19 at 08:22

Use Model with associate this

In Controller

$head = $this->Model_name->call_head();

foreach($head as $item) {
    $name = $item['name'];
    $array['name'] = $name;
    $array['data'] = $item['data'];

    $child = $this->Model_name->call_child($name);

    foreach($child as $value) {
        $array['child'] = array(
            'child_name' => $value['child_name'],
            'child_data' => $value['child_data']
           );
    }
}

In model

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

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

Calling Stored Procedure inside foreach PHP Codeigniter

1 Answers1