Table Structure

 CREATE TABLE Areas(AreaName VARCHAR(255),
                    PinCode VARCHAR(255))
                    
INSERT INTO Areas(AreaName, PinCode)
          VALUES('Teynampet',   '6000018'), 
                ('Ramapuram',   '6000089'),
                ('TNagar',      '6000017'), 
                ('Mylapore',    '6000014'), 
                ('Gopalapuram', '6000087')

Procedure which returns multiple result sets

 DROP PROCEDURE IF EXISTS mp_test1;
 CREATE PROCEDURE mp_test1()
 BEGIN
   SELECT AreaName FROM Areas;
   SELECT PinCode FROM Areas; 
 END 

Procedure Call

 CALL mp_test1()

PHP Code To retrieve Records from Multiple Resultsets

   $mysqli	= new mysqli('localhost', 'root', '', 'test','3306');
	
   $query = "CALL mp_test1()";
	
   $i = 0;	
	
   if ($mysqli->multi_query($query)) 
   {
    do {		
	/* store first result set */
        if ($result = $mysqli->use_result()) 
	{
	  $j = 0;
	  
          while ($row = $result->fetch_row()) 
	  {
	     $arrResult[$i][$j] = $row[0];
	     $j++;
	  }
				
   	  $result->close();
         }
			
	if($mysqli->more_results()) 
	{
  	  $i = $i + 1;
	}			 
     } while ($mysqli->next_result());
   }
	
   print "
";
   print_r($arrResult);

Leave a reply