在php + mysql中调用多个存储过程

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `sp_update_NotifyTrainerStatus_in_notification_table`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_update_NotifyTrainerStatus_in_notification_table`(IN email VARCHAR(50), IN id BIGINT)
BEGIN

 SELECT @employee_id := `Employee_Id` FROM `employee_profile` WHERE `Email_Id` = email;

 UPDATE notification_status_table SET NotifyTrainerStatus=1 WHERE Employee_Id    =@employee_id AND Training_Id = id;

END$$

DELIMITER ;

this is my mysql query and i have to run this when a id=6 . i have my php code like below

这是我的mysql查询,我必须在id = 6时运行它。我有我的PHP代码如下

if ( $id == 6 ) {
    // echo $trainingid;
    // echo "Inside id=6";
    mysql_query("call update_notify_trainer_in_status_table(" . $trainingid  . ")");
    $n = count( $to );
    // echo "count : ".$n;
    // foreach( $to as $values ) {
    for( $i = 0; $i < $n; $i++ ) {
        // echo $values;
        $trainer_mail = $to[$i];
        echo $trainer_mail;
        //echo $trainingid;
        mysql_query("call  sp_update_NotifyTrainerStatus_in_notification_table('".$trainer_mail."' , " . $trainingid . ")")  or die( mysql_error() );
    }
}

My problem is this query runs fine for first $trainer_mail and it doesn't run for second $trainer_mail. any suggestions. Thanks in advance.

我的问题是这个查询运行正常的第一个$ trainer_mail并且它没有运行第二个$ trainer_mail。有什么建议。提前致谢。

1 个解决方案

#1

You need to clean the connection after each call to a store procedure. Try with this:

每次调用存储过程后都需要清除连接。试试这个:

function mysqli_clean_connection($dbc) {
    while(mysqli_more_results($dbc)) {
        if (mysqli_next_result($dbc)) {
            $result = mysqli_use_result($dbc);
            if ($result!=NULL) {
                mysqli_free_result($result);
            }
        }
    }
}

I use mysqli, but you can adapt it

我使用mysqli,但你可以适应它

#1