1

i am trying to get record from database via stored procedure but i am getting error of
"command out of sync can not run now"
here is my stored procedure code. any buddy could help me i had waste too much time

DELIMITER $$
create procedure get_industrialchildIDs(in id int)
BEGIN
 DECLARE total_rows int;
 SET total_rows=0;
create TEMPORARY table IF NOT EXISTS temp_table1(CategoryChildId int(11));
truncate table temp_table1;
insert into temp_table1 SELECT CategoryChildId FROM tblcategoryparentchildassociations WHERE CategoryParentId=id;
SELECT count(CategoryChildId) into total_rows from temp_table1;
 create TEMPORARY  table IF NOT EXISTS temp_table (CategoryChildId int(11));
 truncate table temp_table;
create TEMPORARY table IF NOT EXISTS temp_table2(CategoryChildId int(11));
insert into temp_table select id from tblmastercategories where id  in (select CategoryChildId from tblcategoryparentchildassociations where CategoryParentId = id) AND flag_Industr_sub = 1;
WHILE total_rows <> 0 DO
   insert into temp_table select id from tblmastercategories where id in (select CategoryChildId from tblcategoryparentchildassociations where CategoryParentId in (SELECT CategoryChildId FROM temp_table1)) AND flag_Industr_sub = 1;
    delete from temp_table2 where 1=1;
    insert into temp_table2 SELECT CategoryChildId FROM temp_table1;
    delete from temp_table1 where 1=1; 
    insert into temp_table1 select CategoryChildId from tblcategoryparentchildassociations where CategoryParentId in (SELECT CategoryChildId FROM temp_table2);
    SELECT count(CategoryChildId) into total_rows from temp_table1;
 END WHILE;
SELECT CategoryChildId FROM temp_table;
END $$
DELIMITER ;
sajid
  • 83
  • 8
  • Possible duplicate of [mysqli giving "Commands out of sync" error - why?](https://stackoverflow.com/questions/3632075/mysqli-giving-commands-out-of-sync-error-why) – Solarflare Jul 18 '18 at 06:28
  • Do you have php involved at all? – P.Salmon Jul 18 '18 at 06:37
  • sorry for late @P.Salmon its just mysql not including php its procedure – sajid Jul 18 '18 at 06:55
  • dear @Solarflare all those are talking about ` mysqli_store_result() ` or `mysqli_free_result() ` but where i have to use these commands and what is the best way to write this query. – sajid Jul 18 '18 at 07:07
  • You are calling this procedure somewhere. The problem lies there: the error happens when you do not read the result (of the query before you call that procedure) before you execute the next query. We would need to see the code (and/or the name of the tool) you are using to execute the procedure. – Solarflare Jul 18 '18 at 07:14
  • @Solarflare i am directly executing this procedure from sql panel or when i will call to this procedure from php there is no one other statement. :( – sajid Jul 18 '18 at 07:35
  • Please include the code you "will call to this procedure from php". Somewhere there is an error. The error message means: "The client that is connected to me did something in the wrong order". Without seeing your (php) code, we cannot tell you what exactly you did wrong, as a place where *not* to look for the reason of that error is the procedure itself. If the sql panel is giving this error too (it should not, as the developers are usually aware of this problem and handle it correctly), you should tell us which kind of panel you are using (and what you do exactly to trigger it). – Solarflare Jul 18 '18 at 08:00
  • sorry @Solarflare it was lunch time that is why i was not here. i have xamp server and mysql database. i wrote the call statement in console of mysql database this is [error image](https://drive.google.com/open?id=1D6GVg1F5yQTmXSSQx2OdZOXQBXnr51hN) – sajid Jul 18 '18 at 09:16
  • Well, this seems to be a bug in phpmyadmin, it seems that it does not check if your procedure returns a resultset, I would ask/report it there (although it's possible that they may have stated somewhere that they only support `select`, `update` and `insert`), and/or make your question here a phpmyadmin-question (add it in the title, explain it in the question itself and add the tag), maybe someone knows about this problem specific to phpmyadmin. Anyway, you should be able to execute the procedure from a *real* console (start mysql outside of phpmyadmin) aswell as from any php code you write. – Solarflare Jul 18 '18 at 10:12
  • well thanks a lot senior @Solarflare to give me a lot of time i used here sub queries to optimize my query but its just working when i run these commands from sql panel and its not working with loop inside stored procedure so as i wrote my previous queries where i used joins. for now i just change it form sub queries to join now its working. – sajid Jul 18 '18 at 10:51

0 Answers0