从php调用嵌套存储过程

Question

I am trying to call a stored procedure from php which in turn calls another stored procedure. When i run the stored procedure call in commandline it returns multiple result whereas while calling through php results in only one result. This is my procedure

//first procedure
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `viewalldiamondids`(
in pass_shape int,pass_symmetry varchar(255),in pass_polish varchar(255), in pass_first_carat double,in pass_last_carat double,in pass_clarity double,in pass_color int,
in pass_certificate int,in pass_fluorescence_id int,in pass_first_table_width double,in pass_last_table_width double,
in pass_first_depth double,in pass_last_depth double,
in pass_first_final_price double,in pass_last_final_price double)
block1:begin 
declare first_carat double;
declare first_measure_height double;
declare first_measure_width double;
declare first_measure_depth double;
declare first_final_price double;
declare first_cut int;
declare first_clarity int;
declare first_color int;
declare first_table_width int;
declare first_depth double;
declare first_fluorescence_id int;
declare first_certificate int;
declare first_len_width_ratio double;
declare first_var int;
declare first_measure varchar(255);
declare first_diamond int;
declare second_diamond int;
declare no_more_rows boolean;
declare itemarray varchar(255);
declare ans int;
DECLARE curs CURSOR FOR select item_id from caratlane_enduser_v where shape=pass_shape and color = pass_color and fluorescence_id = pass_fluorescence_id and polish = pass_polish and clarity = pass_clarity and symmetry = pass_symmetry and certificate = pass_certificate and carat >= pass_first_carat and carat <= pass_last_carat and final_price >= pass_first_final_price and final_price <= pass_last_final_price and total_depth >= pass_first_depth and total_depth <= pass_last_depth and table_width >= pass_first_table_width and table_width <= pass_last_table_width;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
create table pair_find(first_id int,second_id int);
open curs;
first:loop
fetch curs into ans;
       IF no_more_rows THEN
            CLOSE curs;
            LEAVE first;
        END IF;
set first_measure_height=substring(first_measure,1,4);
set first_measure_width=substring(first_measure,6,4);
set first_measure_depth=substring(first_measure,11,14);
block2:begin
declare cur CURSOR FOR select item_id,carat,cut,clarity,color,certificate,table_width,total_depth,fluorescence_id,len_width_ratio,measurements,final_price from caratlane_enduser_v where item_id = ans;
open cur;
fetch cur into first_var,first_carat,first_cut,first_clarity,first_color,first_certificate,first_table_width,first_depth,first_fluorescence_id,first_len_width_ratio,first_measure,first_final_price;
set itemarray=first_var;
set itemarray=concat(itemarray,',');
call viewalldiamondinnerloop(first_var,first_carat,first_cut,first_clarity,first_color,first_certificate,first_table_width,first_depth,first_fluorescence_id,first_len_width_ratio,first_measure,first_final_price,itemarray);
close cur;
end block2;
end loop first;
end block1

//second procedure
viewalldiamondinnerloop:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `viewalldiamondinnerloop`( 
    in first_var int, in first_carat double,
    in first_cut int,in first_clarity double,in first_color int,
    in first_certificate int,in first_table_width double,
    in first_depth double,in first_fluorescence_id int,
    in first_len_width_ratio double,in first_measure varchar(255),
    in first_final_price double,in itemarray varchar(255))
    begin 
    declare first_id int;
    declare second_id int;
    declare pair_carat double;
    declare pair_measure_height double;
    declare pair_measure_width double;
    declare pair_measure_depth double;
    declare pair_final_price double;
    declare pair_cut int;
    declare pair_clarity int;
    declare pair_color int;
    declare pair_table_width double;
    declare pair_depth double;
    declare pair_fluorescence_id int;
    declare pair_certificate int;
    declare pair_len_width_ratio double;
    declare pair_var int;
    declare pair_measure varchar(255);
    declare no_rows boolean;
    DECLARE pair_curs CURSOR FOR select item_id,carat,cut,clarity,color,certificate,table_width,total_depth,fluorescence_id,len_width_ratio,measurements,final_price from caratlane_enduser_v where item_id = if(find_in_set(item_id,itemarray),0,item_id) and clarity = first_clarity and color = first_color and fluorescence_id = first_fluorescence_id and certificate = first_certificate and carat >= first_carat - 0.02 and carat <= first_carat + 0.02 and final_price >=first_final_price-0.08*first_final_price and final_price <= first_final_price+0.08*first_final_price and total_depth >= first_depth-0.01*first_depth and total_depth <= first_depth+0.01*first_depth and table_width >= first_table_width-0.01*first_table_width and table_width <= first_table_width+0.01*first_table_width;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_rows = TRUE;
    open pair_curs;
    second:loop
    fetch pair_curs into pair_var,pair_carat,pair_cut,pair_clarity,pair_color,pair_certificate,pair_table_width,pair_depth,pair_fluorescence_id,pair_len_width_ratio,pair_measure,pair_final_price;
    IF no_rows THEN
                CLOSE pair_curs;
                LEAVE second;
            END IF;
    set pair_measure_height=substring(pair_measure,1,4);
    set pair_measure_width=substring(pair_measure,6,4);
    set pair_measure_depth=substring(pair_measure,11,14);
    select first_var,pair_var;
    insert into pair_find(first_id,second_id) values(first_var,pair_var);
    end loop second;
    end

    //php code
    php code : 
    <?php
    include_once '../app/Mage.php';
    umask(0);
    Mage::app();
    ini_set('error_reporting', E_ALL);
    ini_set('display_errors', 'On');
    $mysqli = new MySQLI('localhost','root','123456','caratlane_diamond_dev_test');
    $shape=1;
    $symmetry='Very Good';
    $polish = 'Very Good';
    $first_carat = 0;
    $last_carat = 100;
    $clarity = '1|2|3';
    $color = '1|2|3';
    $certificate = 1;
    $fluorescence_id = 1;
    $first_table_width = 0;
    $last_table_width = 8000;
    $first_total_depth = 0;
    $last_total_depth = 8000;
    $first_final_price = 0;
    $last_final_price = 1000000;
    $query=$mysqli->query("call viewalldiamondids(1,'Very Good','Very Good',0,100,1|2|3,1|2|3,1,1,0,8000,0,8000,0,1000000);");
    $result= $mysqli->query("SELECT first_id,second_id from pair_find");
    if($result->num_rows > 0) 
    {
        while($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
        {
            print_r($row);
        }
    }
    ?> 

Answer 1

From PHP you are only looking at the FIRST result set. From the command line, it simply spits out all result sets.

Each stored proc call you have above is generating a new unique result set (different fields possible etc)

To fix, you can move onto the next result set, if one exists:

Use odbc_next_result ( resource $result_id )

Alternatively, using Mysqli you can use these three commands:

$result = $connection->multi_query("select * from foo...."); $result = $connection->store_result(); $connection->next_result();

Using your code as an example, you can wrap your code that's outputting results inside a do..while loop like this:

$result= $mysqli->multi_query("SELECT first_id,second_id from pair_find");

do {
  $result = $mysqli->store_result();

  while($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
  {
     print_r($row);
  }
} while ($mysqli->next_result());

http://php.net/manual/en/function.odbc-next-result.php

http://php.net/manual/en/mysqli.next-result.php