Calling more than 1 stored procedure in 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.

mysql_*() API are deprecated. Use either the MySQLi or PDO_MySQL extension instead. Deprecated mysql_*() — जलजनक
– जलजनक, Commented Feb 14, 2013 at 7:18
You might do well with making sure your question have an accepted answer. If you found the answer on your own, then share it with everyone and mark it as accepted. If you found the answer in a response by someone else, then mark it so. This will help others in the future pinpoint a solution to a problem similar to yours. — Manatax
– Manatax, Commented Feb 22, 2013 at 5:46

Manatax · Accepted Answer · 2013-02-14 07:51:53Z

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

edited Feb 14, 2013 at 7:51

answered Feb 14, 2013 at 7:32

Manatax

4,2436 gold badges32 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Calling more than 1 stored procedure in php + mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related