使用php返回MySQL存储过程结果集 [英] Return MySQL stored procedure result set using php

问题描述

我是 php 的新手，无法从存储过程中获取结果集，如果可能的话，我希望得到程序格式的答案.

存储过程如下:

DROP PROCEDURE IF EXISTS getJobs;分隔符 $$CREATE PROCEDURE getJobs(IN inputName VARCHAR(50))开始SELECT JobNo FROM jobs WHERE PersonnelName= inputName;结束 $$分隔符；

在 MySQL Workbench 中返回 30539

我想出了以下 php 代码.

';print_r($result);echo '</pre>';//循环结果集并回显while ($row = mysqli_fetch_array($result)){//我希望输出结果的命令回声条目".$行[0]."<br>";//调试以检查结果回声条目".$行."<br>";}$conn->close();?>
运行时提供以下输出:
mysqli_result 对象([current_field] =>0[field_count] =>1[长度] =>[num_rows] =>0[类型] =>1)入口条目数组
如果我使用不返回任何结果的输入，mysqli_result 对象就会消失，这似乎很接近，但实际上并不包含数据.
提前致谢.
解决方案
我看起来不是很优雅，但我想出了以下内容，足​​以让我继续前进:
close();?>
I'm a new to php and am having trouble getting a result set from a stored procedure, I'd like the answer in a procedural format if possible.

The stored procedure is as follows:
DROP PROCEDURE IF EXISTS getJobs;
DELIMITER $$
CREATE PROCEDURE getJobs(IN inputName VARCHAR(50))
BEGIN 
SELECT JobNo FROM jobs WHERE PersonnelName= inputName;
END $$
DELIMITER ;
In MySQL Workbench this returns 30539

I've come up with the following php code.
<?php
//imports the connection
require "conn.php";

$name = "chrisf";

$call = mysqli_prepare($conn, 'CALL getJobs(?)');
mysqli_stmt_bind_param($call, 's', $name);
mysqli_stmt_execute($call);
$result = mysqli_use_result($conn);

//debug to check the result
echo '<pre>'; print_r($result); echo '</pre>';
//loop the result set and echo
while ($row = mysqli_fetch_array($result)){   
//the command I expect to output the result
  echo "Entry" . $row[0] . "<br>"; 
//debug to check the result
 echo "Entry" .  $row . "<br>"; 
}

$conn->close();
?>
When run this provides the following output:
mysqli_result Object
(
[current_field] => 0
[field_count] => 1
[lengths] => 
[num_rows] => 0
[type] => 1
)

 Entry
 EntryArray
As the mysqli_result Object disappears if I use an input which returns no results, this seems to be close but it doesn't actually contain the data.

Thanks in advance.
解决方案
I doesn't seem very elegant, but I've come up with the following, which satisfies me enough to move on:
<?php
//imports the connection
require "conn.php";

//builds the MySQL stored procedure all
$name = "chrisf";
$input = "CALL getEngineersJob('".$name."')";

//executes the store procedure
$result = mysqli_query($conn,$input) or die("Query fail: " . mysqli_error());

//loop through the output and echo
 while ($row = mysqli_fetch_array($result)){   
   echo $row[0] . "<br>"; 
 }

//free resources
mysqli_free_result($result);
$conn->close();
?>


                        
这篇关于使用php返回MySQL存储过程结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！