1

Hello Stack Overflow Community,

I have a table which stores information about an event running sheet. When a user updates this timesheet, I first insert the new data, in order, to the end of the table. To differentiate this data from the data last entered/updated, I have a boolean column called 'transit', and all such new data is marked as TRUE, whilst all previous data is marked as FALSE.

The idea behind this is that in case an error occurs while updating the table with the new data, the previous data is not lost, which would be the case if I deleted all data from the table first, and then entered the new rows.

Creating these rows is working fine, however when I conduct the following code...

$query = "DELETE FROM " . $table_name . " WHERE transit = 0"; 
if (!$result = mysqli_query($conn, $query)) {
    echo("Query Error: " . mysqli_error($conn));
    exit();
    }

$query = "UPDATE " . $table_name . " SET transit = 0";
if (!$result = mysqli_query($conn, $query)) {
    echo("Query Error: " . mysqli_error($conn));
    exit();
    }

...all the new data which was inserted with transit = 1 is deleted. After I did some research on Stack Overflow, it was apparent I should be using mysqli_multi_query(); - When I tried that with this modified code...

$query = "DELETE FROM " . $table_name . " WHERE transit = 0; UPDATE " . $table_name . " SET transit = 0";

if (!$result = mysqli_multi_query($conn, $query)) {
    echo("Query Error: " . mysqli_error($conn));
    exit();
}

.... I was getting the "Commands out of sync; you can't run this command now" error.

Closing and reopening the connection between the sequential queries did not make a difference, nor did adding:

mysqli_store_result($conn);
mysqli_free_result($result);
while (mysqli_next_result());

help either, as someone suggested on another post. Is this some sort of buffer issue with MySql? Any help would be appreciated!

Thank you very much for taking the time to read!

UPDATE

So the logic seems to in fact work, ie enter new data and mark it with transit = 1, delete all data marked with transit = 0, then set all remaining data to transit = 0, when using the mysqli_multi_query() function, as well as the mysqli_store_result($conn); mysqli_free_result($result); while (mysqli_next_result());, as I described above. However, as described, this throws the Commands out of sync error. I thought that meant the queries had failed, but apparently, they have succeeded.

In that case, what is causing this error to throw? Thanks!

FrankieDee
  • 163
  • 1
  • 7
  • Did you try to commit the changes before the delete and/or after the delete? – Iarwa1n Apr 29 '18 at 08:02
  • Yes - I removed the update block, and the delete removed ONLY entries marked with `transit = 0`, (as expected).. As soon as I added the update block back in, new entries are removed. It's almost as if the update block occurs before the delete block, even though it is called after... – FrankieDee Apr 29 '18 at 08:05
  • See related thread [Commands out of sync; you can't run this command now](https://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now) – MichaelsonBritt Apr 29 '18 at 09:24

0 Answers0