Monthly Archives: September 2012

Inserting multiple rows with an insert statement in MySQL

Posted on by
Reply

Today I had to double-check the syntax for doing multiple row inserts in a MySQL insert statement, because my first guess at the syntax was wrong. I found this article which explained the syntax which is basically:

INSERT INTO example
  (example_id, name, value, other_value)
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

MySQL: Error in SQL: Commands out of sync; you can’t run this command now

Posted on by
Reply

Today I was programming MySQL via PHP and I received an error “Error in SQL: Commands out of sync; you can’t run this command now”. It turned out the problem was that a previous mulit_query had mysqli_results that hadn’t been freed. This article helped me solve the problem, and now my batch mode SQL processor looks like this:

  public function execute_batch( $sql ) {

    $this->write_count++;

    if ( ! $this->link->multi_query( $sql ) ) {

      $this->throw_error( $sql );

    }

    if ( $this->link->more_results() ) {

      do {
        $result = $this->link->use_result();
        if ( $result instanceof mysqli_result ) {
          $result->free();
        }
      }
      while ( $this->link->more_results() && $this->link->next_result() );

    }
  }

This code executes the SQL batch and then frees any mysqli_results that result from the query batch.