Heim >Backend-Entwicklung >PHP-Tutorial >mysql查询的1个存储过程,显示2个查询结果,如何在PHP里把2个结果显示出来

mysql查询的1个存储过程,显示2个查询结果,如何在PHP里把2个结果显示出来

WBOY
WBOYOriginal
2016-06-13 10:25:10821Durchsuche

mysql查询的1个存储过程,显示2个查询结果,怎么在PHP里把2个结果显示出来
mysql查询的1个存储过程,显示2个查询结果,怎么在PHP里把2个结果显示出来
------------------------------
mysql存储过程:
-------------------------------
begin
  declare strSQL varchar(3000);
  declare beginnum int;
  declare strCount_s varchar(2000);  
  /*判断页数,如果是空或者0或者null默认第一页*/
  if(PageIndex is null or PageIndex = '' or PageIndex = 0)
  then
set PageIndex = 1;
  end if;
/*判断每页显示数,如果是空或者0或者null默认第一条*/
  if (SplitCount=0 or SplitCount='' or SplitCount is null)
then
set SplitCount = 1;
end if; 
  /*根据条件,查询所有数据集*/
  set @beginnum = (PageIndex -1) * SplitCount; 
  set @strSQL = concat('select ', numtype, ' from ', tablename, ' where ', wheretype,' ' , OrderBy, ' limit ',@beginnum, ',', SplitCount);  
   
  PREPARE stmt from @strSQL;
  execute stmt;  
  DEALLOCATE PREPARE stmt;
  /*根据条件,查询总数据数量*/
  set @strCount_s=concat('select count(1) as countnum from ', tablename,' where ', wheretype);
  prepare select_rowscount from @strCount_s;
  execute select_rowscount;
  DEALLOCATE PREPARE select_rowscount;

end

MYSQL结果1: DEALLOCATE PREPARE stmt; 输出的所有查询数据
MYSQL结果2: DEALLOCATE PREPARE select_rowscount; 输出查询总数量
----------------------------------------

PHP存储过程类

----------------------------------------
/**
* 存储过程操作
*
* @param $produceAndParams 存储过程名称及参数
* 格式为ProduceName(para1, para2.....),
* 如果参数是字符请加单引号
* @return $resultnum 返回操作成功影响的行数
* @throws Exception 存储过程异常
*/
public static function RunProduce($produceAndParams)
{
global $_config;
if (!mysql_query("SET NAMES ".$_config['coding'], self::$_wdbConn)) {
throw new Exception("设置字符集".$_config['coding']."失败:".mysql_error());
}
if(!mysql_query('call '.$produceAndParams.';', self::$_wdbConn)) {
throw new Exception("调用存储过程失败:".mysql_error());
}
$resultNum = mysql_affected_rows(self::$_wdbConn);
return $resultNum;
}

----------------------------------

怎么在PHP里写,能把存储过程查询的2个结果显示出来



------解决方案--------------------
我搜集的资料,你可以参考一下:


PHP code
php调用MySQL存储过程方法集合类型一:调用带输入、输出类型参数的方法view plainprint?$returnValue = '';  try {      mysql_query ( "set @Return" );      $spname = 'P__Test_GetInfo1';      mysql_query ( "call $spname(@Return, '{$userId}', '{$pwd}')" ) or die ( "[$spname]Query failed:" . mysql_error () );      $result_return = mysql_query ( "select @Return" );      $row_return = mysql_fetch_row ( $result_return );      $returnValue = $row_return [0];  } catch ( Exception $e ) {      echo $e;  }  echo $returnValue; //输出来自存储过程中输出的变量  类型二:调用带多个输出类型和多个输入类型参数的方法view plainprint?$userId = 0;  try{      mysql_query("set @Message");      mysql_query("set @Id");      mysql_query("call P__Test_Login(@Message, @Id, '{$userId}', '{$pwd}')", $conn) or die("Query failed:".mysql_error());      $result_mess = mysql_query("select @Message");      $result_uid = mysql_query("select @Id");      $row_mess = mysql_fetch_row($result_mess);      $row_uid = mysql_fetch_row($result_uid);      $Proc_Error = $row_mess[0];      $uId = $row_uid[0];  }  catch( Exception $e )  {     echo $e;  }  echo 'proc return message:'$Proc_Error.'<br>'; //输出来自存储过程中输出的变量  echo 'User id:'.$uId; //获取用户id  类型三:调用带返回结果集的方法view plainprint?try {      $spname = 'P__Test_GetData';      $query = mysql_query ( "call $spname()", $conn ) or die ( "[$spname]Query failed:".mysql_error() );      while ( $row = mysql_fetch_array ( $query ) ) {          echo $row ['ProvinceID'].'::'.$row ['ProvinceName']; //输出数据集      }        } catch ( Exception $e ) {      echo $e;  }  类型四:调用带返回多个结果集的方法(目前只能通过mysqli来实现~~~~~)view plainprint?//PHP  $rows = array ();    $db = new mysqli($server,$user,$psd,$dbname);    if (mysqli_connect_errno()){        $this->message('Can not connect to MySQL server');    }    $db->query("SET NAMES UTF8");    $db->query("SET @Message");  if($db->real_query("call P__Test_GetData2(@Message)")){        do{            if($result = $db->store_result()){                while ($row = $result->fetch_assoc()){                    array_push($rows, $row);                }                $result->close();            }        }while($db->next_result());    }    $db->close();   print_r($rows);<div class="clear">
                 
              
              
        
            </div>
Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn