how to display records in PHP from stored procedure Mysql

I m creating stored procedure with multiple select statement,as shows below:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testsp` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `testsp`(

)
BEGIN
select area_id,areaname from area;
select loc_id,locname from location;
END $$

DELIMITER ;

This shows as 2 different results..though i want to disp开发者_Go百科lay different in PHP. Php code:

$res = $mysqli->query("call testsp()");
  while($row = $res->fetch_assoc())
   {
   $arr[] = $row['area_id'];
   $arr[] = $row['areaname'];
   $arr1[] = $row['loc_id'];
   $arr1[] = $row['locname'];
   }
   echo '{"users":'.json_encode($arr).'}';
   echo '{"users":'.json_encode($arr1).'}';

when i try to display $arr1,its showing null values...so how do i display second result sets in Php.

you have one option use union all to combine this 2 queries.

like :

select 1 as type, area_id as id ,areaname as name from area
union all 
select 2 as type , loc_id as id ,locname as name from location; 

another option is to use a cursor, loop over to sql queries by using cursor.

then INSERT the results of each loop into a temporary table and then SELECT * from that table when you're done looping.