php 使用php调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5641926/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
call stored procedure using php
提问by Pradyut Bhattacharya
i have written a basic stored procedure using mysql
我已经使用 mysql 编写了一个基本的存储过程
DELIMITER //
CREATE PROCEDURE `sp_sel_test`()
BEGIN
SELECT * FROM category c;
END//
DELIMITER ;
now i m calling it from php
现在我从 php 调用它
the php code is:
php代码是：
<?php
$txt = $_GET['id'];
$name = $_GET['name'];
$con = mysql_connect("localhost","four","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("fourthes_a", $con);
//$result = mysql_query("select * from new_c where name like %". $name ."% or c_name like %" . $name . "% order by name asc;");
$result = mysql_query("call sp_sel_test()");
if ($result === FALSE) {
die(mysql_error());
}
while($row = mysql_fetch_array($result))
{
echo $row['category_id'] . " " . $row['c_name'];
?>
<br />
<?php
}
mysql_close($con);
echo $txt;
?>
now its giving the error
现在它给出了错误
PROCEDURE fourthes_a.sp_sel_test can't return a result set in the given context
PROCEDUREfoures_a.sp_sel_test 无法返回给定上下文中的结果集
回答by Marc B
mysql_query()returns false when the query fails. You didn't check if your sproc query succeeded, so most likely you're passing that boolean FALSE to the fetch function, which is rightfully complaining.
mysql_query()查询失败时返回false。您没有检查您的 sproc 查询是否成功，因此很可能您将布尔值 FALSE 传递给 fetch 函数，这是理所当然的抱怨。
Rewrite your code like this, as a bare mininum, for proper error handling:
像这样重写你的代码，作为一个简单的最小值，以进行正确的错误处理：
$res = mysql_query('call sp_sel_test()');
if ($res === FALSE) {
die(mysql_error());
}
Never ever assume a query succeeded. Even if the SQL syntax is perfect, there's far too many other reasons for a query to fail to NOT check if it worked.
永远不要假设查询成功。即使 SQL 语法是完美的，也有太多其他原因导致查询无法检查它是否有效。
回答by Ashwini Dhekane
You need to set client flags while connecting for using stored procedures with php. Use this:
您需要在连接时设置客户端标志，以便在 php 中使用存储过程。用这个：
mysql_connect($this->h,$this->u,$this->p,false,65536);
See MySQL Client Flagsfor more details. PHP MySQL does not allow you to run multiple statements in single query. To overcome this you must tell PHP to allow such queries by setting CLIENT_MULTI_STATEMENTS flag in your connection.
有关更多详细信息，请参阅MySQL 客户端标志。PHP MySQL 不允许您在单个查询中运行多个语句。为了克服这个问题，您必须通过在连接中设置 CLIENT_MULTI_STATEMENTS 标志来告诉 PHP 允许此类查询。
回答by Pradyut Bhattacharya
I know last answer was a year ago, but...
我知道最后一个答案是一年前，但是......
CREATE PROCEDURE sp_sel_test(OUT yourscalarvariable INT/TEXT...)
创建程序sp_sel_test（输出你的标量变量 INT/TEXT...）
Statements that return a result set cannot be used within a stored function. This includes SELECT statements that do not use INTO to fetch column values into variables, SHOW statements, and other statements such as EXPLAIN. For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs (ER_SP_NO_RETSET_IN_FUNC). For statements that can be determined only at runtime to return a result set, a PROCEDURE %s can't return a result set in the given context error occurs (ER_SP_BADSELECT).
不能在存储函数中使用返回结果集的语句。这包括不使用 INTO 将列值提取到变量中的 SELECT 语句、SHOW 语句和其他语句（例如 EXPLAIN）。对于可以在函数定义时确定返回结果集的语句，会发生不允许从函数返回结果集错误 (ER_SP_NO_RETSET_IN_FUNC)。对于只能在运行时确定返回结果集的语句，PROCEDURE %s 无法返回给定上下文中的结果集会发生错误 (ER_SP_BADSELECT)。
So, your select shoould be like this:
所以，你的选择应该是这样的：
SELECT * FROM category **INTO** c;
http://www.cs.duke.edu/csl/docs/mysql-refman/stored-procedures.html
http://www.cs.duke.edu/csl/docs/mysql-refman/stored-procedures.html
