Daily Archives: August 29, 2012

Getting Records from Multiple Result sets in PHP

Posted on by | Leave a reply

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);

Building Dynamic Query in Mysql Stored Procedure

Posted on by | 1 Reply

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')

Stored Procedure

DROP PROCEDURE IF EXISTS mp_test;
CREATE PROCEDURE mp_test(IN pArea VARCHAR(255))
   BEGIN
      SET @Query = '';
      SET @City  = '';
      SET @Query = 'SELECT PinCode FROM Areas';

      IF pArea != '' THEN
         SET @City = CONCAT(' WHERE AreaName = "', pArea, '"');
      END IF;
      
      SET @Query = CONCAT(@Query, @City);

 PREPARE stmt FROM @Query;
 
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;  
END