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

Question

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

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.

Answer 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