shaddf Posted August 24, 2015 Share Posted August 24, 2015 (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 August 24, 2015 by requinix use [code] tags when posting code Quote Link to comment Share on other sites More sharing options...
requinix Posted August 24, 2015 Share Posted August 24, 2015 (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 August 24, 2015 by requinix Quote Link to comment Share on other sites More sharing options...
shaddf Posted August 26, 2015 Author Share Posted August 26, 2015 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( ); Quote Link to comment Share on other sites More sharing options...
requinix Posted August 26, 2015 Share Posted August 26, 2015 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? Quote Link to comment Share on other sites More sharing options...
shaddf Posted August 27, 2015 Author Share Posted August 27, 2015 (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 August 27, 2015 by shaddf Quote Link to comment Share on other sites More sharing options...
requinix Posted August 27, 2015 Share Posted August 27, 2015 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. Quote Link to comment Share on other sites More sharing options...
shaddf Posted August 28, 2015 Author Share Posted August 28, 2015 (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 August 28, 2015 by shaddf Quote Link to comment Share on other sites More sharing options...
shaddf Posted August 28, 2015 Author Share Posted August 28, 2015 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.