MySQL query 1 stored procedures, display 2 query results, how to display the 2 results in PHP

Source: Internet
Author: User
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);
  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.