List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:January 20 2011 4:59am
Subject:Re: running a mysql query inside a loop of another without a sync
View as plain text  
Hello Delan,

On 1/19/2011 21:54, Delan Azabani wrote:
> Hi all,
> I'm using MySQL with C in a CGI application. I hope this is the right
> list to ask for help.
> If I have this simplified code:
> MYSQL_RES *res;
> MYSQL_ROW row;
> mysql_query(mysql, "some select query");
> res = mysql_use_result(mysql);
> while (row = mysql_fetch_row(res)) {
>      MYSQL_RES *res2;
>      MYSQL_ROW row2;
>      mysql_query(mysql, "some other select query using an id from the
> first");
>      res2 = mysql_use_result(mysql);
>      /* ... */
>      mysql_free_result(res2);
> }
> mysql_free_result(res);
> Whenever I run the second query, inside the loop, I get the nasty
> 'commands out of sync' error. How can I run a select query while in a
> loop fetching rows from another select query? Or, do I have to fetch all
> the rows completely first and store them in memory (which wouldn't be
> very 'nice' to do)?
> If someone could help me with this problem, it would be greatly appreciated.

The mysql object you are using for your connection can only have one 
active query or result on it at a time. To have two sets of results 
working, you need a second independent connection to the MySQL server

This structure represents a handle to one database connection. It is 
used for almost all MySQL functions. You should not try to make a copy 
of a MYSQL structure. There is no guarantee that such a copy will be 
The first parameter should be the address of an existing MYSQL 
structure. Before calling mysql_real_connect() you must call 
mysql_init() to initialize the MYSQL structure. You can change a lot of 
connect options with the mysql_options() call. See Section, 
Two threads can't send a query to the MySQL server at the same time on 
the same connection. In particular, you have to ensure that between 
calls to mysql_query() and mysql_store_result() no other thread is using 
the same connection.

This same rule applies to attempting to process more than one query on 
the same connection. You must complete the first query before starting 
the second or you must open a separate connection to handle the second 

Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
running a mysql query inside a loop of another without a sync errorDelan Azabani20 Jan
  • Re: running a mysql query inside a loop of another without a syncerrorMySQL)20 Jan
  • Re: running a mysql query inside a loop of another without a sync errorMichael Dykman20 Jan