为什么需要在每个存储过程之前包括连接文件

debugcn 发表于 Dev

阿努

我有一个php文件，它正在调用一些文件stored procedures。

include "../../commonFilesForAll/db.php";
$tagQueryQcWaiting = "CALL qcWaitingQuery()";
$tagQueryQcWaitingExecute = mysqli_query($conn, $tagQueryQcWaiting);
$qcWaiting = mysqli_num_rows($tagQueryQcWaitingExecute);

$tagQueryQcFailed = "CALL qcFailedQuery()";
$tagQueryQcFailedExecute = mysqli_query($conn, $tagQueryQcFailed);
$qcFailed = mysqli_num_rows($tagQueryQcFailedExecute);

$tagQueryQcAssigned = "CALL qcAssignedQuery()";
$tagQueryQcAssignedExecute = mysqli_query($conn, $tagQueryQcAssigned);
$qcAssigned = mysqli_num_rows($tagQueryQcAssignedExecute);

但是当我给出上述方法时，它不起作用。我必须在如下所示的每个查询之前包含连接文件，以便其如下所示工作

include "../../commonFilesForAll/db.php";
$tagQueryQcWaiting = "CALL qcWaitingQuery()";
$tagQueryQcWaitingExecute = mysqli_query($conn, $tagQueryQcWaiting);
$qcWaiting = mysqli_num_rows($tagQueryQcWaitingExecute);

include "../../commonFilesForAll/db.php";
$tagQueryQcFailed = "CALL qcFailedQuery()";
$tagQueryQcFailedExecute = mysqli_query($conn, $tagQueryQcFailed);
$qcFailed = mysqli_num_rows($tagQueryQcFailedExecute);

include "../../commonFilesForAll/db.php";  
$tagQueryQcAssigned = "CALL qcAssignedQuery()";
$tagQueryQcAssignedExecute = mysqli_query($conn, $tagQueryQcAssigned);
$qcAssigned = mysqli_num_rows($tagQueryQcAssignedExecute);

我的存储过程示例如下 qcWaitingQuery

BEGIN
  SELECT * FROM plannertags WHERE (`status` = '0' OR `status` = '2') 
  AND currentStage = '12' 
  AND assignedTo = '0' 
  AND handoverStatus = '0' 
  AND failedStatus = '0' 
  ORDER BY deliveryDate ASC;
END

qcFailedQuery

BEGIN
  SELECT * FROM plannertags t JOIN failedTable n on t.srNumber = n.plannerTagsSrNumber
  WHERE (t.status = 0 OR t.status = 2) 
  AND t.currentStage = '12' 
  AND t.assignedTo = '0' 
  AND t.handoverStatus = '0' 
  AND n.failedDepartment = '12' 
  AND n.status = '0' 
  AND n.latestTag='1' 
  ORDER BY t.deliveryDate ASC;
END

qcAssignedQuery

BEGIN
  SELECT *FROM plannertags t JOIN qctable n on t.srNumber = n.plannerTagsSrNumber
  WHERE (t.status = 0 OR t.status = 2) 
  AND t.currentStage = '12' 
  AND t.assignedTo = '12' 
  AND t.handoverStatus = '0' 
  AND n.failedStatus = '0' 
  AND n.qcHold != '1'
  ORDER BY t.deliveryDate ASC;
END

我的db.php档案如下

$servername = "111.11.11.111";
$username = "111";
$password = "111111";
$database = "111";

$conn = mysqli_connect($servername, $username, $password, $database);

if (!$conn) {
   die("Connection failed: " . mysqli_connect_error());
}

有人知道为什么会这样吗？

达曼

您无需每次都打开连接。您的错误是您正在调用存储过程，但未获取所有结果。SP具有隐藏的行为，它们总是在其中包含的SELECT语句之上返回结果。这意味着您需要正确获取两个结果，否则将出现不同步错误。

在对存储过程的每次调用之后，获取第二个结果，它应该可以工作。

include "../../commonFilesForAll/db.php";
$tagQueryQcWaiting = "CALL qcWaitingQuery()";
$tagQueryQcWaitingExecute = mysqli_query($conn, $tagQueryQcWaiting);
$qcWaiting = mysqli_num_rows($tagQueryQcWaitingExecute);

mysqli_next_result($conn); // fetch the empty result of SP call

如果正确配置了mysqli，则会看到正确的不同步错误。您的连接脚本不正确。您必须始终启用错误报告。替换为：

$servername = "111.11.11.111";
$username = "111";
$password = "111111";
$database = "111";

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = mysqli_connect($servername, $username, $password, $database);

确保删除该if语句。您还应该设置正确的连接字符集。请阅读手册以获取更多信息。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。