Commands out of sync you can t run this command now

Question

I am trying to execute my PHP code  which calls two MySQL queries via mysqli  and get the error  Commands out of sync  you can t run this command now    Here is the code I am using   lt  php  con   mysqli connect  localhost    user    password    db    if    con        echo  Can t connect to MySQL Server  Errorcode   s n   Mysqli connect error        exit     con- gt query  SET NAMES  utf8      brand   o    countQuery    SELECT ARTICLE NO FROM AUCTIONS WHERE upper ARTICLE NAME  LIKE         if   numRecords    con- gt prepare  countQuery          numRecords- gt bind param  s    brand        numRecords- gt execute         data    con- gt query  countQuery  or die print r  con- gt error         rowcount    data- gt num rows       rows   getRowsByArticleSearch  test    Auctions              last   ceil  rowcount  page rows      else    print r  con- gt error     foreach   rows as  row         pk    row  ARTICLE NO        echo   lt tr gt       n       echo   lt td gt  lt a href     onclick  updateByPk   Layer2           pk         gt    row  USERNAME     lt  a gt  lt  td gt       n       echo   lt td gt  lt a href     onclick  updateByPk   Layer2           pk         gt    row  shortDate     lt  a gt  lt  td gt       n       echo   lt td gt  lt a href     onclick  deleterec   Layer2           pk         gt DELETE RECORD lt  a gt  lt  td gt       n       echo   lt  tr gt       n     function getRowsByArticleSearch  searchString   table   max         con   mysqli connect  localhost    user    password    db         recordsQuery    SELECT ARTICLE NO  USERNAME  ACCESSSTARTS  ARTICLE NAME  date format str to date ACCESSSTARTS    d  m  Y  k  i  s      d  m  Y    AS shortDate FROM AUCTIONS WHERE upper ARTICLE NAME  LIKE       ORDER BY str to date ACCESSSTARTS    d  m  Y  k  i  s       max      if   getRecords    con- gt prepare  recordsQuery              getRecords- gt bind param  s    searchString            getRecords- gt execute             getRecords- gt bind result  ARTICLE NO   USERNAME   ACCESSSTARTS   ARTICLE NAME   shortDate           while   getRecords- gt fetch                   result    con- gt query  recordsQuery                rows   array                while  row    result- gt fetch assoc                       rows      row                            return  rows                      I have tried reading up on this  but I am unsure of what to do  I have read about store result and free result  however these have made no difference when using them  I am unsure at exactly which point this error is being caused  and would like to know why it is being caused  and how to fix it   Going by my debug statements  the first if loop for countQuery is not even being entered  because of an error in my sql syntax near near          However if I just select   instead of trying to limit based on a LIKE clause  I still get the command out of sync error

User · Accepted Answer

You can t have two simultaneous queries because mysqli uses unbuffered queries by default  for prepared statements  it s the opposite for vanilla mysql query   You can either fetch the first one into an array and loop through that  or tell mysqli to buffer the queries  using  stmt- gt store result      See here for details

User · Answer

I ran into this error using Doctrine DBAL QueryBuilder   I created a query with QueryBuilder that uses column subselects  also created with QueryBuilder  The subselects were only created via  queryBuilder- gt getSQL   and not executed  The error happened on creating the second subselect  By provisionally executing each subselect with  queryBuilder- gt execute   before using  queryBuilder- gt getSQL    everything worked  It is as if the connection  queryBuilder- gt connection remains in an invalid state for creating a new SQL before executing the currently prepared SQL  despite the new QueryBuilder instance on each subselect   My solution was to write the subselects without QueryBuilder

User · Answer

I had today the same problem  but only when working with a stored procedure  This make the query behave like a multi query  so you need to  consume  other results available before make another query   while  this- gt mysql- gt more results          this- gt mysql- gt next result         this- gt mysql- gt use result

User · Answer

This is an old question  but none of the posted answers worked in my case   I found that in my case I had selects and updates on a table in my stored procedure   the same table had an update trigger which was being triggered and senging the procedure into an infinite loop   Once the bug was found the error went away

User · Answer

The problem is the MySQL client C library  which most MySQL APIs are built on  The problem is that the C library doesn t support simultaneous execution of queries  so all APIs built on top of that also do not  Even if you use unbuffered queries  This is one reason why the asynchronous MySQL API was written  It communicates directly with the MySQL server using TCP and the wire-protocol does support simultaneous queries   Your solution is to either modify the algorithm so you don t need to have both in progress at once  or change them to use buffered queries  which is probably one of the original reasons for their existence in the C library  the other is to provide a kind of cursor

User · Answer

I use CodeIgniter   One server OK     this one probably older      Anyway using    this- gt db- gt reconnect      Fixed it

User · Answer

I call this function every time before using  mysqli- query Works with stored procedures as well   function clearStoredResults        global  mysqli       do            if   res    mysqli- gt store result                  res- gt free                         while   mysqli- gt more results    amp  amp   mysqli- gt next result

User · Answer

to solve this problem you have to store result data before use it    numRecords- gt execute      numRecords- gt store result      that s all

User · Answer

I am using ODBC  and this fix works for me  ODBC -  tab System DSN -  double click to configure my data source -  details -  tab Cursors -  Uncheck  Don t cache results of forward-only cursors  -  click Ok

User · Answer

Check to see if you are typing all the parameters correctly  It throws the same error if the amount of parameters defined and then passed to the function are different

User · Answer

I think the problem is that you re making a new connection in the function  and then not closing it at the end   Why don t you try passing in the existing connection and re-using it   Another possibility is that you re returning out of the middle of a while loop fetching   You never complete that outer fetch

User · Answer

This is not related to the original question  but i had the same error-message and this thread is the first hit in Google and it took me a while to figure out what the Problem was  so it May be of use for others   i m NOT using mysqli  still using mysql connect i had some simple querys  but ONE query caused all other querys to fail within the same Connection   I use mysql 5 7 and php 5 6 i had a table with the data-Type  JSON   obviously  my php-version did not recognize the return value from mysql  php just did not know what to do with the JSON-Format because the built-in mysql-module was too old  at least i think    for now i changed the JSON-Field-Type to Text  as for now i don t need the native mysql JSON-functionality  and everything works fine

User · Answer

I solved this problem in my C application - here s how I did it    Quoting from mysql forums      This error results when you terminate your query with a semicolon delimiter inside the application  While it is required to terminate a query with a semicolon delimiter when executing it from the command line or in the query browser  remove the delimiter from the query inside your application   After running my query and dealing with the results  C API  mysql store result     I iterate over any further potentially pending results that occurs via multiple SQL statement execution such as two or more select statements  back to back without dealing with the results    The fact is that my procedures don t return multiple results but the database doesn t know that until I execute   C API  mysql next result     I do this in a loop  for good measure  until it returns  non-zero  That s when the current connection handler knows it s okay to execute another query  I cache my handlers to minimize connection overhead     This is the loop I use   for   mysql next result mysql handler     0         do nothing        I don t know PHP but I m sure it has something similar

User · Answer

My problem was that I was using first prepare statement and then I was using mysqli query on the same page and was getting the error  Commands out of sync  you can t run this command now   The error  was only then when I was using the code that had prepare statement    What I did was to close the question  and it worked   mysqli stmt close  stmt    My code   get category php  here using prepare statement         lt  php       global  connection       cat to delete      mysqli real escape string  connection    GET  get            sql    SELECT category name FROM categories WHERE category id                stmt   mysqli stmt init  connection        if   mysqli stmt prepare  stmt   sql             SESSION  error      Error at preaparing for deleting query  mysqli stmt error  stmt      amp  redirect     error php         mysqli stmt bind param  stmt   s    cat to delete        if   mysqli stmt execute  stmt             SESSION  error      ERror at executing delete category   mysqli stmt error  stmt   amp              redirect     error php          mysqli stmt bind result  stmt   cat name        if   mysqli stmt fetch  stmt             mysqli stmt error  stmt                mysqli stmt free result  stmt       mysqli stmt close  stmt     admin get category body php  here mysqli            lt  php          if  isset   GET  get     amp  amp   empty   GET  get                            include  intodb edit category php                       if  check method  get    amp  amp  isset   GET  delete                            require  intodb delete category php                       if  check method  get    amp  amp  isset   GET  get                            require  intodb get category php                         gt            lt  --            start  cat body          -- gt            lt div     class  columns is-mobile is-centered is-vcentered  gt               lt div class  column is-half  gt                   lt div   class  section has-background-white-ter box   gt                         lt div class  has-text-centered column       gt                           lt h4 class  title is-4  gt Ctegories lt  h4 gt                       lt  div gt                        lt div class  column    gt                             lt  php if  check method  get    amp  amp  isset   GET  get        gt                           lt form action    method  post  gt                               lt  php   else    gt                               lt form action  intodb add category php  method  post  gt                                   lt  php     gt                                    lt label class  label  for  admin add category bar  gt Add Category lt  label gt                                   lt div class  field is-grouped  gt                                        lt p class  control is-expanded  gt                                            lt  php if  check method  get    amp  amp  isset   GET  get        gt                                                lt input id  admin add category bar  name  admin add category bar edit  class  input  type  text  placeholder  Add Your Category Here  value   lt  php echo  cat name    gt   gt                                                lt  php                                                 gt                                           lt  php   else    gt                                               lt input id  admin add category bar  name  admin add category bar  class  input  type  text  placeholder  Add Your Category Here  gt                                            lt  php     gt                                       lt  p gt                                       lt p class  control  gt                                           lt input type  submit  name  admin add category submit  class  button is-info my fucking hover right arrow  value  Add Category  gt                                       lt  p gt                                   lt  div gt                               lt  form gt                                 lt div class    gt                                    lt  --            start  body for all posts inside admin          -- gt                                     lt table class  table is-bordered  gt                                       lt thead gt                                       lt tr gt                                           lt th gt  lt p gt Category Name lt  p gt  lt  th gt                                           lt th gt  lt p gt Edit lt  p gt  lt  th gt                                           lt th gt  lt p gt Delete lt  p gt  lt  th gt                                       lt  tr gt                                       lt  thead gt                                        lt  php                                      global  connection                                       sql    SELECT   FROM categories                                           result   mysqli query  connection   sql                                       if    result                                            echo mysqli error  connection                                                                             while  row   mysqli fetch assoc  result                                                                                   gt                                           lt tbody gt                                           lt tr gt                                               lt td gt  lt p gt  lt  php echo  row  category name    gt  lt  p gt  lt  td gt                                               lt td gt                                                   lt a href  admin category php get  lt  php echo  row  category id      gt   class  button is-info my fucking hover right arrow   gt Edit lt  a gt                                                lt  td gt                                                lt td gt                                                   lt a href  admin category php delete  lt  php echo  row  category id      gt   class  button is-danger my fucking hover right arrow   gt Delete lt  a gt                                                lt  td gt                                           lt  tr gt                                             lt  tbody gt                                       lt  php    gt                                   lt  table gt                                    lt  --           end  body for all posts inside admin             -- gt                                   lt  div gt                        lt  div gt                   lt  div gt                 lt  div gt            lt  div gt           lt  div gt    Something that just crossed my mind  I am also again adding connection variable via global  connection   So I think basically a whole new set of query system is being started after ending of prepare statement with mysqli stmt close  stmt   and also I am adding these files and other stuff via include

User · Answer

Create  two  connections  use both separately   mysqli   new mysqli  localhost    Admin    dilhdk    SMS      conn   new mysqli  localhost    Admin    dilhdk    SMS

User · Answer

Just for reference i had this problem mixing both multi query and query in the same code   connection- gt multi query  query        connection- gt query  otherQuery    Which for what i read had unconsumed results pending  therefore causing in the mentioned error  I solved it with a loop consuming all the results of the multi query   connection- gt multi query  query   while   connection- gt next result         lt --- solves the problem      connection- gt query  otherQuery    In my case all the queries in the multi query where inserts so i had no interest in the results themselves

User · Answer

Here is what was MY PROBLEM     The param binding was  dynamic  so I had a variable that sets the params of the data in order to use bind param  So that variable was wrong but instead of throwing an error like  wrong param data  it says  out of sync bla bla bla  so I was confused

User · Answer

Another cause   store result   cannot be called twice   For instance  in the following code  Error 5 is printed    lt  php   db   new mysqli  localhost    something    something    something      stmt    db- gt stmt init    if   stmt- gt error  printf  Error 1    s n    stmt- gt error     stmt- gt prepare  select 1    if   stmt- gt error  printf  Error 2    s n    stmt- gt error     stmt- gt execute    if   stmt- gt error  printf  Error 3    s n    stmt- gt error     stmt- gt store result    if   stmt- gt error  printf  Error 4    s n    stmt- gt error     stmt- gt store result    if   stmt- gt error  printf  Error 5    s n    stmt- gt error      This may not be relevant to the original sample code  but it can be relevant to people seeking answers to this error

User · Answer

To clear the referencing memory  and run the next MYSQL fetch If you either use Buffered or Unbuffered result set for fetching data  first you must simply clear the fetched data from the memory  once you have fetched all the data  As you can t execute another MYSQL procedure on the same connection until you clear the fetched memory  Add this below function right end of your script  so it will solve the problem  numRecords- gt close    or  numRecords- gt free       This clears the referencing memory  and will be ready for the next MYSQL fetch  Reference from the PHP documentation

User · Answer

I often hit this error and it s always when I run a stored procedure that I have been debugging in phpmyadmin or SQL Workbench  I am working in php connecting with mysqli   The error results from the fact that the debugging involves inserting SELECT statements at strategic points in the code to tell me the state of variables  etc  Running a stored procedure that produces multiple results sets in these client environments is fine  but it produces the  quot Commands out of sync quot  error when called from php  The solution is always to comment-out or remove the debugging selects so the procedure has only one result set

User · Answer

Once you used   stmt- gt execute      You MAY close it to use another query    stmt- gt close      This problem was hunting me for hours  Hopefully  it will fix yours

[php] Commands out of sync; you can't run this command now

Examples related to php

Examples related to sql

Examples related to mysql

Examples related to mysqli