Why need to include connection file before every stored procedure

Question

I have a php file whereby it is calling some 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);

But when I gave the above way, it is not working. I have to include the connection file before every query as below in order for it to work as shown below

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);

Sample of my stored procedure as below 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

My db.php file as follows

$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());
}

Does anyone know why is it that way?

score 1 · Accepted Answer · answered Feb 01 '21 at 14:32

You don't need to open the connection each time. Your mistake is that you are calling a stored procedure but you are not fetchin all results. SPs have a hidden behaviour that they always return a result on top of what SELECT statements you have inside of them. This means that you have two results which you need to fetch properly otherwise you will get out of synch error.

After each call to stored procedure fetch the second result and it should work.

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

You would see the proper out of synch error if you had the mysqli configured properly. Your connection script is incorrect. You must always enable error reporting. Replace it with:

$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);

Make sure to remove the if statement. You should also set the correct connection charset. Please read the manual for more information.

Thank you Dharman. The reason why I didn't give any error reporting is because of I don't want user see the errors. I think with the above error reporting, sometimes user will be able to see even the username and password — Anu, Feb 02 '21 at 05:45
@Anu You are mixing two things together. Error reporting should always be fully enabled. However, when the site goes live then the setting `display_errors` should be switched off. See [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Feb 02 '21 at 13:37
okay. its clear now. I am trying to figure out where to set `display_errors` from the link you mentioned. — Anu, Feb 03 '21 at 05:58

Why need to include connection file before every stored procedure

1 Answers1