MySQL query 1 stored procedures, display 2 query results, how to show in PHP 2 results
MySQL query 1 stored procedures, display 2 query results, how to show in PHP 2 results
------------------------------
MySQL Stored procedures:
-------------------------------
Begin
DECLARE strSQL varchar (3000);
declare beginnum int;
DECLARE strcount_s varchar (2000);
/* Determine the number of pages, if empty or 0 or null default first page */
if (PageIndex is null or PageIndex = "or PageIndex = 0)
Then
Set PageIndex = 1;
End If;
/* Determine the number of displays per page, if it is empty or 0 or null default first */
if (splitcount=0 or splitcount= "or splitcount is null)
Then
Set splitcount = 1;
End If;
/* Query all data sets according to the criteria */
Set @beginnum = (PageIndex-1) * SPLITCOUNT;
Set @strSQL = concat (' Select ', Numtype, ' from ', TableName, ' where ', Wheretype, ', by, ' limit ', @beginnum, ', ' , Splitcount);
PREPARE stmt from @strSQL;
Execute stmt;
Deallocate PREPARE stmt;
/* Query the total data quantity according to the conditions */
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 results 1:deallocate PREPARE stmt; All query data for output
MySQL results 2:deallocate PREPARE select_rowscount; Total number of output queries
----------------------------------------
PHP Stored Procedure Classes
----------------------------------------
/**
* Stored Procedure Operations
*
* @param $produceAndParams stored procedure name and parameters
* Format is producename (PARA1, para2 ...),
* If the argument is a character, enclose the single quotation mark
* @return $resultnum The number of rows that were successfully affected by the return operation
* @throws Exception Stored Procedure exception
*/
public static function Runproduce ($produceAndParams)
{
Global $_config;
if (!mysql_query ("SET NAMES". $_config[' coding '], Self::$_wdbconn)) {
throw new Exception ("Set CharSet". $_config[' coding '). " Failure: ". Mysql_error ());
}
if (!mysql_query (' call '. $produceAndParams. '; ', Self::$_wdbconn)) {
throw new Exception ("Call stored procedure failed:". Mysql_error ());
}
$resultNum = Mysql_affected_rows (self::$_wdbconn);
return $resultNum;
}
----------------------------------
How to write in PHP, can show the 2 results of stored procedure query
------Solution--------------------
I collect the information, you can refer to:
PHP Code
PHP call MySQL Stored procedure method collection type one: Call the method with input, output type parameter 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; Output variable type from output from stored procedure two: Call a method with multiple output types and multiple input type parameters 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. '
'; Outputs the variable from the stored procedure echo ' User ID: '. $uId; Get user ID Type three: Call a method with a return result set 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 '];//output DataSet }} catch (Exception $e) {echo $e; Type four: Calls a method with multiple result sets returned (currently only implemented by 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);