Jump to content

how to execute a transaction inside stored procedure in php to insert data from array

shaddf

By shaddf
in PHP Coding Help

 Share

Recommended Posts

shaddf Regular Member

shaddf

Posted
Posted (edited)

I have this stored procedure below.how can i excute itt using php to insert data from an array like this one:

Array
(
    [Japanese] => Array
        (
            [hot] => wasabi
            [salty] => soy sauce
        [sweety] => soy rdsauce
        [bitter] => pepper sauce
        )

    [Chinese] => Array
        (
            [hot] => wasabi
            [salty] => soy sauce
        [sweety] => soy rdsauce_one
        [bitter] => pepper sauce3
        )

    [indian] => Array
        (
            [hot] => wasabi
            [salty] => soy sauce
        [sweety] => soy rdsauce
        [bitter] => pepper sauce4
        )

    [mexican] => Array
        (
            [hot] => soanzo
            [salty] => soy sauce1
        [sweety] => soy sauceeur
        [bitter] => pepper sauce_sausage
        )

    [russian] => Array
        (
            [hot] => soanzo
            [salty] => soy sauce1
        [sweety] => soy sauceeur
        [bitter] => pepper sauce_pork

        )

    [ganda] => Array
        (
            [hot] => soanzo
            [salty] => soy sauce1
        [sweety] => soy sauce_beef
        [bitter] => luwombo_chicken

        )

)
DROP PROCEDURE IF EXISTS sp_addnewstratplandetails $$
CREATE PROCEDURE sp_addnewstratplandetails(IN in_stratprio_id int,IN in_Plalt varchar(255),in in_Out varchar(255),in in_Actes varchar(255),in in_performars varchar(255),in in_tar varchar(255),in in_state int)
MODIFIES SQL DATA
BEGIN
  DECLARE Result1 int;
  DECLARE pcnt1 int;
  DECLARE o_id varchar(255);DECLARE in_Outrids varchar(255);
  DECLARE in_actids varchar(255);DECLARE in_pids varchar(255);
DECLARE r_id varchar(255);
DECLARE in_PLANrids varchar(255);
  DECLARE pcnt int;
 DECLARE a_id varchar(255);
 DECLARE pcntI int;
DECLARE p_id varchar(255);DECLARE in_tids varchar(255);
 DECLARE pcntt int;
 DECLARE pchk int;
 DECLARE lo_id int;
 DECLARE l_AO_id int;
DECLARE l_perfo_id int;
 DECLARE l_tar_id int;
 DECLARE t_id varchar(255);
    DECLARE in_Pllt_id  varchar(20);
 
    DECLARE l_out_id  varchar(20);
    DECLARE l_Actes_id  varchar(20);
    DECLARE l_pers_id  varchar(20);
    DECLARE l_tar_id  varchar(20);
DECLARE exit handler for sqlexception
  BEGIN
/*   ERROR*/
       
  ROLLBACK;
 SELECT CONCAT('Error occurred – Record  not saved!')as "Result";
END;

DECLARE exit handler for sqlwarning
 BEGIN
/* WARNING*/
 ROLLBACK;
SELECT CONCAT('Error:Record not saved already Exists! ') as "Result";
END;

START TRANSACTION;
/*check if plad result exists*/
SELECT count(*) into Result1  FROM _Plantstbl WHERE Plannult=in_Plalt and EXISTS (SELECT * FROM _stratbl WHERE      _Plantstbl.PlannedResult_id=_stratbl.Plannult_id and stratprio_id=in_stratprio_id);
IF Result1=0 && in_state=0 THEN
    SELECT count(*) into pcnt from _Plantstbl where Plannult_id like concat(in_stratprio_id,'%');
    SET r_id=pcnt+1;
      SET in_PLANrids=concat(in_stratprio_id,'.',r_id);
            INSERT INTO _Plantstbl(Plannult_id,Plannsult)  VALUES(in_PLANrids,in_Plalt);
ELSE
UPDATE _Plantstbl SET Plannsult=in_Plalt WHERE PlannedResult=in_Plalt;
END IF;
     SELECT Plannult_id into in_Pllt_id FROM _Plantstbl WHERE Plannsult=in_Plalt ;    

SELECT count(*) into lo_id  FROM Out_tbl WHERE outcome=in_Out ;
IF lo_id=0 THEN
 
    SELECT count(*) into pcnt1 from Out_tbl where out_id like concat(in_Pllt_id,'.','%');
    SET o_id=pcnt1+1;
      SET in_Outrids=concat(in_Pllt_id,'.',o_id);
    INSERT INTO Out_tbl(out_id,outme)  VALUES(in_Outrids,in_Out);

ELSE

UPDATE Out_tbl SET outcome=in_Out WHERE outcome=in_Out ;
END IF;
SELECT count(*) into l_AO_id FROM _Plastbl WHERE Actes=in_Actes;
IF l_AO_id=0 THEN
SELECT out_id into l_out_id  FROM Out_tbl WHERE outcome=in_Out ;
    SELECT count(*) into pcnt from _Plastbl where Actes_id  like concat(l_out_id,'.','%');
    SET a_id=pcnt+1;
        SET in_actids=concat(l_out_id,'.',a_id);
        INSERT INTO _Plastbl(Actes_id,Actes)  VALUES(in_actids,in_Actes);
ELSE
    UPDATE _Plastbl SET Actes=in_Actes WHERE Actes=in_Actes ;

END IF;
SELECT count(*) into l_perfo_id  FROM _Pertbl WHERE perfors=in_performars;
IF l_perfo_id=0 THEN


              SELECT count(*) into pcntI from _Pertbl where Pind_id  like concat(l_out_id,'.','%');
        SET p_id=pcntI+1;
              SET in_pids=concat(l_out_id,'.',p_id);
             INSERT INTO _Pertbl(Pind_id,perfors)  VALUES(in_pids,in_performars);
ELSE
UPDATE _Pertbl SET perfors=in_performars WHERE perfors=in_performars;

END IF;
SELECT count(*) into l_tar_id FROM _Ttbl WHERE tar=in_tar;
IF l_tar_id=0 THEN

            SELECT count(*) into pcntt from _Ttbl where tar_id  like concat(l_out_id,'.','%');
            SET t_id=pcntt+1;
                SET in_tids=concat(l_out_id,'.',t_id);
                    INSERT INTO _Ttbl(tar_id,tar)  VALUES(in_tids,in_tar);
ELSE
UPDATE _Ttbl SET tar=in_tar WHERE tar=in_tar;

END IF;

COMMIT;    
SELECT Actes_id into l_Actes_id FROM _Plastbl WHERE Actes=in_Actes;
SELECT Pind_id into l_pers_id  FROM _Pertbl WHERE perfors=in_performars;
SELECT tar_id into l_tar_id FROM _Ttbl WHERE tar=in_tar;

INSERT INTO _stratbl(stratprio_id,Plesult_id,out_id,Actes_id,Pind_id,tar_id )  VALUES(in_stratprio_id,in_Pllt_id,l_out_id,l_Actes_id,l_pers_id ,l_tar_id);
SELECT 'success ';


END$$
DELIMITER ;
Edited by requinix
use [code] tags when posting code
Link to comment
Share on other sites
requinix Prolific Member

requinix

Posted
Posted (edited)

Use code tags when posting code (or stuff like that PHP output). I've done it for you this time, please do it yourself next time.

 

Assuming you're using mysqli, take a look at the documentation for examples on how to call stored procedures. If you aren't, the process and code will likely be very similar.

Edited by requinix
Link to comment
Share on other sites
shaddf Regular Member

shaddf

Posted
  • Author
Posted

Use code tags when posting code (or stuff like that PHP output). I've done it for you this time, please do it yourself next time.

 

Assuming you're using mysqli, take a look at the documentation for examples on how to call stored procedures. If you aren't, the process and code will likely be very similar.

i have tried this so far but no good:

	include("config.php");

	$strat_sql="call sp_addnewetails(?,?,?,?,?,?)";
	$insert_t_stmt=$mysqli->prepare($strat_sql)or die($mysqli->error);
		   #associate variables with the input parameters
	$insertt_stmt->bind_param("isssss",$my_prioid,$my_planid,$my_outcome_id,$my_Activities_id,$my_performanceIndicator_id,$my_target_id); #i=integer
   		//aggregate the records to save
		$my_prioid =$_POST['strat_priorities'];
		$my_planid=stripslashes($plan);
	   for ($i = 0, $num_specials = count($arr); $i < $num_specials; $i++) {
   		 for ($m = 0, $num_sub = count($arr[$i]); $m < $num_sub; $m++) {
	     //save record
		$my_id=stripslashes($arr[$i][$m]);
		$my_Act_id=stripslashes($arr[$i][$m]);
		$my_p_id=stripslashes($arr[$i][$m]);
		$my_t_id=stripslashes($arr[$i][$m]);
		   #Execute the statement
		$insert_t_stmt->execute(  ) or die ($insert_t_stmt->error);
		$insert_t_stmt->bind_result($Entryerr);
		while ($insert_strat_stmt->fetch(  )) {
			 $error12=$Entryerr;
			if(substr($error12,0, 4)=="succ"){$suc=$error12;}
		}	


		 }//inner for
	    }
   $insert_t_stmt->close(  );
   $mysqli->close(  );
Link to comment
Share on other sites
shaddf Regular Member

shaddf

Posted
  • Author
Posted (edited)

I can't tell if you actually assigned values to all those variables.

 

You have something that outputs an error in case of failure. Did it output an error? What did it say?

why is it that  it works for one record.that is when the for loop is out it does work well. but when i try to do it multiple times it does not do anything:it does not insert any record .And iam concrete sure that the data is there in the array as seen from the reply through ajax

iuhioh[oijoi  rec1

ii[oioij rec2

 oijoi]op]po rec3

 iuuiho[iuho[i rec4

Is it because of the transaction that has manytables to insert into data ,why??

    $strat_sql="call sp_addnewetails(?,?,?,?,?,?)";

    $insert_t_stmt=$mysqli->prepare($strat_sql)or die($mysqli->error);

         #associate variables with the input parameters

    $insertt_stmt->bind_param("isssss",$my_prioid,$my_planid,$my_outcome_id,$my_Activities_id,$my_performanceIndicator_id,$my_target_id); #i=integer

        //aggregate the records to save

        $my_prioid =$_POST['strat_priorities'];

        $my_planid=stripslashes($plan);

 

     //save record

        $my_id="you";//stripslashes($arr[$i][$m]);

        $my_Act_id="me";//stripslashes($arr[$i][$m]);

        $my_p_id="him";//stripslashes($arr[$i][$m]);

        $my_t_id="her";//stripslashes($arr[$i][$m]);

         #Execute the statement

        $insert_t_stmt->execute( ) or die ($insert_t_stmt->error);

        $insert_t_stmt->bind_result($Entryerr);

        while ($insert_strat_stmt->fetch( )) {

             $error12=$Entryerr;

            if(substr($error12,0, 4)=="succ"){$suc=$error12;}

        }    

 

 

 

$insert_t_stmt->close( );

$mysqli->close( );

Edited by shaddf
Link to comment
Share on other sites
shaddf Regular Member

shaddf

Posted
  • Author
Posted (edited)

I can't tell if you actually assigned values to all those variables.

 

You have something that outputs an error in case of failure. Did it output an error? What did it say

it has managed to save with the code below but it is not saving the second record of the array.why is it not looping through

for ($i = 0, $num_specials = count($arr); $i < $num_specials; $i++) {

    include("config.php");

    $strat_sql="call sp_addnewetails(?,?,?,?,?,?)";
    $insert_t_stmt=$mysqli->prepare($strat_sql)or die($mysqli->error);
         #associate variables with the input parameters
    $insertt_stmt->bind_param("isssss",$my_prioid,$my_planid,$my_outcome_id,$my_Activities_id,$my_performanceIndicator_id,$my_target_id); #i=integer
        //aggregate the records to save
        $my_prioid =$_POST['strat_priorities'];
        $my_planid=stripslashes($plan);
   
         for ($m = 0, $num_sub = count($arr[$i]); $m < $num_sub; $m++) {
     //save record
        $my_id=stripslashes($arr[$i][$m]);
        $my_Act_id=stripslashes($arr[$i][$m]);
        $my_p_id=stripslashes($arr[$i][$m]);
        $my_t_id=stripslashes($arr[$i][$m]);
         #Execute the statement
        $insert_t_stmt->execute( ) or die ($insert_t_stmt->error);
        $insert_t_stmt->bind_result($Entryerr);
        while ($insert_strat_stmt->fetch( )) {
             $error12=$Entryerr;
            if(substr($error12,0, 4)=="succ"){$suc=$error12;}
        }    


         }//inner for
    
$insert_t_stmt->close( );
$mysqli->close( );

}//end outer for
Edited by shaddf
Link to comment
Share on other sites
shaddf Regular Member

shaddf

Posted
  • Author
Posted

I just noticed some mistakes in the code you posted that would (should) have caused your script to crash. Please code your complete code without any modifications.

DROP PROCEDURE IF EXISTS sp_addnewstratplandetails $$
CREATE PROCEDURE sp_addnewstratplandetails(IN in_stratprio_id int,IN in_Plalt varchar(255),in in_Out varchar(255),in in_Actes varchar(255),in in_performars varchar(255),in in_tar varchar(255),in in_state int)
MODIFIES SQL DATA
BEGIN
  DECLARE Result1 int;
  DECLARE pcnt1 int;
  DECLARE o_id varchar(255);DECLARE in_Outrids varchar(255);
  DECLARE in_actids varchar(255);DECLARE in_pids varchar(255);
DECLARE r_id varchar(255);
DECLARE in_PLANrids varchar(255);
  DECLARE pcnt int;
 DECLARE a_id varchar(255);
 DECLARE pcntI int;
DECLARE p_id varchar(255);DECLARE in_tids varchar(255);
 DECLARE pcntt int;
 DECLARE pchk int;
 DECLARE lo_id int;
 DECLARE l_AO_id int;
DECLARE l_perfo_id int;
 DECLARE l_tar_id int;
 DECLARE t_id varchar(255);
    DECLARE in_Pllt_id  varchar(20);
 
    DECLARE l_out_id  varchar(20);
    DECLARE l_Actes_id  varchar(20);
    DECLARE l_pers_id  varchar(20);
    DECLARE l_tar_id  varchar(20);
DECLARE exit handler for sqlexception
  BEGIN
/*   ERROR*/
       
  ROLLBACK;
 SELECT CONCAT('Error occurred – Record  not saved!')as "Result";
END;

DECLARE exit handler for sqlwarning
 BEGIN
/* WARNING*/
 ROLLBACK;
SELECT CONCAT('Error:Record not saved already Exists! ') as "Result";
END;

START TRANSACTION;
/*check if plad result exists*/
SELECT count(*) into Result1  FROM _Plantstbl WHERE Plannult=in_Plalt and EXISTS (SELECT * FROM _stratbl WHERE      _Plantstbl.PlannedResult_id=_stratbl.Plannult_id and stratprio_id=in_stratprio_id);
IF Result1=0 && in_state=0 THEN
    SELECT count(*) into pcnt from _Plantstbl where Plannult_id like concat(in_stratprio_id,'%');
    SET r_id=pcnt+1;
      SET in_PLANrids=concat(in_stratprio_id,'.',r_id);
            INSERT INTO _Plantstbl(Plannult_id,Plannsult)  VALUES(in_PLANrids,in_Plalt);
ELSE
UPDATE _Plantstbl SET Plannsult=in_Plalt WHERE PlannedResult=in_Plalt;
END IF;
     SELECT Plannult_id into in_Pllt_id FROM _Plantstbl WHERE Plannsult=in_Plalt ;    

SELECT count(*) into lo_id  FROM Out_tbl WHERE outcome=in_Out ;
IF lo_id=0 THEN
 
    SELECT count(*) into pcnt1 from Out_tbl where out_id like concat(in_Pllt_id,'.','%');
    SET o_id=pcnt1+1;
      SET in_Outrids=concat(in_Pllt_id,'.',o_id);
    INSERT INTO Out_tbl(out_id,outme)  VALUES(in_Outrids,in_Out);

ELSE

UPDATE Out_tbl SET outcome=in_Out WHERE outcome=in_Out ;
END IF;
SELECT count(*) into l_AO_id FROM _Plastbl WHERE Actes=in_Actes;
IF l_AO_id=0 THEN
SELECT out_id into l_out_id  FROM Out_tbl WHERE outcome=in_Out ;
    SELECT count(*) into pcnt from _Plastbl where Actes_id  like concat(l_out_id,'.','%');
    SET a_id=pcnt+1;
        SET in_actids=concat(l_out_id,'.',a_id);
        INSERT INTO _Plastbl(Actes_id,Actes)  VALUES(in_actids,in_Actes);
ELSE
    UPDATE _Plastbl SET Actes=in_Actes WHERE Actes=in_Actes ;

END IF;
SELECT count(*) into l_perfo_id  FROM _Pertbl WHERE perfors=in_performars;
IF l_perfo_id=0 THEN


              SELECT count(*) into pcntI from _Pertbl where Pind_id  like concat(l_out_id,'.','%');
        SET p_id=pcntI+1;
              SET in_pids=concat(l_out_id,'.',p_id);
             INSERT INTO _Pertbl(Pind_id,perfors)  VALUES(in_pids,in_performars);
ELSE
UPDATE _Pertbl SET perfors=in_performars WHERE perfors=in_performars;

END IF;
SELECT count(*) into l_tar_id FROM _Ttbl WHERE tar=in_tar;
IF l_tar_id=0 THEN

            SELECT count(*) into pcntt from _Ttbl where tar_id  like concat(l_out_id,'.','%');
            SET t_id=pcntt+1;
                SET in_tids=concat(l_out_id,'.',t_id);
                    INSERT INTO _Ttbl(tar_id,tar)  VALUES(in_tids,in_tar);
ELSE
UPDATE _Ttbl SET tar=in_tar WHERE tar=in_tar;

END IF;

COMMIT;    
SELECT Actes_id into l_Actes_id FROM _Plastbl WHERE Actes=in_Actes;
SELECT Pind_id into l_pers_id  FROM _Pertbl WHERE perfors=in_performars;
SELECT tar_id into l_tar_id FROM _Ttbl WHERE tar=in_tar;

INSERT INTO _stratbl(stratprio_id,Plesult_id,out_id,Actes_id,Pind_id,tar_id )  VALUES(in_stratprio_id,in_Pllt_id,l_out_id,l_Actes_id,l_pers_id ,l_tar_id);
SELECT 'success ';


END$$
DELIMITER ;

that is the stored procedure code.

and this is the   php:

Array
(
    [Japanese] => Array
        (
            [hot] => wasabi
            [salty] => soy sauce
        [sweety] => soy rdsauce
        [bitter] => pepper sauce
        )

    [Chinese] => Array
        (
            [hot] => wasabi
            [salty] => soy sauce
        [sweety] => soy rdsauce_one
        [bitter] => pepper sauce3
        )

    [indian] => Array
        (
            [hot] => wasabi
            [salty] => soy sauce
        [sweety] => soy rdsauce
        [bitter] => pepper sauce4
        )

    [mexican] => Array
        (
            [hot] => soanzo
            [salty] => soy sauce1
        [sweety] => soy sauceeur
        [bitter] => pepper sauce_sausage
        )

    [russian] => Array
        (
            [hot] => soanzo
            [salty] => soy sauce1
        [sweety] => soy sauceeur
        [bitter] => pepper sauce_pork

        )

    [ganda] => Array
        (
            [hot] => soanzo
            [salty] => soy sauce1
        [sweety] => soy sauce_beef
        [bitter] => luwombo_chicken

        )

)

DROP PROCEDURE IF EXISTS sp_addnewstratplandetails $$
CREATE PROCEDURE sp_addnewstratplandetails(IN in_stratprio_id int,IN in_Plalt varchar(255),in in_Out varchar(255),in in_Actes varchar(255),in in_performars varchar(255),in in_tar varchar(255),in in_state int)
MODIFIES SQL DATA
BEGIN
  DECLARE Result1 int;
  DECLARE pcnt1 int;
  DECLARE o_id varchar(255);DECLARE in_Outrids varchar(255);
  DECLARE in_actids varchar(255);DECLARE in_pids varchar(255);
DECLARE r_id varchar(255);
DECLARE in_PLANrids varchar(255);
  DECLARE pcnt int;
 DECLARE a_id varchar(255);
 DECLARE pcntI int;
DECLARE p_id varchar(255);DECLARE in_tids varchar(255);
 DECLARE pcntt int;
 DECLARE pchk int;
 DECLARE lo_id int;
 DECLARE l_AO_id int;
DECLARE l_perfo_id int;
 DECLARE l_tar_id int;
 DECLARE t_id varchar(255);
    DECLARE in_Pllt_id  varchar(20);
 
    DECLARE l_out_id  varchar(20);
    DECLARE l_Actes_id  varchar(20);
    DECLARE l_pers_id  varchar(20);
    DECLARE l_tar_id  varchar(20);
DECLARE exit handler for sqlexception
  BEGIN
/*   ERROR*/
       
  ROLLBACK;
 SELECT CONCAT('Error occurred – Record  not saved!')as "Result";
END;

DECLARE exit handler for sqlwarning
 BEGIN
/* WARNING*/
 ROLLBACK;
SELECT CONCAT('Error:Record not saved already Exists! ') as "Result";
END;

START TRANSACTION;
/*check if plad result exists*/
SELECT count(*) into Result1  FROM _Plantstbl WHERE Plannult=in_Plalt and EXISTS (SELECT * FROM _stratbl WHERE      _Plantstbl.PlannedResult_id=_stratbl.Plannult_id and stratprio_id=in_stratprio_id);
IF Result1=0 && in_state=0 THEN
    SELECT count(*) into pcnt from _Plantstbl where Plannult_id like concat(in_stratprio_id,'%');
    SET r_id=pcnt+1;
      SET in_PLANrids=concat(in_stratprio_id,'.',r_id);
            INSERT INTO _Plantstbl(Plannult_id,Plannsult)  VALUES(in_PLANrids,in_Plalt);
ELSE
UPDATE _Plantstbl SET Plannsult=in_Plalt WHERE PlannedResult=in_Plalt;
END IF;
     SELECT Plannult_id into in_Pllt_id FROM _Plantstbl WHERE Plannsult=in_Plalt ;    

SELECT count(*) into lo_id  FROM Out_tbl WHERE outcome=in_Out ;
IF lo_id=0 THEN
 
    SELECT count(*) into pcnt1 from Out_tbl where out_id like concat(in_Pllt_id,'.','%');
    SET o_id=pcnt1+1;
      SET in_Outrids=concat(in_Pllt_id,'.',o_id);
    INSERT INTO Out_tbl(out_id,outme)  VALUES(in_Outrids,in_Out);

ELSE

UPDATE Out_tbl SET outcome=in_Out WHERE outcome=in_Out ;
END IF;
SELECT count(*) into l_AO_id FROM _Plastbl WHERE Actes=in_Actes;
IF l_AO_id=0 THEN
SELECT out_id into l_out_id  FROM Out_tbl WHERE outcome=in_Out ;
    SELECT count(*) into pcnt from _Plastbl where Actes_id  like concat(l_out_id,'.','%');
    SET a_id=pcnt+1;
        SET in_actids=concat(l_out_id,'.',a_id);
        INSERT INTO _Plastbl(Actes_id,Actes)  VALUES(in_actids,in_Actes);
ELSE
    UPDATE _Plastbl SET Actes=in_Actes WHERE Actes=in_Actes ;

END IF;
SELECT count(*) into l_perfo_id  FROM _Pertbl WHERE perfors=in_performars;
IF l_perfo_id=0 THEN


              SELECT count(*) into pcntI from _Pertbl where Pind_id  like concat(l_out_id,'.','%');
        SET p_id=pcntI+1;
              SET in_pids=concat(l_out_id,'.',p_id);
             INSERT INTO _Pertbl(Pind_id,perfors)  VALUES(in_pids,in_performars);
ELSE
UPDATE _Pertbl SET perfors=in_performars WHERE perfors=in_performars;

where are thos errors you have just found

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.