php - 从 PHP 中的 mySQL 存储过程获取返回值
问题描述
我有一个返回用户列表的存储过程,但是当我在 php 中调用此过程时,只需返回此消息: {"status":true,"data":[{"userID":0}]}
这是我的SP
CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllUsers`(in userID int )
BEGIN
select userID;
if (userID =0) then
select u.id, u.name, u.email from users u ;
else
select u.id, u.name, u.email from users u where u.id = userid;
end if;
END
这是我的 PHP 代码
function getAll(){
$userID = 0;
try{
$this->stmt = $this->pdo->prepare("call getAllUsers(:userID)");
$this->stmt->bindParam(':userID',$userID, PDO::PARAM_INT);
$this->stmt->execute();
$users = $this->stmt->fetchAll();
return count($users)==0 ? false : $users;
}
catch(Exception $ex)
{
echo 'Caught exception: ', $ex->getMessage(), "\n";
}
}
编辑:我使用 do/while 来获取所有记录,但现在我获得了有关字段规范的额外数据。这是我的新代码
function getAll(){
$userID = 0;
try{
$this->stmt = $this->pdo->prepare("call getAllUsers(:userID)");
$this->stmt->bindParam(':userID',$userID, PDO::PARAM_INT);
$this->stmt->execute();
do {
$users = $this->stmt->fetchAll();
var_dump($users);
} while ($this->stmt->nextRowset() && $this->stmt->columnCount());
return count($users)==0 ? false : $users;
}
catch(Exception $ex)
{
echo 'Caught exception: ', $ex->getMessage(), "\n";
}
}
这是结果:
array(1) { [0]=> array(1) { ["userID"]=> int(0) } } array(21) { [0]=> array(3) { ["id"]=> int(1) ["name"]=> string(8) "John Doe" ["email"]=> string(12) "john@doe.com" } [1]=> array(3) { ["id"]=> int(2) ["name"]=> string(8) "Jane Doe" ["email"]=> string(12) "jane@doe.com" } [2]=> array(3) { ["id"]=> int(3) ["name"]=> string(9) "Apple Doe" ["email"]=> string(13) "apple@doe.com" } [3]=> array(3) { ["id"]=> int(4) ["name"]=> string(8) "Beck Doe" ["email"]=> string(12) "beck@doe.com" } [4]=> array(3) { ["id"]=> int(5) ["name"]=> string(11) "Charlie Doe" ["email"]=> string(15) "charlie@doe.com" } [5]=> array(3) { ["id"]=> int(6) ["name"]=> string(11) "Charles Doe" ["email"]=> string(15) "charles@doe.com" } [6]=> array(3) { ["id"]=> int(7) ["name"]=> string(8) "Dion Doe" ["email"]=> string(12) "dion@doe.com" } [7]=> array(3) { ["id"]=> int(8) ["name"]=> string(7) "Dee Doe" ["email"]=> string(11) "dee@doe.com" } [8]=> array(3) { ["id"]=> int(9) ["name"]=> string(9) "Emily Doe" ["email"]=> string(13) "emily@doe.com" } [9]=> array(3) { ["id"]=> int(10) ["name"]=> string(9) "Ethan Doe" ["email"]=> string(13) "ethan@doe.com" } [10]=> array(3) { ["id"]=> int(11) ["name"]=> string(9) "Frank Doe" ["email"]=> string(13) "frank@doe.com" } [11]=> array(3) { ["id"]=> int(12) ["name"]=> string(8) "Gina Doe" ["email"]=> string(12) "gina@doe.com" } [12]=> array(3) { ["id"]=> int(13) ["name"]=> string(8) "Hela Doe" ["email"]=> string(12) "hela@doe.com" } [13]=> array(3) { ["id"]=> int(14) ["name"]=> string(10) "Hubert Doe" ["email"]=> string(14) "hubert@doe.com" } [14]=> array(3) { ["id"]=> int(15) ["name"]=> string(7) "Ivy Doe" ["email"]=> string(11) "ivy@doe.com" } [15]=> array(3) { ["id"]=> int(16) ["name"]=> string(10) "Ingrid Doe" ["email"]=> string(14) "ingrid@doe.com" } [16]=> array(3) { ["id"]=> int(17) ["name"]=> string(9) "James Doe" ["email"]=> string(13) "james@doe.com" } [17]=> array(3) { ["id"]=> int(18) ["name"]=> string(8) "Jace Doe" ["email"]=> string(12) "jace@doe.com" } [18]=> array(3) { ["id"]=> int(19) ["name"]=> string(8) "Kate Doe" ["email"]=> string(12) "kate@doe.com" } [19]=> array(3) { ["id"]=> int(20) ["name"]=> string(8) "Luke Doe" ["email"]=> string(12) "luke@doe.com" } [20]=> array(3) { ["id"]=> int(21) ["name"]=> string(4) "3213" ["email"]=> string(21) "heidary@saipacorp.com" } } {"status":true,"data":[{"id":1,"name":"John Doe","email":"john@doe.com"},{"id":2,"name":"Jane Doe","email":"jane@doe.com"},{"id":3,"name":"Apple Doe","email":"apple@doe.com"},{"id":4,"name":"Beck Doe","email":"beck@doe.com"},{"id":5,"name":"Charlie Doe","email":"charlie@doe.com"},{"id":6,"name":"Charles Doe","email":"charles@doe.com"},{"id":7,"name":"Dion Doe","email":"dion@doe.com"},{"id":8,"name":"Dee Doe","email":"dee@doe.com"},{"id":9,"name":"Emily Doe","email":"emily@doe.com"},{"id":10,"name":"Ethan Doe","email":"ethan@doe.com"},{"id":11,"name":"Frank Doe","email":"frank@doe.com"},{"id":12,"name":"Gina Doe","email":"gina@doe.com"},{"id":13,"name":"Hela Doe","email":"hela@doe.com"},{"id":14,"name":"Hubert Doe","email":"hubert@doe.com"},{"id":15,"name":"Ivy Doe","email":"ivy@doe.com"},{"id":16,"name":"Ingrid Doe","email":"ingrid@doe.com"},{"id":17,"name":"James Doe","email":"james@doe.com"},{"id":18,"name":"Jace Doe","email":"jace@doe.com"},{"id":19,"name":"Kate Doe","email":"kate@doe.com"},{"id":20,"name":"Luke Doe","email":"luke@doe.com"},
解决方案
我编辑的代码工作正常。我只需要评论“var_dump($users);”
推荐阅读
- scala - Spark File Streaming 获取文件名
- android - android 9上的Flutter WebView插件错误
- flutter - 在 Android studio 中遇到 Dart 分析服务器错误
- oracle - 在 SYSDATE 上 Oracle 中的间隔分区
- flutter - Flutter 中从 Http.get 获取 Null 数据
- python - 我该如何优化它以使其在更短的时间内运行?
- java - 如果数字 A 高于数字 B,则继续要求较低的用户输入数字 A
- mysql - GitHub Actions:如何在 Windows 或 macOS 上运行“服务”?
- spring - 无法解释的`kotlinx.coroutines.JobCancellationException: Job was cancelled` with kotlin coroutines and spring WebClient?
- php - Windows 10 和 PHP 的 file_get_contents() 响应权限被拒绝