3

I have 2 queries that I have to run:

  1. A complex SELECT query in a stored procedure to identify which records I need to modify.
  2. An UPDATE using the record id's from the first that modifies values in the same table.

I try to get the results from the first to close so I can run the second, but the only way I can do so without getting this error :

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

is to close the mysqli connection and re-create it.

Here's the essence of my code:

//  The $sql is to run a stored procedure.  I omitted that code
//  because it's very lengthy, but it works perfectly every time.
//  The stored procedure is basically a recursive SELECT query to
//  pull hierarchical data from a self-referencing data set.

$res = mysqli_query($mysqli, $sql);

//  Build a comma-delimited list of id numbers
$ids = "";
$i = 0;
$qty = mysqli_num_rows($res);
while ($recs = mysqli_fetch_array($res)) {
  $i += 1;
  $ids .= $recs["data_id"];
  if ($i < $qty) $ids .= ", ";
}
mysqli_free_result($res);

//  Close connection to MySQL (Remove this & connect statement below causes it to fail)
mysqli_close($mysqli);

//  Re-open connection to MySQL (Remove this & close statement above causes it to fail)
$mysqli = connect_DB();

$sql = "UPDATE data SET data_val = data_val + ".$val." WHERE data_id IN (".$ids.")";

$res = mysqli_query($mysqli, $sql);

I've tried every suggestion I found (including more than a dozen from stackoverflow posts), but nothing works. What am I missing? It sure seems unreasonable to have to do that, especially considering that I can run other queries back-to-back by using a "free_result" statement.

stj
  • 9,037
  • 19
  • 33
Big_Al_Tx
  • 954
  • 9
  • 14
  • Is there a reason you don't combine them into a single query? `UPDATE ... WHERE data_id IN (SELECT ...)`? – Barmar Dec 23 '14 at 02:46
  • 1
    The code you posted closes the connection in between. Please post the code that fails, not the code that works. – Barmar Dec 23 '14 at 02:47
  • Simply removing the close and connect statements makes the code fail. – Big_Al_Tx Dec 23 '14 at 02:53
  • I can't combine them because eventually I have to incorporate more complex logic into the UPDATE query to account for other criteria on which records & columns should be modified or not. – Big_Al_Tx Dec 23 '14 at 02:55
  • Did you try the suggestion of calling `mysqli_store_result()` from http://stackoverflow.com/questions/3632075/mysqli-giving-commands-out-of-sync-error-why? – Barmar Dec 23 '14 at 02:58
  • Yes, and I get the same error, plus "Warning: mysqli_store_result() expects parameter 1 to be mysqli, object given in include.php". – Big_Al_Tx Dec 23 '14 at 03:08
  • Sounds like you gave the wrong argument to `mysqli_store_result`. It should be `$mysqli`, not `$res`. – Barmar Dec 23 '14 at 03:20
  • Just to be sure I tried it both ways -- same "Commands out of sync" error either way. – Big_Al_Tx Dec 23 '14 at 03:28
  • I'm really not sure why you're getting that error in the first place. Everything I've read about it says that it happens when you start a new query before you've read all the results of the first query. But you're not doing the second query until after the first `while (mysqli_fetch_array())` loop. – Barmar Dec 23 '14 at 03:33
  • maybe try wrapping it all in an "if" statement like the PHP manual shows; `if($res = mysqli_query($mysqli, $sql)){ ... mysqli_free_result($res); }` not sure but may be worth a try. http://php.net/manual/en/mysqli.query.php – Joe T Dec 23 '14 at 04:22
  • @JoeT - Tried it, same result. – Big_Al_Tx Dec 23 '14 at 04:35
  • that's so weird, i wonder if your result set is bigger than the buffer or something bizarre like that – Joe T Dec 23 '14 at 04:44

1 Answers1

0

It's caused by known "Internal SQL Bug" where the commands get out of sync. Therefore the connection has to be closed and reopened to get the commands back in sync.

Reference in the User Contributed Notes: http://php.net/manual/en/mysqli-result.free.php

kayleighsdaddy
  • 670
  • 5
  • 15
  • Actually, I **am** running the "_mysqli_free_result_" command (one of the commands that's _supposed to_ address the problem according to the info on the page referenced above) before trying to run the second query, but I still get that error. – Big_Al_Tx Jul 14 '15 at 21:06
  • Connecting twice will slow down your connection. If you actually have to connect twice, I would recommend threading the connections. In a bit, I'll post some code on how to do that. – kayleighsdaddy Jul 14 '15 at 22:25