• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Handling Stored Procedures that return multiple record sets

#10
(03-06-2018, 02:47 AM)oscar1925 Wrote:
(03-06-2018, 02:01 AM)Muthusamy Wrote:
(03-05-2018, 11:49 PM)oscar1925 Wrote:
(06-05-2017, 05:16 PM)skunkbad Wrote:
(06-01-2017, 10:11 AM)brocknessmonster Wrote: Our database engineer is fond of creating stored procedures that return multiple record sets/results. I have had success in the past with hacking the CI 2 system files to accommodate multiple recordsets but now we are using CI 3.1.X and things have changed a little bit.

There are some known hacks that are used to add this functionality to CI 2.  Can these be added to CI 3?  We do a lot of data abstraction and visualization in our applications.

Thanks.


Try this:


PHP Code:
public function test()
{
 
   $sql 'CALL your_procedure()';
 
   $results $this->multiple_result_array$sql );

 
   echo '<pre>';
 
   var_dump($results);
 
   echo '</pre>';

 
   $sql 'CALL your_procedure()';
 
   $results $this->multiple_result$sql );

 
   echo '<pre>';
 
   var_dump($results);
 
   echo '</pre>';
}

/**
 * This could go in MY_Model:
 */
public function multiple_result_array$sql )
{
 
   if( empty( $sql ) )
 
       return NULL;

 
   $i 0;
 
   $set = [];

 
   ifmysqli_multi_query$this->db->conn_id$sql ) )
 
   {
 
       do 
        
{
 
           mysqli_next_result$this->db->conn_id );
 
           
            if
FALSE != $result mysqli_store_result$this->db->conn_id ) )
 
           {
 
               $row_id 0;

 
               while$row $result->fetch_assoc() )
 
               {
 
                   $set[$i][$row_id] = $row;
 
                   $row_id++;
 
               }
 
           }

 
           $i++;
 
       
 
       while mysqli_more_results$this->db->conn_id ) );
 
   }

 
   return $set;
}

/**
 * This could go in MY_Model:
 */
public function multiple_result$sql )
{
 
   if( empty( $sql ) )
 
       return NULL;

 
   $i 0;
 
   $set = [];

 
   ifmysqli_multi_query$this->db->conn_id$sql ) )
 
   {
 
       do 
        
{
 
           mysqli_next_result$this->db->conn_id );
 
           
            if
FALSE != $result mysqli_store_result$this->db->conn_id ) )
 
           {
 
               $row_id 0;

 
               while$row $result->fetch_object() )
 
               {
 
                   $set[$i][$row_id] = $row;
 
                   $row_id++;
 
               }
 
           }

 
           $i++;
 
       
 
       while mysqli_more_results$this->db->conn_id ) );
 
   }

 
   return $set;




i try this and it works... my problem now is how to echo/view the records on my view using <?php foreach ($data as $rows): ?>

this is my data...

Code:
[color=#000000]

array(1) {
 [0]=>
 array(4) {
   [0]=>
   object(stdClass)#31 (9) {
     ["student"]=>
     string(2) "17"
     ["section"]=>
     string(8) "Hinam-is"
     ["subject"]=>
     string(21) "Afro Asian Literature"
     ["loadid_id"]=>
     string(2) "12"
     ["grade1"]=>
     string(2) "80"
     ["grade2"]=>
     string(2) "79"
     ["grade3"]=>
     string(1) "0"
     ["grade4"]=>
     string(1) "0"
     ["average"]=>
     string(5) "39.75"
   }
   [1]=>
   object(stdClass)#32 (9) {
     ["student"]=>
     string(2) "17"
     ["section"]=>
     string(8) "Hinam-is"
     ["subject"]=>
     string(18) "Araling Panlipunan"
     ["loadid_id"]=>
     string(1) "9"
     ["grade1"]=>
     string(2) "90"
     ["grade2"]=>
     string(2) "85"
     ["grade3"]=>
     string(2) "75"
     ["grade4"]=>
     string(2) "95"
     ["average"]=>
     string(5) "86.25"
   }
   [2]=>
   object(stdClass)#33 (9) {
     ["student"]=>
     string(2) "17"
     ["section"]=>
     string(8) "Hinam-is"
     ["subject"]=>
     string(40) "Music, Arts, Physical Education, Health "
     ["loadid_id"]=>
     string(2) "11"
     ["grade1"]=>
     string(2) "81"
     ["grade2"]=>
     string(2) "83"
     ["grade3"]=>
     string(2) "89"
     ["grade4"]=>
     string(2) "90"
     ["average"]=>
     string(5) "85.75"
   }
   [3]=>
   object(stdClass)#34 (9) {
     ["student"]=>
     string(2) "17"
     ["section"]=>
     string(8) "Hinam-is"
     ["subject"]=>
     string(21) "Philippine Literature"
     ["loadid_id"]=>
     string(1) "8"
     ["grade1"]=>
     string(2) "88"
     ["grade2"]=>
     string(1) "0"
     ["grade3"]=>
     string(1) "0"
     ["grade4"]=>
     string(1) "0"
     ["average"]=>
     string(2) "22"
   }
 }
}

[/color]

Help....

I try this Please check the below code:

Code:
$test =Array( "0" => Array
(
    "0" => (Object)
        [
            "PRO_PRICE_CODE" => "AAA001001",
            "PRO_MST_NM" => "Promo Air Asia",
            "PRO_AMT_CUST" => "65000.00"
       ],

     "1" => (Object)
        [
            "PRO_PRICE_CODE" => "AAAnghngng001001",
            "PRO_MST_NM" => "Asia Airlines",
            "PRO_AMT_CUST" => "55000.00"
        ]
)

);

foreach ($test as $key => $value) {
    echo $value["0"]->PRO_PRICE_CODE;   
}

[Image: lightbulb.gif] it somehow works...nice but do i need to always code..
Code:
<?php foreach ($data as $key => $value): ?>
  <?php echo "Sakto ".$value[0]->loadid_id; ?>
  <?php echo "Sakto ".$value[0]->subject; ?>
  <?php echo "Sakto ".$value[1]->loadid_id; ?>
  <?php echo "Sakto ".$value[1]->subject; ?>
 <?php endforeach; ?>

do you have a code to loop these data?

check this!!!
Code:
foreach ($test as $key => $value) {
    foreach ($value as $key1 => $value1) {
        echo $value1->PRO_PRICE_CODE;   
    }

    
}
Reply


Messages In This Thread
RE: Handling Stored Procedures that return multiple record sets - by Muthusamy - 03-06-2018, 04:08 AM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.