通过 PHP 调用 MySQL 存储过程时出错 [英] Error calling MySQL stored procedure through PHP
问题描述
我正在尝试从 MySQL 调用存储过程并取回两个 OUT 参数(@eset 和 @leng).我想将这两个参数回显给 JavaScript,其中我有一个 XMLHttpRequest 等待结果.
I'm trying to call a stored procedure from MySQL and get back the two OUT parameters (@eset and @leng). I would like to echo out these two parameters back to JavaScript where I have an XMLHttpRequest waiting for the results.
我收到此错误:
Strict standards: mysqli::next_result(): There is no next result set.
这是我的代码:
<?php
//get the q parameter from URL
$q=$_GET["q"];
$eset= "";
$length= 0;
// Opens a connection to a MySQL server
$db= new mysqli('localhost', 'db_name', 'pass');
if (!$db) { die('Not connected : ' . mysql_error());}
// Set the active MySQL database
$db_selected = $db->select_db('db_name');
if (!$db_selected) {
die ('Can\'t use db : ' . mysql_error());
}
// Select all the rows in the markers table
$db->multi_query( "CALL mst2($q, @eset, @leng);SELECT @eset as eset;SELECT @leng as length" );
$db->next_result(); // flush the null RS from the call
$eset=$db->store_result(); // get the RS containing the id
//echo $eset->fetch_object()->eset, "\n";
$length= $db->store_result();
//echo $length->fetch_object()->leng, "\n";
$response= $eset.$length;
//$eset->free();
//$length->free();
//$response=str_shuffle($q);
//output the response
echo $response;
?>
推荐答案
我假设你的存储过程的第一个参数是 VARCHAR,所以第一个问题是你传递了 $q
查询中没有引号的变量.应该是这样的:
I'm assuming the first argument of your stored procedure is VARCHAR, so the first problem is that you are passing the $q
variable without quotes in the query. It should be like this:
$db->multi_query("CALL mst2('$q', @eset, @leng); SELECT @eset as eset; SELECT @leng as length");
此外,您不需要进行两次 SELECT 调用,只需执行一次:
Also, you don't need to make two SELECT calls, do it only once:
SELECT @eset AS eset, @leng AS leng;
不用说,永远不应该信任用户输入.您应该使用准备好的语句:
Needless to say that user inputs should never be trusted. You should use prepared statements:
if (($stmt = $db->prepare("CALL mst2(?, @eset, @leng)"))) {
$stmt->bind_param("s", $q);
$stmt->execute();
$stmt->close();
if (($res = $db->query("SELECT @eset AS eset, @leng AS leng"))) {
list($eset, $leng) = $res->fetch_array();
$result = $eset.$length;
echo $result;
$res->free();
}
}
这篇关于通过 PHP 调用 MySQL 存储过程时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!