I have some code which works, but only in 1 of 2 possible ways. I'd like to use the multi_query function of MySQLi, but each time I do at this point in the code, the server replies with a MySQL 2006 error of having lost the database connection.

PHP Code:

      if (isset($_POST["data_module"])) {
        //$queries_to_execute = '';
        foreach ($_POST["data_module"] as $line_id=>$module_name) {
          if ($module_name == "Delete") {
            //$queries_to_execute .= "DELETE FROM {$db_table_prefix}data_file_contents WHERE data_file_id=".$line_id."; DELETE FROM {$db_table_prefix}data_file WHERE data_file_id=".$line_id." LIMIT 1;";
            $database->query("DELETE FROM {$db_table_prefix}data_file_contents WHERE data_file_id=".$line_id."; DELETE FROM {$db_table_prefix}data_file WHERE data_file_id=".$line_id." LIMIT 1");
          } else if ($module_name == "Reset") {
            //$queries_to_execute .= "UPDATE {$db_table_prefix}data_file_contents SET posted='No' WHERE data_file_id=".$line_id.";";
            $database->query("UPDATE {$db_table_prefix}data_file_contents SET posted='No' WHERE data_file_id=".$line_id);
          } else {
            $data_from = $_POST["data_from"][$line_id];
            if ($data_from == "") $data_from = 0;
            $data_to = $_POST["data_to"][$line_id];
            if ($data_to == "") $data_to = 0;

            $data_display_name = preg_replace('/[^a-z0-9 \-\_\&]/i','',$_POST["data_display_name"][$line_id]);

            if ($data_from <= $data_to) {
              //$queries_to_execute .= "UPDATE {$db_table_prefix}data_file SET display_name='".$data_display_name."', data_module='".$_POST["data_module"][$line_id]."', sort_order=".$_POST['sort_order'][$line_id].", data_from=".$data_from.", data_to=".$data_to.", exclusive=".((isset($_POST['data_solo'][$line_id]))?1:0)." WHERE data_file_id=".$line_id." LIMIT 1;";
              $database->query("UPDATE {$db_table_prefix}data_file SET display_name='".$data_display_name."', data_module='".$_POST["data_module"][$line_id]."', sort_order=".$_POST['sort_order'][$line_id].", data_from=".$data_from.", data_to=".$data_to.", exclusive=".((isset($_POST['data_solo'][$line_id]))?1:0)." WHERE data_file_id=".$line_id." LIMIT 1");
            } else {
              $DatabaseMessage .= "Content source {$line_id} must have <b>from</b> value &le; <b>to</b> value.<br />";
            }
          }
        }
        /*if (strlen($queries_to_execute) > 0) {
          $database->multi_query($queries_to_execute,true);
        }*/
      } 


$database is an instance of the following class:

PHP Code:

class MySQLiExtended extends MySQLi {
  public function multi_query($query, $clear_result_set=false) {
    //Execute query and store results
    $query_results = parent::multi_query($query);
    //Clear result cache -- useful for UPDATE or INSERT commands where the result set isn't needed
    if ($clear_result_cache) {
      while ($this->next_result()) {
        $result = $this->use_result();
        if ($result instanceof mysqli_result) {
          $result->free();
        }
      }
    }
    return $query_results;
  }
} 


I'm trying to clear the results there -- perhaps that's where something's happening? I've used that code before without any problems.

In the code in the first box above, it works as shown. Change it to use the multi_query code and it fails with the error code. Where did I go wrong?

Julie figured it out! I need to match my variable names. The second code segment ought to be:

PHP Code:

class MySQLiExtended extends MySQLi {
  public function multi_query($query, $clear_result_set=false) {
    //Execute query and store results
    $query_results = parent::multi_query($query);
    //Clear result cache -- useful for UPDATE or INSERT commands where the result set isn't needed
    if ($clear_result_set) {
      while ($this->next_result()) {
        $result = $this->use_result();
        if ($result instanceof mysqli_result) {
          $result->free();
        }
      }
    }
    return $query_results;
  }
} 


What was killing the actual connection?

The var $clear_result_set was mistyped in the first example. That Julie is a smart one, she's a keeper

Originally Posted by Learning Newbie

What was killing the actual connection?

It appears that if you don't clear the result set after a multi_query and then execute another query, MySQL craps out and kills the connection before the second query is executed. One would think that a more useful error would be thrown!

Originally Posted by mgraphic

The var $clear_result_set was mistyped in the first example. That Julie is a smart one, she's a keeper

Yep. She's the other half of the company! A very bright individual.